Subject: Re: Use of additional index columns in rows filtering

From: Tom Lane <[email-masked]>
Date: Wed, 15 Feb 2023 10:18:12 -0500

Tomas Vondra <[email-masked]> 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

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 15 Feb 2023 18:01:50 +0100

> available from the index.)

Sure, but we can do the same VM check as index-only scan, right?

That would save some of the I/O to fetch the heap tuple, as long as the
page is all-visible and the filter eliminates the tuples. It makes the
costing a bit trickier, because it needs to consider both how many pages
are all-visible and selectivity of the condition.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Maxim Ivanov <[email-masked]>
Date: Wed, 15 Feb 2023 17:15:50 +0000

> available from the index.)

Assuming operators are pure and don't have global side effects, is it possible to ignore any error during that check? If tuple is not visible it shouldn't matter, if it is visible then error will be reported by the same routine which does filtering now (ExecQual?).

If not, then limiting this optimization to builtin ops is something I can live with :)