using CTE in multiple subqueries and full outer join - v15 vs v17 different query plan, v17 a bit slower than v15

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