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

"Olivier Poquet" <opoquet(at)plumdev(dot)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