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

On Mon, Feb 20, 2023 at 11:44 PM Charles <peacech(at)gmail(dot)com> wrote:

> On Mon, Feb 20, 2023 at 11:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Charles <peacech(at)gmail(dot)com> writes:
>> > 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.
>>
>> You need to do something to fix this poor row-count estimate:
>>
>> > -> 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
>>
>> Considering that 14.x also estimated this as returning only one row,
>> I'm fairly surprised that you didn't get the same poor plan choice there.
>>
>> The length(code) condition suggests a fairly poor choice of data
>> representation; can you change that?
>>
>
> Thanks, removing the length filter do restore the execution time to 30ms.
>
>
>
>
>
>
>
>
Wrapping the query with a select * from (...) t where length(code) = 4 puts
the execution time back to 27 seconds.

This is a bit unexpected since I expect that the result from the inner
query to be executed first and then filtered.

select * from (
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
inner join vol_avg va on
s.code = va.code
where
s.date = '2023-02-20' and
s.open > 0 and
s.value > 0
) t where length(code) = 4

Nested Loop (cost=63003.26..64440.14 rows=1 width=89) (actual
time=22.859..26784.170 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.084..4.024 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=33.983..34.347 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.013..0.013 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.012..0.012
rows=6 loops=1)
Heap Fetches: 6
-> Gather Merge (cost=63002.40..63236.91 rows=2010 width=37)
(actual time=33.972..34.045 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.885..0.896 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.696..0.776 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.335
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.177..0.177 rows=8917 loops=779)
Index Cond: (date > $0)
Planning Time: 0.362 ms
Execution Time: 26784.929 ms