Re: Use of additional index columns in rows filtering

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, James Coleman <jtc331(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Maxim Ivanov <hi(at)yamlcoder(dot)me>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>, Konstantin Knizhnik <knizhnik(at)garret(dot)ru>, markus(dot)winand(at)winand(dot)at
Subject: Re: Use of additional index columns in rows filtering
Date: 2023-08-08 16:24:29
Message-ID: CAH2-WzkSvK_+kSqvEeMeZ7_kKPGYLnPXTP1rDzp0hzprsUeYcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Aug 8, 2023 at 7:28 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> Are you sure? Because if I try with the 20230716 patch, I get this plan
> (after disabling bitmapscan):

> So the condition is recognized as index filter. Or did you mean
> something different?

No, I was just wrong about this (and about inequalities in general). I
now see why the planner preferred a bitmap scan, which makes sense.
Apologies.

> Not sure. A couple messages back I suggested that maybe there is a way
> to check which expression would be safe to evaluate before checking the
> visibility. This seems similar, although what you're suggesting really
> applies to the "transformed" SAOP, and I'm not sure it can be extended
> to the original SAOP.

The transformation doesn't necessarily have to happen in order for it
to be possible in principle (and correct). My point was that there are
a handful of important types of expressions (SAOPs among them, but
possibly also RowCompareExpr and IS NULL tests) that are "index quals
in spirit". These expressions therefore don't seem to need visibility
checks at all -- the index qual guarantees "apply transitively".

It's possible that an approach that focuses on leakproof quals won't
have any problems, and will be strictly better than "extending the
index qual guarantees to index-qual-like expressions". Really not sure
about that.

In any case I see a huge amount of value in differentiating between
cases that need visibility checks (if only via the VM) and those that
do not, ever. I'm speaking very generally here -- nothing to do with
my adversarial tenk1 test case. It's weird that index quals have such
a massive advantage over even simple index filters -- that feels
artificial. I suggest that you focus on that aspect, since it has the
potential to make what is already a compelling patch into a much more
compelling patch.

--
Peter Geoghegan

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2023-08-08 16:34:58 Re: Cirrus-ci is lowering free CI cycles - what to do with cfbot, etc?
Previous Message Robert Haas 2023-08-08 16:05:26 Re: generic plans and "initial" pruning