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@xxxxxxxxxxx On Wed, Oct 28, 2020, at 7:30 PM, Tom Lane wrote: > "Olivier Poquet" <opoquet@xxxxxxxxxxx> 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 >
