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

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.

There are only around 900 rows in a date so it looks like 14.6 filters the
date first (which is indexed) while 15.2 filters the length first.

Although when not joined with the cte the query runs fast even with the
length filter

select
s.code,
s.close,
100.0 * (s.close - s.open) / s.open as chg,
s.value,
s.volume
from
stock_price s
where
s.date = '2023-02-20' and
length(s.code) = 4 and
s.open > 0 and
s.value > 0

Index Scan using idx_stock_price_date on stock_price s (cost=0.43..1152.55
rows=1 width=57) (actual time=0.074..0.461 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
Planning Time: 0.098 ms
Execution Time: 0.487 ms