Query run in 27s with 15.2 vs 37ms with 14.6 - Mailing list pgsql-bugs

Hi,

I have just upgraded my postgresql from 14.6 to 15.2 and my query that runs in 37ms in 14.6 run requires 27 seconds to complete. The table is a typical OHLC table (date date, code text, open int, high int, low int, close int, volume bigint, value bigint), 2725207 rows in table.

This is the query

with vol_avg as (
  select
    code,
    avg(value) as value
  from
    stock_price
  where
    value > 0 and
    date > (select date from stock_date order by date desc limit 1 offset 5)
  group by
    code
)
select
  s.code,
  s.close,
  100.0 * (s.close - s.open) / s.open as chg,
  s.value,
  s.volume,
  va.value as value_avg
from
  stock_price s,
  vol_avg va
where
  length(s.code) = 4 and
  s.date = '2023-02-20' and
  s.open > 0 and
  s.value > 0 and
  s.code = va.code

If run individually the query in the CTE and the bottom query without CTE runs fast. Only when joined do they require 27 seconds to complete.

The plan from 15.2:

Nested Loop  (cost=63003.26..64440.14 rows=1 width=89) (actual time=23.234..27407.834 rows=779 loops=1)
  Join Filter: (s.code = stock_price.code)
  Rows Removed by Join Filter: 349117
  ->  Index Scan using idx_stock_price_date on stock_price s  (cost=0.43..1152.53 rows=1 width=29) (actual time=0.180..4.060 rows=779 loops=1)
        Index Cond: (date = '2023-02-20'::date)
        Filter: ((open > 0) AND (value > 0) AND (length(code) = 4))
        Rows Removed by Filter: 210
  ->  Finalize GroupAggregate  (cost=63002.83..63264.98 rows=1005 width=37) (actual time=34.783..35.149 rows=449 loops=779)
        Group Key: stock_price.code
        InitPlan 1 (returns $0)
          ->  Limit  (cost=0.41..0.43 rows=1 width=4) (actual time=0.094..0.095 rows=1 loops=1)
                ->  Index Only Scan Backward using stock_date_pkey on stock_date  (cost=0.28..182.90 rows=7359 width=4) (actual time=0.091..0.092 rows=6 loops=1)
                      Heap Fetches: 6
        ->  Gather Merge  (cost=63002.40..63236.91 rows=2010 width=37) (actual time=34.772..34.843 rows=450 loops=779)
              Workers Planned: 2
              Params Evaluated: $0
              Workers Launched: 2
              ->  Sort  (cost=62002.37..62004.89 rows=1005 width=37) (actual time=0.907..0.919 rows=150 loops=2337)
                    Sort Key: stock_price.code
                    Sort Method: quicksort  Memory: 95kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Partial HashAggregate  (cost=61939.70..61952.26 rows=1005 width=37) (actual time=0.706..0.786 rows=302 loops=2337)
                          Group Key: stock_price.code
                          Batches: 1  Memory Usage: 577kB
                          Worker 0:  Batches: 1  Memory Usage: 73kB
                          Worker 1:  Batches: 1  Memory Usage: 73kB
                          ->  Parallel Bitmap Heap Scan on stock_price  (cost=8799.81..61483.32 rows=91275 width=13) (actual time=0.068..0.333 rows=1427 loops=2337)
                                Recheck Cond: (date > $0)
                                Filter: (value > 0)
                                Rows Removed by Filter: 222
                                Heap Blocks: exact=138662
                                ->  Bitmap Index Scan on idx_stock_price_date  (cost=0.00..8745.05 rows=908402 width=0) (actual time=0.176..0.176 rows=8917 loops=779)
                                      Index Cond: (date > $0)
Planning Time: 1.122 ms
Execution Time: 27408.516 ms

Plan generated by 14.6

Merge Join  (cost=61456.65..61716.09 rows=1 width=89) (actual time=25.509..37.185 rows=779 loops=1)
  Merge Cond: (s.code = stock_price.code)
  ->  Sort  (cost=319.77..319.78 rows=1 width=29) (actual time=0.745..0.771 rows=779 loops=1)
        Sort Key: s.code
        Sort Method: quicksort  Memory: 85kB
        ->  Index Scan using idx_stock_price_date on stock_price s  (cost=0.43..319.76 rows=1 width=29) (actual time=0.197..0.514 rows=779 loops=1)
              Index Cond: (date = '2023-02-20'::date)
              Filter: ((open > 0) AND (value > 0) AND (length(code) = 4))
              Rows Removed by Filter: 210
  ->  Finalize GroupAggregate  (cost=61136.88..61384.43 rows=949 width=37) (actual time=24.756..35.979 rows=906 loops=1)
        Group Key: stock_price.code
        InitPlan 1 (returns $0)
          ->  Limit  (cost=0.41..0.43 rows=1 width=4) (actual time=0.116..0.116 rows=1 loops=1)
                ->  Index Only Scan Backward using stock_date_pkey on stock_date  (cost=0.28..181.48 rows=7261 width=4) (actual time=0.113..0.115 rows=6 loops=1)
                      Heap Fetches: 6
        ->  Gather Merge  (cost=61136.45..61357.90 rows=1898 width=37) (actual time=24.736..35.284 rows=907 loops=1)
              Workers Planned: 2
              Params Evaluated: $0
              Workers Launched: 2
              ->  Sort  (cost=60136.42..60138.80 rows=949 width=37) (actual time=1.286..1.311 rows=302 loops=3)
                    Sort Key: stock_price.code
                    Sort Method: quicksort  Memory: 95kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Partial HashAggregate  (cost=60077.63..60089.50 rows=949 width=37) (actual time=1.098..1.179 rows=302 loops=3)
                          Group Key: stock_price.code
                          Batches: 1  Memory Usage: 577kB
                          Worker 0:  Batches: 1  Memory Usage: 73kB
                          Worker 1:  Batches: 1  Memory Usage: 73kB
                          ->  Parallel Index Scan using idx_stock_price_date on stock_price  (cost=0.43..59671.39 rows=81248 width=13) (actual time=0.017..0.745 rows=1427 loops=3)
                                Index Cond: (date > $0)
                                Filter: (value > 0)
                                Rows Removed by Filter: 222
Planning Time: 1.331 ms
Execution Time: 37.413 ms