From: Tom Lane <tgl@sss.pgh.pa.us>
To: Olivier Poquet <opoquet@plumdev.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: query plan using partial index expects a much larger number of rows than is possible
Date: Wed, 28 Oct 2020 19:30:18 -0400
Message-ID: <1455831.1603927818@sss.pgh.pa.us> (raw)
In-Reply-To: <59eb4cf7-4c68-448d-a2d8-541464803353@www.fastmail.com>
References: <59eb4cf7-4c68-448d-a2d8-541464803353@www.fastmail.com>
"Olivier Poquet" <opoquet@plumdev.com> writes:
> Looking at it in more detail, I found that the planner is assuming that I'll get millions of rows back even when I do a simple query that does an index scan on my partial index:
We don't look at partial-index predicates when trying to estimate the
selectivity of a WHERE clause. It's not clear to me whether that'd be
a useful thing to do, or whether it could be shoehorned into the system
easily. (One big problem is that while the index size could provide
an upper bound, it's not apparent how to combine that knowledge with
selectivities of unrelated conditions. Also, it's riskier to extrapolate
a current rowcount estimate from stale relpages/reltuples data for an
index than it is for a table, because the index is less likely to scale
up linearly.)
If this particular query is performance-critical, you might consider
materializing the condition, that is something like
create table orderitems (
... ,
committed_unfulfilled bool GENERATED ALWAYS AS
(LEAST(committed, quantity) > fulfilled) STORED
);
and then your queries and your partial-index predicate must look
like "WHERE committed_unfulfilled". Having done this, ANALYZE
would gather stats on the values of that column and the WHERE
clauses would be estimated accurately.
regards, tom lane
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: tgl@sss.pgh.pa.us, opoquet@plumdev.com, pgsql-performance@lists.postgresql.org
Subject: Re: query plan using partial index expects a much larger number of rows than is possible
In-Reply-To: <1455831.1603927818@sss.pgh.pa.us>
* 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