From: Justin Pryzby <pryzby@telsasoft.com>
To: Paul McGarry <paul@paulmcgarry.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Odd (slow) plan choice with min/max
Date: Tue, 23 Mar 2021 00:13:03 -0500
Message-ID: <20210323051303.GX4203@telsasoft.com> (raw)
In-Reply-To: <CAPrE0SYrq9VvWiKqNMKOhkRu0c-zTGsSOnSmL3TrHZNt6KOLUw@mail.gmail.com>
References: <CAPrE0SYrq9VvWiKqNMKOhkRu0c-zTGsSOnSmL3TrHZNt6KOLUw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, paul@paulmcgarry.com, pgsql-performance@lists.postgresql.org
Subject: Re: Odd (slow) plan choice with min/max
In-Reply-To: <20210323051303.GX4203@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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