Date: Tue, 23 Mar 2021 00:13:03 -0500
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
Message-ID: <20210323051303.GX4203@telsasoft.com>
References: 
 <CAPrE0SYrq9VvWiKqNMKOhkRu0c-zTGsSOnSmL3TrHZNt6KOLUw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAPrE0SYrq9VvWiKqNMKOhkRu0c-zTGsSOnSmL3TrHZNt6KOLUw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210323051303.GX4203%40telsasoft.com>
Precedence: bulk

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