Re: Trying to understand why a query is filtering when there is a composite index

On Mon, Aug 19, 2024 at 12:06 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > It was fixed? At least on 17.
>
> Oh, sorry, I was thinking of a related problem that doesn't apply
> here: matching indexes on expressions to fragments of a filter
> condition. However, the fact that the OP's EXPLAIN shows heap
> fetches from a supposedly all-visible table suggests that his
> IN isn't getting optimized that way.

As you pointed out, the number of tuples filtered out by the filter
qual is only a small proportion of the total in this particular
example (wasn't really paying attention to that aspect myself). I
guess that that factor makes the Postgres 17 nbtree SAOP work almost
irrelevant to the exact scenario shown, since even if true index quals
could be used they'd only save at most one heap page access.

I would still expect the 17 work to make the query slightly faster,
since my testing showed that avoiding expression evaluation is
slightly faster. Plus it would *definitely* make similar queries
faster by avoiding heap access entirely -- cases where the use of true
index quals can eliminate most heap page accesses.

> No heap fetches, so it must have done the filter from the index.
> Why not in the original case?

My guess is that that's due to some kind of naturally occuring
correlation. The few unset-in-VM pages are disproportionately likely
to become heap fetches.

The difficulty at predicting this kind of variation argues for an
approach that makes as many decisions as possible at runtime. This is
particularly true of how we skip within the index scan. I wouldn't
expect skipping to be useful in the exact scenario shown, but why not
be open to the possibility? If the planner only has one choice then
there are no wrong choices.

--
Peter Geoghegan