From: Olivier Poquet <opoquet@plumdev.com>
To: 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 20:53:09 -0400
Message-ID: <7416fcaf-bbae-4f10-a790-769430f9e897@www.fastmail.com> (raw)
In-Reply-To: <1455831.1603927818@sss.pgh.pa.us>
References: <59eb4cf7-4c68-448d-a2d8-541464803353@www.fastmail.com>
<1455831.1603927818@sss.pgh.pa.us>
Thanks Tom,
That makes perfect sense.
I'd already gone the route of materializing the condition but I didn't even realize that generated columns was an option (I'd done the same with triggers instead). So thanks a lot of that too!
--
Olivier Poquet
opoquet@plumdev.com
On Wed, Oct 28, 2020, at 7:30 PM, Tom Lane wrote:
> "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: opoquet@plumdev.com, pgsql-performance@lists.postgresql.org
Subject: Re: Re: query plan using partial index expects a much larger number of rows than is possible
In-Reply-To: <7416fcaf-bbae-4f10-a790-769430f9e897@www.fastmail.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