On Mon, Feb 20, 2023 at 11:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Charles <peacech@gmail.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
