Re: Use of additional index columns in rows filtering

Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> writes:
> On 2/15/23 09:57, Maxim Ivanov wrote:
>> TLDR; additional index column B specified in CREATE INDEX .. (A)
>> INCLUDE(B) is not used to filter rows in queries like WHERE B = $1
>> ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L

> Seems doable, unless I'm missing some fatal issue.

Partly this is lack of round tuits, but there's another significant
issue: there very likely are index entries corresponding to dead heap
rows. Applying random user-defined quals to values found in such rows
could produce semantic anomalies; for example, divide-by-zero failures
even though you deleted all the rows having a zero in that column.

This isn't a problem for operators found in operator families, because
we trust those to not have undesirable side effects like raising
data-dependent errors. But it'd be an issue if we started to apply
quals that aren't index quals directly to index rows before doing
the heap liveness check. (And, of course, once you've fetched the
heap row there's no point in having a special path for columns
available from the index.)

regards, tom lane