Re: Use of additional index columns in rows filtering - Mailing list pgsql-hackers

From Jeff Davis
Subject Re: Use of additional index columns in rows filtering
Date
Msg-id ab8dd095b22f770b7b870f531fb2694991c6c2c5.camel@j-davis.com
Whole thread Raw
In response to Re: Use of additional index columns in rows filtering  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
Responses Re: Use of additional index columns in rows filtering  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: [PoC] Federated Authn/z with OAUTHBEARER
Next
From: "Fujii.Yuki@df.MitsubishiElectric.co.jp"
Date:
Subject: RE: Partial aggregates pushdown