Re: Use of additional index columns in rows filtering

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?

Regards,
    Jeff Davis