Re: Query run in 27s with 15.2 vs 37ms with 14.6 - Mailing list pgsql-bugs
| From | Charles |
|---|---|
| Subject | Re: Query run in 27s with 15.2 vs 37ms with 14.6 |
| Date | |
| Msg-id | CABthHP_KBYvDvPEfrGEtP=ghbPKrxD5asAaHRX1F4ajJ_=-vcw@mail.gmail.com Whole thread Raw |
| In response to | Re: Query run in 27s with 15.2 vs 37ms with 14.6 (Charles <peacech@gmail.com>) |
| Responses |
Re: Query run in 27s with 15.2 vs 37ms with 14.6
|
| List | pgsql-bugs |
On Mon, Feb 20, 2023 at 11:44 PM Charles <peacech@gmail.com> wrote:
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.
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
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
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
pgsql-bugs by date: