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