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

Just for my own knowledge:

This index covers both columns needed in the predicate/projection, and the
visibility bit is almost always set, why does it need to go to the heap at
all and doesn't just get what it needs from the index?
Or does scanning the _vm table count as a heap access in the planner ?

On Mon, 19 Aug 2024 at 10:21, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

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