Hi all,
I have a query where Postgresql (11.9 at the moment) is making an odd plan choice, choosing to use index scans which require filtering out millions of rows, rather than "just" doing an aggregate over the rows the where clause targets which is much faster.
AFAICT it isn't a statistics problem, at least increasing the stats target and analyzing the table doesn't seem to fix the problem.
The query looks like:
======
explain analyze select min(risk_id),max(risk_id) from risk where time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=217.80..217.81 rows=1 width=16) (actual time=99722.685..99722.687 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.57..108.90 rows=1 width=8) (actual time=38454.537..38454.538 rows=1 loops=1)
-> Index Scan using risk_risk_id_key on risk (cost=0.57..9280362.29 rows=85668 width=8) (actual time=38454.535..38454.536 rows=1 loops=1)
Index Cond: (risk_id IS NOT NULL)
Filter: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Rows Removed by Filter: 161048697
InitPlan 2 (returns $1)
-> Limit (cost=0.57..108.90 rows=1 width=8) (actual time=61268.140..61268.140 rows=1 loops=1)
-> Index Scan Backward using risk_risk_id_key on risk risk_1 (cost=0.57..9280362.29 rows=85668 width=8) (actual time=61268.138..61268.139 rows=1 loops=1)
Index Cond: (risk_id IS NOT NULL)
Filter: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Rows Removed by Filter: 41746396
Planning Time: 0.173 ms
Execution Time: 99722.716 ms
(15 rows)
======
If I add a count(*) so it has to consider all rows in the range for that part of the query and doesn't consider using the other index for a min/max "shortcut" then the query is fast.
======
explain analyze select min(risk_id),max(risk_id), count(*) from risk where time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4376.67..4376.68 rows=1 width=24) (actual time=30.011..30.012 rows=1 loops=1)
-> Index Scan using risk_time_idx on risk (cost=0.57..3734.17 rows=85667 width=8) (actual time=0.018..22.441 rows=90973 loops=1)
Index Cond: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Planning Time: 0.091 ms
Execution Time: 30.045 ms
(5 rows)
======
My count() hack works around my immediate problem but I'm trying to get my head round why Postgres chooses the plan it does without it, in case there is some general problem with my configuration that may negatively effect other areas, or there's something else I am missing.
Any ideas?
Paul McGarry
I have a query where Postgresql (11.9 at the moment) is making an odd plan choice, choosing to use index scans which require filtering out millions of rows, rather than "just" doing an aggregate over the rows the where clause targets which is much faster.
AFAICT it isn't a statistics problem, at least increasing the stats target and analyzing the table doesn't seem to fix the problem.
The query looks like:
======
explain analyze select min(risk_id),max(risk_id) from risk where time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=217.80..217.81 rows=1 width=16) (actual time=99722.685..99722.687 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.57..108.90 rows=1 width=8) (actual time=38454.537..38454.538 rows=1 loops=1)
-> Index Scan using risk_risk_id_key on risk (cost=0.57..9280362.29 rows=85668 width=8) (actual time=38454.535..38454.536 rows=1 loops=1)
Index Cond: (risk_id IS NOT NULL)
Filter: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Rows Removed by Filter: 161048697
InitPlan 2 (returns $1)
-> Limit (cost=0.57..108.90 rows=1 width=8) (actual time=61268.140..61268.140 rows=1 loops=1)
-> Index Scan Backward using risk_risk_id_key on risk risk_1 (cost=0.57..9280362.29 rows=85668 width=8) (actual time=61268.138..61268.139 rows=1 loops=1)
Index Cond: (risk_id IS NOT NULL)
Filter: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Rows Removed by Filter: 41746396
Planning Time: 0.173 ms
Execution Time: 99722.716 ms
(15 rows)
======
If I add a count(*) so it has to consider all rows in the range for that part of the query and doesn't consider using the other index for a min/max "shortcut" then the query is fast.
======
explain analyze select min(risk_id),max(risk_id), count(*) from risk where time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4376.67..4376.68 rows=1 width=24) (actual time=30.011..30.012 rows=1 loops=1)
-> Index Scan using risk_time_idx on risk (cost=0.57..3734.17 rows=85667 width=8) (actual time=0.018..22.441 rows=90973 loops=1)
Index Cond: (("time" >= '2020-01-20 15:00:07+00'::timestamp with time zone) AND ("time" < '2020-01-21 15:00:08+00'::timestamp with time zone))
Planning Time: 0.091 ms
Execution Time: 30.045 ms
(5 rows)
======
My count() hack works around my immediate problem but I'm trying to get my head round why Postgres chooses the plan it does without it, in case there is some general problem with my configuration that may negatively effect other areas, or there's something else I am missing.
Any ideas?
Paul McGarry
