pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOdd (slow) plan choice with min/max
6+ messages / 4 participants
[nested] [flat]
* Odd (slow) plan choice with min/max
@ 2021-03-23 04:00 Paul McGarry <paul@paulmcgarry.com>
0 siblings, 1 reply; 6+ messages in thread
From: Paul McGarry @ 2021-03-23 04:00 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd (slow) plan choice with min/max
@ 2021-03-23 05:13 Justin Pryzby <pryzby@telsasoft.com>
parent: Paul McGarry <paul@paulmcgarry.com>
0 siblings, 1 reply; 6+ messages in thread
From: Justin Pryzby @ 2021-03-23 05:13 UTC (permalink / raw)
To: Paul McGarry <paul@paulmcgarry.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Mar 23, 2021 at 03:00:38PM +1100, Paul McGarry wrote:
> 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.
> 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';
I'm guessing the time and ID columns are highly correlated...
So the planner thinks it can get the smallest ID by scanning the ID index, but
then ends up rejecting the first 161e6 rows for which the time is too low, and
fails the >= condition.
And thinks it can get the greatest ID by backward scanning the ID idx, but ends
up rejecting/filtering the first 41e6 rows, for which the time is too high,
failing the < condition.
This is easy to reproduce:
postgres=# DROP TABLE t; CREATE TABLE t AS SELECT a i,a j FROM generate_series(1,999999)a; CREATE INDEX ON t(j); ANALYZE t;
postgres=# explain analyze SELECT min(j), max(j) FROM t WHERE i BETWEEN 9999 AND 99999;
One solution seems to be to create an index on (i,j), but I don't know if
there's a better way.
--
Justin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd (slow) plan choice with min/max
@ 2021-03-23 06:51 Paul McGarry <paul@paulmcgarry.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 6+ messages in thread
From: Paul McGarry @ 2021-03-23 06:51 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, 23 Mar 2021 at 16:13, Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Tue, Mar 23, 2021 at 03:00:38PM +1100, Paul McGarry wrote:
> > 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.
>
> > 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';
>
> I'm guessing the time and ID columns are highly correlated...
>
> So the planner thinks it can get the smallest ID by scanning the ID index,
> but
> then ends up rejecting the first 161e6 rows for which the time is too low,
> and
> fails the >= condition.
>
> And thinks it can get the greatest ID by backward scanning the ID idx, but
> ends
> up rejecting/filtering the first 41e6 rows, for which the time is too high,
> failing the < condition.
>
Yes, the columns are highly correlated, but that alone doesn't seem like it
should be sufficient criteria to choose this plan.
Ie the selection criteria (1 day of data about a year ago) has a year+
worth of data after it and probably a decade of data before it, so anything
walking a correlated index from top or bottom is going to have to walk past
a lot of data before it gets to data that fits the criteria.
> One solution seems to be to create an index on (i,j), but I don't know if
> there's a better way.
>
>
Adding the count() stops the planner considering the option so that will
work for now.
My colleague has pointed out that we had the same issue in November and I
came up with the count() workaround then too, but somehow seem to have
forgotten it in the meantime and reinvented it today. I wonder if I posted
to pgsql-performance then too.....
Maybe time for me to read the PG12 release notes....
Paul
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd (slow) plan choice with min/max
@ 2021-03-23 13:07 Rick Otten <rottenwindfish@gmail.com>
parent: Paul McGarry <paul@paulmcgarry.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rick Otten @ 2021-03-23 13:07 UTC (permalink / raw)
To: Paul McGarry <paul@paulmcgarry.com>; pgsql-performance@lists.postgresql.org
On Tue, Mar 23, 2021 at 2:52 AM Paul McGarry <paul@paulmcgarry.com> wrote:
>
>
> On Tue, 23 Mar 2021 at 16:13, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
>> On Tue, Mar 23, 2021 at 03:00:38PM +1100, Paul McGarry wrote:
>> > 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.
>>
>> > 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';
>>
>> I'm guessing the time and ID columns are highly correlated...
>>
>> So the planner thinks it can get the smallest ID by scanning the ID
>> index, but
>> then ends up rejecting the first 161e6 rows for which the time is too
>> low, and
>> fails the >= condition.
>>
>> And thinks it can get the greatest ID by backward scanning the ID idx,
>> but ends
>> up rejecting/filtering the first 41e6 rows, for which the time is too
>> high,
>> failing the < condition.
>>
>
> Yes, the columns are highly correlated, but that alone doesn't seem like
> it should be sufficient criteria to choose this plan.
> Ie the selection criteria (1 day of data about a year ago) has a year+
> worth of data after it and probably a decade of data before it, so anything
> walking a correlated index from top or bottom is going to have to walk past
> a lot of data before it gets to data that fits the criteria.
>
I assume you have a statistic on the correlated columns, ie `create
statistic` ?
If you can't use partitions on your date column, can you use partial
indexes instead? Or a functional index with min() over day and max() over
day?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd (slow) plan choice with min/max
@ 2021-03-23 21:38 Paul McGarry <paul@paulmcgarry.com>
parent: Rick Otten <rottenwindfish@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Paul McGarry @ 2021-03-23 21:38 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, 24 Mar 2021 at 00:07, Rick Otten <rottenwindfish@gmail.com> wrote:
>
>> Yes, the columns are highly correlated, but that alone doesn't seem like
>> it should be sufficient criteria to choose this plan.
>> Ie the selection criteria (1 day of data about a year ago) has a year+
>> worth of data after it and probably a decade of data before it, so anything
>> walking a correlated index from top or bottom is going to have to walk past
>> a lot of data before it gets to data that fits the criteria.
>>
>
>
> I assume you have a statistic on the correlated columns, ie `create
> statistic` ?
>
I didn't, but adding
======
CREATE STATISTICS risk_risk_id_time_correlation_stats ON risk_id,time FROM
risk;
analyze risk;
======
doesn't seem to help.
I get the same plan before/after. Second run was faster, but just because
data was hot.
> If you can't use partitions on your date column, can you use partial
> indexes instead? Or a functional index with min() over day and max() over
> day?
>
I don't particularly want to add more weird indexes to solve this one
particular query. as the existing risk_id index should make it efficient
enough if only the planner chose to use it. This is part of an archiving
job, identifying sections of historical data, so not a query that needs to
be super optimised, but essentially doing a full table scan
backwards/forwards as it is now is doing a lot of unnecessary IO that would
be best left free for more time sensitive queries.My count(() workaround
works so we can use that.
I'm more interested in understanding why the planner makes what seems to be
an obviously bad choice.
Paul
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd (slow) plan choice with min/max
@ 2021-03-24 09:26 Yoan SULTAN <yeyeyo@gmail.com>
parent: Paul McGarry <paul@paulmcgarry.com>
0 siblings, 0 replies; 6+ messages in thread
From: Yoan SULTAN @ 2021-03-24 09:26 UTC (permalink / raw)
To: Paul McGarry <paul@paulmcgarry.com>; +Cc: pgsql-performance@lists.postgresql.org
Another workaround could be :
explain analyze select min(risk_id),max(risk_id) from (select * from risk
where time>='2020-01-20 15:00:07+00' and time < '2020-01-21 15:00:08+00')
t;
in order to force the planner to use first the timestamp index.
However, I agree with you; we meet a planner bad behavior here.
Regards,
Yoan SULTAN
Le mar. 23 mars 2021 à 22:38, Paul McGarry <paul@paulmcgarry.com> a écrit :
>
>
> On Wed, 24 Mar 2021 at 00:07, Rick Otten <rottenwindfish@gmail.com> wrote:
>
>>
>>> Yes, the columns are highly correlated, but that alone doesn't seem like
>>> it should be sufficient criteria to choose this plan.
>>> Ie the selection criteria (1 day of data about a year ago) has a year+
>>> worth of data after it and probably a decade of data before it, so anything
>>> walking a correlated index from top or bottom is going to have to walk past
>>> a lot of data before it gets to data that fits the criteria.
>>>
>>
>>
>> I assume you have a statistic on the correlated columns, ie `create
>> statistic` ?
>>
>
> I didn't, but adding
> ======
> CREATE STATISTICS risk_risk_id_time_correlation_stats ON risk_id,time FROM
> risk;
> analyze risk;
> ======
> doesn't seem to help.
> I get the same plan before/after. Second run was faster, but just because
> data was hot.
>
>
>
>> If you can't use partitions on your date column, can you use partial
>> indexes instead? Or a functional index with min() over day and max() over
>> day?
>>
>
> I don't particularly want to add more weird indexes to solve this one
> particular query. as the existing risk_id index should make it efficient
> enough if only the planner chose to use it. This is part of an archiving
> job, identifying sections of historical data, so not a query that needs to
> be super optimised, but essentially doing a full table scan
> backwards/forwards as it is now is doing a lot of unnecessary IO that would
> be best left free for more time sensitive queries.My count(() workaround
> works so we can use that.
> I'm more interested in understanding why the planner makes what seems to
> be an obviously bad choice.
>
> Paul
>
--
Regards,
Yo.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-03-24 09:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-23 04:00 Odd (slow) plan choice with min/max Paul McGarry <paul@paulmcgarry.com>
2021-03-23 05:13 ` Justin Pryzby <pryzby@telsasoft.com>
2021-03-23 06:51 ` Paul McGarry <paul@paulmcgarry.com>
2021-03-23 13:07 ` Rick Otten <rottenwindfish@gmail.com>
2021-03-23 21:38 ` Paul McGarry <paul@paulmcgarry.com>
2021-03-24 09:26 ` Yoan SULTAN <yeyeyo@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox