Query run in 27s with 15.2 vs 37ms with 14.6

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