Re: Use of additional index columns in rows filtering

On 7/19/23 01:22, Jeff Davis wrote:
> On Wed, 2023-07-19 at 00:36 +0200, Tomas Vondra wrote:
>>> * I'm confused about the relationship of an IOS to an index filter.
>>> It
>>> seems like the index filter only works for an ordinary index scan?
>>> Why
>>> is that?
>>
>> What would it do for IOS?
>
> The way it's presented is slightly confusing. If you have table x with
> and index on column i, then:
>
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT i, j FROM x WHERE i = 7 and (i % 1000 = 7);
>
> Index Scan using x_idx on x (cost=0.42..8.45 rows=1 width=8)
> (actual time=0.094..0.098 rows=1 loops=1)
> Index Cond: (i = 7)
> Index Filter: ((i % 1000) = 7)
>
> But if you remove "j" from the target list, you get:
>
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT i FROM x WHERE i = 7 and (i % 1000 = 7);
>
> Index Only Scan using x_idx on x (cost=0.42..4.45 rows=1 width=4)
> (actual time=0.085..0.088 rows=1 loops=1)
> Index Cond: (i = 7)
> Filter: ((i % 1000) = 7)
>
> The confused me at first because the "Filter" in the second plan means
> the same thing as the "Index Filter" in the first plan. Should we call
> the filter in an IOS an "Index Filter" too? Or is that redundant?
>

I agree the naming in explain is a bit confusing.

I wonder if Andres was right (in the index prefetch thread) that
splitting regular index scans and index-only scans may not be ideal. In
a way, this patch moves those nodes closer, both in capability and code
(because now both use index_getnext_tid etc.).

regards

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