using CTE in multiple subqueries and full outer join - v15 vs v17 different query plan, v17 a bit slower than v15
marcos sicat <marcos.sicat@atlasifs.com>
From: marcos sicat <marcos.sicat@atlasifs.com>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2025-05-12T20:13:50Z
Lists: pgsql-bugs
Same tables, same indexes, exact row count.
Do any parameters need to be set in v17 to improve performance or at least match the execution time of v15? I see in v17 is using multiple workers but not in v15.
v15 actual timing: 0.502 ms
v17 actual timing: 0.680 ms
Although the output is less than 1 second, this stock equities trading platform that will be used by over 90 brokerage firms and thousands of traders. I plan to upgrade to v17, and performance is essential. Therefore, I am looking for nearly instantaneous response times.
[image.png]
Query:
EXPLAIN ANALYZE WITH CTE
AS
(
SELECT CAST(openqty.order_verb as text) AS side,
(openqty.price) AS price,
(openqty.qty) AS qty,
openqty.added_date AS latest_trade_date
FROM (
SELECT balances.added_date,
balances.new_order_number,
balances.root,
balances.order_verb,
(Cast(balances.price AS DECIMAL) / 10000) AS price,
(balances.quantity - COALESCE(minus_quantity,0)) AS qty
FROM (
SELECT *
FROM ( WITH recursive updates AS
(
SELECT b.added_date,
b.new_order_number,
b.original_order_number,
b.original_order_number AS root,
b.price,
b.quantity
FROM PUBLIC.prod_itch_u_message as b
WHERE original_order_number IN
(
SELECT order_number
FROM PUBLIC.prod_itch_a_message
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1)
AND orderbook= 5082)
UNION
SELECT u.added_date,
u.new_order_number,
u.original_order_number,
s.root,
u.price,
u.quantity
FROM PUBLIC.prod_itch_u_message u
JOIN updates s
ON (
s.new_order_number = u.original_order_number) )SELECT a.added_date,
a.order_number AS new_order_number,
a.order_number AS original_order_number,
a.order_number AS root,
a.order_verb,
a.price,
a.quantity
FROM PUBLIC.prod_itch_a_message as a
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1)
AND a.orderbook= 5082
AND a.order_number >0
AND a.order_number NOT IN
(
SELECT root
FROM updates)
UNION ALL
SELECT *
FROM (
SELECT DISTINCT
ON (
u.root) u.added_date,
u.new_order_number,
u.original_order_number,
u.root ,
added.order_verb,
u.price,
u.quantity
FROM updates u
LEFT JOIN
(
SELECT order_number,
order_verb
FROM PUBLIC.prod_itch_a_message
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1)
AND orderbook= 5082 ) AS added
ON u.root=added.order_number
ORDER BY u.root,
u.added_date DESC )AS updated ) AS orders
WHERE new_order_number NOT IN
(
SELECT order_number
FROM PUBLIC.prod_itch_d_message
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1) ) ) AS balances
LEFT JOIN
(
SELECT max(rest_order_number) AS rest_order_number,
sum(minus_quantity) AS minus_quantity
FROM (
SELECT max(order_number) AS rest_order_number,
sum(executed_quantity) AS minus_quantity
FROM PUBLIC.prod_itch_e_small_message
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1)
GROUP BY order_number
UNION ALL
SELECT max(order_number) AS rest_order_number,
sum(executed_quantity) AS minus_quantity
FROM PUBLIC.prod_itch_c_small_message
WHERE DATE(added_date) = (
SELECT
DATE(d.added_date)
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT 1)
GROUP BY order_number
)AS grouped
GROUP BY rest_order_number
) AS
minus
ON rest_order_number=new_order_number ) AS openqty
WHERE openqty.qty >0
GROUP BY side, openqty.price, openqty.qty, openqty.added_date
ORDER BY side,openqty.price DESC, added_date desc
)
SELECT x.BID_orders, x.BID_CumQ, z.buy_latest_qty, x.BID ,
x.ASK, z.sell_latest_qty, x.Ask_CumQ, x.ASK_Orders
FROM
(
SELECT Row_number() OVER () AS buy_id,
buy.total_orders as BID_Orders,
buy.CumQ as BID_CumQ,
buy.price::numeric(10,4) as BID,
sell.price::numeric(10,4) AS ASK,
sell.CumQ AS ASK_CumQ,
sell.total_orders as ASK_Orders
FROM
(
(
SELECT Row_number() OVER () AS id,B.side, B.price, B.total_orders, B.CumQ
FROM
(
SELECT side, price, count(*) as total_orders, sum(qty) as CumQ
from CTE
WHERE side = 'B'
group by price,side
order by side, price desc LIMIT 10
) AS B
) as buy
FULL OUTER JOIN
(
SELECT Row_number() OVER () AS id, C.side, C.price, C.total_orders, C.CumQ
FROM
(
SELECT c.side, c.price, count(*) as total_orders, sum(c.qty) as CumQ
from CTE c
WHERE side = 'S'
group by c.price,c.side
order by c.side, c.price asc LIMIT 10
) AS C
) as sell
ON ( sell.id = buy.id )
)
) AS x
FULL OUTER JOIN
(
SELECT Row_number() OVER () AS sell_id,
bc.side as buy_side,
bc.price as buy_curr_price,
bc.qty as buy_latest_qty,
sc.side as sell_side,
sc.price as sell_curr_price,
sc.qty as sell_latest_qty
FROM
(
(
SELECT Row_number() OVER () AS id, side, price, qty
FROM
(
SELECT side, price, qty, row_number() over ( partition by price order by price desc ) as rn
FROM CTE
where side = 'B'
GROUP BY side,price,qty, latest_trade_date
order by price desc, latest_trade_date desc
) AS b
WHERE b.rn = 1 limit 10
) as bc
FULL OUTER JOIN
(
SELECT Row_number() OVER () AS id, s.side, s.price, s.qty
FROM
(
SELECT side, price, qty, row_number() over ( partition by price order by price asc ) as rn FROM CTE
where side = 'S'
GROUP BY side,price,qty, latest_trade_date
order by price asc, latest_trade_date desc
) AS s
WHERE s.rn = 1 limit 10
) as sc
ON ( sc.id = bc.id )
)
) AS z
ON ( z.sell_id = x.buy_id )
;
PostgreSQL 15.12 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
V15 QPlan:
v15
QUERY PLAN
Hash Full Join (cost=26413.33..26413.45 rows=1 width=176) (actual time=316.784..316.863 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = x.buy_id)
CTE cte
-> Group (cost=26408.64..26409.52 rows=39 width=104) (actual time=313.725..314.159 rows=1571 loops=1)
Group Key: ((orders.order_verb)::text), (((orders.price)::numeric / '10000'::numeric)), orders.added_date, (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)))
-> Sort (cost=26408.64..26408.74 rows=39 width=104) (actual time=313.723..313.840 rows=1571 loops=1)
Sort Key: ((orders.order_verb)::text), (((orders.price)::numeric / '10000'::numeric)) DESC, orders.added_date DESC, (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)))
Sort Method: quicksort Memory: 147kB
-> Hash Left Join (cost=25540.35..26407.61 rows=39 width=104) (actual time=294.007..311.735 rows=1571 loops=1)
Hash Cond: (orders.new_order_number = minus.rest_order_number)
Filter: (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)) > '0'::numeric)
Rows Removed by Filter: 3960
-> Subquery Scan on orders (cost=20000.83..20862.39 rows=118 width=33) (actual time=75.168..89.810 rows=5531 loops=1)
Filter: (NOT (hashed SubPlan 2))
Rows Removed by Filter: 3875
-> Append (cost=17335.98..18194.59 rows=236 width=49) (actual time=39.190..51.310 rows=9406 loops=1)
CTE updates
-> Recursive Union (cost=274.72..17261.99 rows=2995 width=48) (actual time=9.199..35.786 rows=2582 loops=1)
-> Nested Loop (cost=274.72..838.34 rows=25 width=48) (actual time=9.195..26.424 rows=1891 loops=1)
InitPlan 5 (returns $5)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.020..0.021 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.019..0.020 rows=1 loops=1)
Sort Key: d_3.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_3 (cost=0.00..1.43 rows=2 width=12) (actual time=0.005..0.010 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> HashAggregate (cost=272.99..273.72 rows=73 width=8) (actual time=9.173..11.284 rows=8736 loops=1)
Group Key: prod_itch_a_message.order_number
Batches: 1 Memory Usage: 865kB
-> Bitmap Heap Scan on prod_itch_a_message (cost=5.17..272.81 rows=73 width=8) (actual time=1.025..5.485 rows=9431 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = $5))
Heap Blocks: exact=2178
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.15 rows=73 width=0) (actual time=0.675..0.675 rows=9431 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = $5))
-> Index Scan using icx_orig_order_number on prod_itch_u_message b_2 (cost=0.29..7.70 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=8736)
Index Cond: (original_order_number = prod_itch_a_message.order_number)
-> Nested Loop (cost=0.29..1636.38 rows=297 width=48) (actual time=0.004..0.515 rows=255 loops=13)
-> WorkTable Scan on updates s_1 (cost=0.00..5.00 rows=250 width=16) (actual time=0.000..0.018 rows=199 loops=13)
-> Index Scan using icx_orig_order_number on prod_itch_u_message u (cost=0.29..6.52 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=2582)
Index Cond: (original_order_number = s_1.new_order_number)
-> Bitmap Heap Scan on prod_itch_a_message a (cost=73.99..341.99 rows=36 width=49) (actual time=39.188..44.462 rows=7901 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = $9))
Filter: ((order_number > 0) AND (NOT (hashed SubPlan 8)))
Rows Removed by Filter: 1530
Heap Blocks: exact=2178
InitPlan 7 (returns $9)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.031..0.032 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.031..0.031 rows=1 loops=1)
Sort Key: d_4.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_4 (cost=0.00..1.43 rows=2 width=12) (actual time=0.008..0.013 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.15 rows=73 width=0) (actual time=0.709..0.709 rows=9431 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = $9))
SubPlan 8
-> CTE Scan on updates (cost=0.00..59.90 rows=2995 width=8) (actual time=9.201..37.217 rows=2582 loops=1)
-> Unique (cost=572.09..587.07 rows=200 width=49) (actual time=5.806..6.214 rows=1505 loops=1)
InitPlan 9 (returns $11)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.021..0.022 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.020..0.021 rows=1 loops=1)
Sort Key: d_5.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_5 (cost=0.00..1.43 rows=2 width=12) (actual time=0.009..0.012 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Sort (cost=570.65..578.14 rows=2995 width=49) (actual time=5.804..5.915 rows=2633 loops=1)
Sort Key: u_1.root, u_1.added_date DESC
Sort Method: quicksort Memory: 343kB
-> Hash Right Join (cost=102.51..397.71 rows=2995 width=49) (actual time=1.079..5.180 rows=2633 loops=1)
Hash Cond: (prod_itch_a_message_1.order_number = u_1.root)
-> Bitmap Heap Scan on prod_itch_a_message prod_itch_a_message_1 (cost=5.17..272.81 rows=73 width=9) (actual time=0.606..3.304 rows=9431 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = $11))
Heap Blocks: exact=2178
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.15 rows=73 width=0) (actual time=0.358..0.358 rows=9431 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = $11))
-> Hash (cost=59.90..59.90 rows=2995 width=48) (actual time=0.406..0.407 rows=2582 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 234kB
-> CTE Scan on updates u_1 (cost=0.00..59.90 rows=2995 width=48) (actual time=0.001..0.142 rows=2582 loops=1)
SubPlan 2
-> Seq Scan on prod_itch_d_message (cost=1.44..2663.63 rows=487 width=8) (actual time=8.578..21.928 rows=46281 loops=1)
Filter: (date(added_date) = $0)
Rows Removed by Filter: 51065
InitPlan 1 (returns $0)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.032..0.033 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.030..0.031 rows=1 loops=1)
Sort Key: d.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d (cost=0.00..1.43 rows=2 width=12) (actual time=0.007..0.012 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Hash (cost=5537.02..5537.02 rows=200 width=40) (actual time=218.803..218.812 rows=54642 loops=1)
Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3074kB
-> Subquery Scan on minus (cost=5524.58..5537.02 rows=200 width=40) (actual time=149.192..206.652 rows=54642 loops=1)
-> GroupAggregate (cost=5524.58..5535.02 rows=200 width=48) (actual time=149.191..199.422 rows=54642 loops=1)
Group Key: "*SELECT* 1_1".rest_order_number
-> Sort (cost=5524.58..5526.57 rows=794 width=40) (actual time=149.179..156.408 rows=54844 loops=1)
Sort Key: "*SELECT* 1_1".rest_order_number
Sort Method: external merge Disk: 1256kB
-> Append (cost=4976.79..5486.34 rows=794 width=40) (actual time=65.807..132.099 rows=54844 loops=1)
-> Subquery Scan on "*SELECT* 1_1" (cost=4976.79..5000.21 rows=720 width=40) (actual time=65.806..117.528 rows=50829 loops=1)
-> GroupAggregate (cost=4976.79..4993.01 rows=720 width=48) (actual time=65.805..111.515 rows=50829 loops=1)
Group Key: prod_itch_e_small_message.order_number
InitPlan 3 (returns $2)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.031..0.032 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.029..0.029 rows=1 loops=1)
Sort Key: d_1.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_1 (cost=0.00..1.43 rows=2 width=12) (actual time=0.009..0.014 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Sort (cost=4975.35..4977.16 rows=722 width=16) (actual time=65.794..75.850 rows=76950 loops=1)
Sort Key: prod_itch_e_small_message.order_number
Sort Method: external merge Disk: 1976kB
-> Seq Scan on prod_itch_e_small_message (cost=0.00..4941.07 rows=722 width=16) (actual time=12.437..37.642 rows=76950 loops=1)
Filter: (date(added_date) = $2)
Rows Removed by Filter: 67388
-> Subquery Scan on "*SELECT* 2" (cost=479.75..482.15 rows=74 width=40) (actual time=4.766..9.808 rows=4015 loops=1)
-> GroupAggregate (cost=479.75..481.41 rows=74 width=48) (actual time=4.764..9.345 rows=4015 loops=1)
Group Key: prod_itch_c_small_message.order_number
InitPlan 4 (returns $3)
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.020..0.022 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.018..0.019 rows=1 loops=1)
Sort Key: d_2.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_2 (cost=0.00..1.43 rows=2 width=12) (actual time=0.007..0.011 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Sort (cost=478.31..478.49 rows=74 width=16) (actual time=4.753..5.494 rows=7447 loops=1)
Sort Key: prod_itch_c_small_message.order_number
Sort Method: quicksort Memory: 600kB
-> Seq Scan on prod_itch_c_small_message (cost=0.00..476.01 rows=74 width=16) (actual time=1.171..3.160 rows=7447 loops=1)
Filter: (date(added_date) = $3)
Rows Removed by Filter: 7287
-> WindowAgg (cost=1.85..1.95 rows=1 width=200) (actual time=1.470..1.509 rows=10 loops=1)
-> Hash Full Join (cost=1.85..1.94 rows=1 width=64) (actual time=1.468..1.502 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = sc.id)
-> Limit (cost=0.89..0.94 rows=1 width=104) (actual time=0.499..0.526 rows=10 loops=1)
-> WindowAgg (cost=0.89..0.94 rows=1 width=104) (actual time=0.498..0.524 rows=10 loops=1)
-> Subquery Scan on b (cost=0.89..0.93 rows=1 width=32) (actual time=0.495..0.518 rows=10 loops=1)
Filter: (b.rn = 1)
-> WindowAgg (cost=0.89..0.92 rows=1 width=112) (actual time=0.494..0.515 rows=10 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Group (cost=0.89..0.90 rows=1 width=104) (actual time=0.485..0.495 rows=17 loops=1)
Group Key: cte.price, cte.latest_trade_date, cte.side, cte.qty
-> Sort (cost=0.89..0.89 rows=1 width=104) (actual time=0.484..0.485 rows=17 loops=1)
Sort Key: cte.price DESC, cte.latest_trade_date DESC, cte.qty
Sort Method: quicksort Memory: 83kB
-> CTE Scan on cte (cost=0.00..0.88 rows=1 width=104) (actual time=0.002..0.215 rows=934 loops=1)
Filter: (side = 'B'::text)
Rows Removed by Filter: 637
-> Hash (cost=0.95..0.95 rows=1 width=40) (actual time=0.947..0.949 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on sc (cost=0.89..0.95 rows=1 width=40) (actual time=0.872..0.941 rows=10 loops=1)
-> Limit (cost=0.89..0.94 rows=1 width=104) (actual time=0.871..0.938 rows=10 loops=1)
-> WindowAgg (cost=0.89..0.94 rows=1 width=104) (actual time=0.869..0.936 rows=10 loops=1)
-> Subquery Scan on s (cost=0.89..0.93 rows=1 width=32) (actual time=0.866..0.929 rows=10 loops=1)
Filter: (s.rn = 1)
-> WindowAgg (cost=0.89..0.92 rows=1 width=112) (actual time=0.864..0.925 rows=10 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Group (cost=0.89..0.90 rows=1 width=104) (actual time=0.853..0.878 rows=60 loops=1)
Group Key: cte_1.price, cte_1.latest_trade_date, cte_1.side, cte_1.qty
-> Sort (cost=0.89..0.89 rows=1 width=104) (actual time=0.851..0.855 rows=60 loops=1)
Sort Key: cte_1.price, cte_1.latest_trade_date DESC, cte_1.qty
Sort Method: quicksort Memory: 64kB
-> CTE Scan on cte cte_1 (cost=0.00..0.88 rows=1 width=104) (actual time=0.091..0.187 rows=637 loops=1)
Filter: (side = 'S'::text)
Rows Removed by Filter: 934
-> Hash (cost=1.95..1.95 rows=1 width=120) (actual time=315.294..315.299 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on x (cost=1.85..1.95 rows=1 width=120) (actual time=315.254..315.290 rows=10 loops=1)
-> WindowAgg (cost=1.85..1.94 rows=1 width=120) (actual time=315.253..315.288 rows=10 loops=1)
-> Hash Full Join (cost=1.85..1.92 rows=1 width=144) (actual time=315.247..315.274 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = sell.id)
-> WindowAgg (cost=0.89..0.94 rows=1 width=112) (actual time=0.386..0.403 rows=10 loops=1)
-> Subquery Scan on b_1 (cost=0.89..0.92 rows=1 width=72) (actual time=0.383..0.396 rows=10 loops=1)
-> Limit (cost=0.89..0.91 rows=1 width=104) (actual time=0.382..0.394 rows=10 loops=1)
-> GroupAggregate (cost=0.89..0.91 rows=1 width=104) (actual time=0.381..0.392 rows=10 loops=1)
Group Key: cte_2.side, cte_2.price
-> Sort (cost=0.89..0.89 rows=1 width=96) (actual time=0.376..0.377 rows=17 loops=1)
Sort Key: cte_2.price DESC
Sort Method: quicksort Memory: 76kB
-> CTE Scan on cte cte_2 (cost=0.00..0.88 rows=1 width=96) (actual time=0.001..0.173 rows=934 loops=1)
Filter: (side = 'B'::text)
Rows Removed by Filter: 637
-> Hash (cost=0.95..0.95 rows=1 width=80) (actual time=314.845..314.848 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on sell (cost=0.89..0.95 rows=1 width=80) (actual time=314.817..314.841 rows=10 loops=1)
-> WindowAgg (cost=0.89..0.94 rows=1 width=112) (actual time=314.817..314.839 rows=10 loops=1)
-> Subquery Scan on c (cost=0.89..0.92 rows=1 width=72) (actual time=314.813..314.832 rows=10 loops=1)
-> Limit (cost=0.89..0.91 rows=1 width=104) (actual time=314.812..314.830 rows=10 loops=1)
-> GroupAggregate (cost=0.89..0.91 rows=1 width=104) (actual time=314.811..314.827 rows=10 loops=1)
Group Key: c_1.side, c_1.price
-> Sort (cost=0.89..0.89 rows=1 width=96) (actual time=314.803..314.805 rows=60 loops=1)
Sort Key: c_1.price
Sort Method: quicksort Memory: 59kB
-> CTE Scan on cte c_1 (cost=0.00..0.88 rows=1 width=96) (actual time=314.148..314.578 rows=637 loops=1)
Filter: (side = 'S'::text)
Rows Removed by Filter: 934
Planning Time: 3.598 ms
Execution Time: 318.782 ms
PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
v17 QPlan:
[image.png]
V17
QUERY PLAN
Hash Full Join (cost=25785.60..25786.58 rows=1 width=176) (actual time=507.191..507.576 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = x.buy_id)
CTE cte
-> Group (cost=25781.81..25782.69 rows=39 width=104) (actual time=505.690..506.335 rows=960 loops=1)
Group Key: ((orders.order_verb)::text), (((orders.price)::numeric / '10000'::numeric)), orders.added_date, (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)))
-> Sort (cost=25781.81..25781.91 rows=39 width=104) (actual time=505.687..506.073 rows=960 loops=1)
Sort Key: ((orders.order_verb)::text), (((orders.price)::numeric / '10000'::numeric)) DESC, orders.added_date DESC, (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)))
Sort Method: quicksort Memory: 62kB
-> Hash Left Join (cost=25013.65..25780.78 rows=39 width=104) (actual time=484.337..504.458 rows=960 loops=1)
Hash Cond: (orders.new_order_number = minus.rest_order_number)
Filter: (((orders.quantity)::numeric - COALESCE(minus.minus_quantity, '0'::numeric)) > '0'::numeric)
Rows Removed by Filter: 3928
-> Subquery Scan on orders (cost=18084.90..18846.37 rows=117 width=33) (actual time=100.009..116.791 rows=4888 loops=1)
Filter: (NOT (ANY (orders.new_order_number = (hashed SubPlan 2).col1)))
Rows Removed by Filter: 3847
-> Append (cost=14709.35..15467.90 rows=234 width=49) (actual time=51.392..65.384 rows=8735 loops=1)
CTE updates
-> Recursive Union (cost=260.14..14649.02 rows=2390 width=48) (actual time=11.111..46.607 rows=2572 loops=1)
-> Nested Loop (cost=260.14..790.12 rows=20 width=48) (actual time=11.107..33.700 rows=1885 loops=1)
InitPlan 5
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.032..0.033 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.031..0.032 rows=1 loops=1)
Sort Key: d_3.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_3 (cost=0.00..1.43 rows=2 width=12) (actual time=0.008..0.015 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> HashAggregate (cost=258.41..259.09 rows=68 width=8) (actual time=11.083..13.584 rows=8736 loops=1)
Group Key: prod_itch_a_message.order_number
Batches: 1 Memory Usage: 801kB
-> Bitmap Heap Scan on prod_itch_a_message (cost=5.12..258.24 rows=68 width=8) (actual time=1.422..6.345 rows=8736 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 5).col1))
Heap Blocks: exact=1891
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.10 rows=68 width=0) (actual time=1.037..1.037 rows=8736 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 5).col1))
-> Index Scan using icx_orig_order_number on prod_itch_u_message b_2 (cost=0.29..7.78 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=8736)
Index Cond: (original_order_number = prod_itch_a_message.order_number)
-> Nested Loop (cost=0.29..1383.50 rows=237 width=48) (actual time=0.005..0.711 rows=252 loops=13)
-> WorkTable Scan on updates s_1 (cost=0.00..4.00 rows=200 width=16) (actual time=0.000..0.034 rows=198 loops=13)
-> Index Scan using icx_orig_order_number on prod_itch_u_message u (cost=0.29..6.89 rows=1 width=40) (actual time=0.002..0.003 rows=1 loops=2572)
Index Cond: (original_order_number = s_1.new_order_number)
-> Bitmap Heap Scan on prod_itch_a_message a (cost=60.33..313.79 rows=34 width=49) (actual time=51.391..56.979 rows=7230 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 7).col1))
Filter: ((order_number > 0) AND (NOT (ANY (order_number = (hashed SubPlan 8).col1))))
Rows Removed by Filter: 1506
Heap Blocks: exact=1891
InitPlan 7
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.044..0.045 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.043..0.044 rows=1 loops=1)
Sort Key: d_4.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_4 (cost=0.00..1.43 rows=2 width=12) (actual time=0.019..0.027 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.10 rows=68 width=0) (actual time=1.025..1.025 rows=8736 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 7).col1))
SubPlan 8
-> CTE Scan on updates (cost=0.00..47.80 rows=2390 width=8) (actual time=11.114..48.778 rows=2572 loops=1)
-> Unique (cost=491.97..503.92 rows=200 width=49) (actual time=7.231..7.703 rows=1505 loops=1)
InitPlan 9
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.036..0.037 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.035..0.036 rows=1 loops=1)
Sort Key: d_5.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_5 (cost=0.00..1.43 rows=2 width=12) (actual time=0.015..0.019 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Sort (cost=490.52..496.50 rows=2390 width=49) (actual time=7.230..7.361 rows=2572 loops=1)
Sort Key: u_1.root, u_1.added_date DESC
Sort Method: quicksort Memory: 277kB
-> Hash Right Join (cost=82.79..356.41 rows=2390 width=49) (actual time=1.319..6.294 rows=2572 loops=1)
Hash Cond: (prod_itch_a_message_1.order_number = u_1.root)
-> Bitmap Heap Scan on prod_itch_a_message prod_itch_a_message_1 (cost=5.12..258.24 rows=68 width=9) (actual time=0.776..3.888 rows=8736 loops=1)
Recheck Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 9).col1))
Heap Blocks: exact=1891
-> Bitmap Index Scan on orderbook_added_date (cost=0.00..5.10 rows=68 width=0) (actual time=0.490..0.490 rows=8736 loops=1)
Index Cond: ((orderbook = 5082) AND (date(added_date) = (InitPlan 9).col1))
-> Hash (cost=47.80..47.80 rows=2390 width=48) (actual time=0.445..0.446 rows=2572 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 233kB
-> CTE Scan on updates u_1 (cost=0.00..47.80 rows=2390 width=48) (actual time=0.001..0.194 rows=2572 loops=1)
SubPlan 2
-> Seq Scan on prod_itch_d_message (cost=1.44..3374.33 rows=486 width=8) (actual time=2.861..29.276 rows=46194 loops=1)
Filter: (date(added_date) = (InitPlan 1).col1)
Rows Removed by Filter: 51065
InitPlan 1
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.038..0.039 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.037..0.037 rows=1 loops=1)
Sort Key: d.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d (cost=0.00..1.43 rows=2 width=12) (actual time=0.012..0.019 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Hash (cost=6926.26..6926.26 rows=200 width=40) (actual time=384.289..384.608 rows=54642 loops=1)
Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3074kB
-> Subquery Scan on minus (cost=6913.82..6926.26 rows=200 width=40) (actual time=299.508..368.864 rows=54642 loops=1)
-> GroupAggregate (cost=6913.82..6924.26 rows=200 width=48) (actual time=299.506..361.281 rows=54642 loops=1)
Group Key: "*SELECT* 1_1".rest_order_number
-> Sort (cost=6913.82..6915.80 rows=794 width=40) (actual time=299.487..309.068 rows=54844 loops=1)
Sort Key: "*SELECT* 1_1".rest_order_number
Sort Method: external merge Disk: 1248kB
-> Append (cost=6155.97..6875.57 rows=794 width=40) (actual time=98.243..267.501 rows=54844 loops=1)
-> Subquery Scan on "*SELECT* 1_1" (cost=6155.97..6254.45 rows=720 width=40) (actual time=98.242..241.066 rows=50829 loops=1)
-> Finalize GroupAggregate (cost=6155.97..6247.25 rows=720 width=48) (actual time=98.240..232.983 rows=50829 loops=1)
Group Key: prod_itch_e_small_message.order_number
InitPlan 3
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.040..0.042 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.039..0.040 rows=1 loops=1)
Sort Key: d_1.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_1 (cost=0.00..1.43 rows=2 width=12) (actual time=0.017..0.024 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Gather Merge (cost=6154.53..6230.79 rows=602 width=48) (actual time=98.227..179.490 rows=55932 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=5154.50..5161.28 rows=301 width=48) (actual time=78.508..103.956 rows=18644 loops=3)
Group Key: prod_itch_e_small_message.order_number
-> Sort (cost=5154.50..5155.26 rows=301 width=16) (actual time=78.480..83.961 rows=25650 loops=3)
Sort Key: prod_itch_e_small_message.order_number
Sort Method: quicksort Memory: 1579kB
Worker 0: Sort Method: quicksort Memory: 1655kB
Worker 1: Sort Method: quicksort Memory: 1476kB
-> Parallel Seq Scan on prod_itch_e_small_message (cost=0.00..5142.11 rows=301 width=16) (actual time=4.004..43.561 rows=25650 loops=3)
Filter: (date(added_date) = (InitPlan 3).col1)
Rows Removed by Filter: 22463
-> Subquery Scan on "*SELECT* 2" (cost=614.75..617.15 rows=74 width=40) (actual time=7.206..15.470 rows=4015 loops=1)
-> GroupAggregate (cost=614.75..616.41 rows=74 width=48) (actual time=7.205..14.896 rows=4015 loops=1)
Group Key: prod_itch_c_small_message.order_number
InitPlan 4
-> Limit (cost=1.44..1.44 rows=1 width=12) (actual time=0.029..0.030 rows=1 loops=1)
-> Sort (cost=1.44..1.44 rows=2 width=12) (actual time=0.027..0.028 rows=1 loops=1)
Sort Key: d_2.added_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on prod_itchbbo_s_message d_2 (cost=0.00..1.43 rows=2 width=12) (actual time=0.010..0.015 rows=2 loops=1)
Filter: ((event_code)::text = 'S'::text)
Rows Removed by Filter: 32
-> Sort (cost=613.31..613.49 rows=74 width=16) (actual time=7.170..8.315 rows=7447 loops=1)
Sort Key: prod_itch_c_small_message.order_number
Sort Method: quicksort Memory: 425kB
-> Seq Scan on prod_itch_c_small_message (cost=0.00..611.01 rows=74 width=16) (actual time=0.377..4.646 rows=7447 loops=1)
Filter: (date(added_date) = (InitPlan 4).col1)
Rows Removed by Filter: 7287
-> WindowAgg (cost=0.96..1.93 rows=1 width=200) (actual time=0.523..0.557 rows=10 loops=1)
-> Hash Full Join (cost=0.96..1.92 rows=1 width=64) (actual time=0.518..0.548 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = sc.id)
-> Limit (cost=0.00..0.93 rows=1 width=104) (actual time=0.014..0.036 rows=10 loops=1)
-> WindowAgg (cost=0.00..0.93 rows=1 width=104) (actual time=0.013..0.034 rows=10 loops=1)
-> Subquery Scan on b (cost=0.00..0.91 rows=1 width=32) (actual time=0.006..0.024 rows=10 loops=1)
Filter: (b.rn = 1)
-> WindowAgg (cost=0.00..0.90 rows=1 width=112) (actual time=0.005..0.022 rows=10 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Group (cost=0.00..0.88 rows=1 width=104) (actual time=0.003..0.011 rows=16 loops=1)
Group Key: cte.price, cte.latest_trade_date, cte.qty
-> CTE Scan on cte (cost=0.00..0.88 rows=1 width=104) (actual time=0.002..0.004 rows=16 loops=1)
Filter: (side = 'B'::text)
-> Hash (cost=0.95..0.95 rows=1 width=40) (actual time=0.473..0.476 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on sc (cost=0.89..0.95 rows=1 width=40) (actual time=0.428..0.469 rows=10 loops=1)
-> Limit (cost=0.89..0.94 rows=1 width=104) (actual time=0.427..0.466 rows=10 loops=1)
-> WindowAgg (cost=0.89..0.94 rows=1 width=104) (actual time=0.427..0.465 rows=10 loops=1)
-> Subquery Scan on s (cost=0.89..0.93 rows=1 width=32) (actual time=0.425..0.460 rows=10 loops=1)
Filter: (s.rn = 1)
-> WindowAgg (cost=0.89..0.92 rows=1 width=112) (actual time=0.424..0.458 rows=10 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Group (cost=0.89..0.90 rows=1 width=104) (actual time=0.421..0.439 rows=57 loops=1)
Group Key: cte_1.price, cte_1.latest_trade_date, cte_1.qty
-> Sort (cost=0.89..0.89 rows=1 width=104) (actual time=0.420..0.423 rows=57 loops=1)
Sort Key: cte_1.price, cte_1.latest_trade_date DESC, cte_1.qty
Sort Method: quicksort Memory: 44kB
-> CTE Scan on cte cte_1 (cost=0.00..0.88 rows=1 width=104) (actual time=0.048..0.124 rows=422 loops=1)
Filter: (side = 'S'::text)
Rows Removed by Filter: 538
-> Hash (cost=1.93..1.93 rows=1 width=120) (actual time=506.636..506.642 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on x (cost=0.96..1.93 rows=1 width=120) (actual time=506.599..506.635 rows=10 loops=1)
-> WindowAgg (cost=0.96..1.92 rows=1 width=120) (actual time=506.597..506.631 rows=10 loops=1)
-> Hash Full Join (cost=0.96..1.90 rows=1 width=144) (actual time=506.591..506.619 rows=10 loops=1)
Hash Cond: ((row_number() OVER (?)) = sell.id)
-> WindowAgg (cost=0.00..0.92 rows=1 width=112) (actual time=0.011..0.029 rows=10 loops=1)
-> Subquery Scan on b_1 (cost=0.00..0.91 rows=1 width=72) (actual time=0.008..0.023 rows=10 loops=1)
-> Limit (cost=0.00..0.90 rows=1 width=104) (actual time=0.007..0.021 rows=10 loops=1)
-> GroupAggregate (cost=0.00..0.90 rows=1 width=104) (actual time=0.006..0.019 rows=10 loops=1)
Group Key: cte_2.price
-> CTE Scan on cte cte_2 (cost=0.00..0.88 rows=1 width=96) (actual time=0.001..0.005 rows=24 loops=1)
Filter: (side = 'B'::text)
-> Hash (cost=0.94..0.94 rows=1 width=80) (actual time=506.550..506.552 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on sell (cost=0.89..0.94 rows=1 width=80) (actual time=506.518..506.546 rows=10 loops=1)
-> WindowAgg (cost=0.89..0.93 rows=1 width=112) (actual time=506.517..506.544 rows=10 loops=1)
-> Subquery Scan on c (cost=0.89..0.92 rows=1 width=72) (actual time=506.512..506.534 rows=10 loops=1)
-> Limit (cost=0.89..0.91 rows=1 width=104) (actual time=506.511..506.532 rows=10 loops=1)
-> GroupAggregate (cost=0.89..0.91 rows=1 width=104) (actual time=506.510..506.529 rows=10 loops=1)
Group Key: c_1.price
-> Sort (cost=0.89..0.89 rows=1 width=96) (actual time=506.499..506.502 rows=58 loops=1)
Sort Key: c_1.price
Sort Method: quicksort Memory: 38kB
-> CTE Scan on cte c_1 (cost=0.00..0.88 rows=1 width=96) (actual time=506.039..506.348 rows=422 loops=1)
Filter: (side = 'S'::text)
Rows Removed by Filter: 538
Planning Time: 5.388 ms
Execution Time: 650.023 ms