Thread

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> — 2025-05-12T20:13:50Z

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