Re: query plan using partial index expects a much larger number of rows than is possible

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
doa 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
>