Re: Index Skip Scan (new UniqueKeys)

From: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, Floris Van Nee <florisvannee(at)optiver(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Subject: Re: Index Skip Scan (new UniqueKeys)
Date: 2021-03-14 14:48:33
Message-ID: 20210314144833.tbfnn3pbrpjgl56z@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> On Sat, Dec 05, 2020 at 06:55:42PM +0100, Dmitry Dolgov wrote:
> > On Tue, Dec 01, 2020 at 10:59:22PM +0200, Heikki Linnakangas wrote:
> >
> > > > - Does this optimization apply to bitmap index scans?
> > >
> > > No, from what I understand it doesn't.
> >
> > Would it be hard to add? Don't need to solve everything in the first
> > version of this, but I think in principle you could do the same
> > optimization for bitmap index scans, so if the current API can't do it,
> > that's maybe an indication that the API isn't quite right.
> I would expect it should not be hard as at the moment all parts seems
> relatively generic. But of course I need to check, while it seems no one
> had bitmap index scans in mind while developing this patch.
> On Sun, Dec 06, 2020 at 09:27:08AM +1300, Thomas Munro wrote:
> FWIW here's what I wrote about that years ago[1]:
> [1]

Thanks, that clarifies this topic a bit.

> > If you care whether the qual is evaluated by the index AM or not, you need
> > to also check that the operator is indexable. Attached is a query that
> > demonstrates that problem.
> > ...
> > Also, you should probably check that the index quals are in the operator
> > family as that used for the DISTINCT.
> Yes, good point, will change this in the next version.

Sorry for such long silence, now I've got a bit of free time after
subscripting patch to work on this one. Here is rebased version with a
few changes to address Heikki feedback about checking if the qual
operator is indexable. But...

This version is based on the old version of UniqueKey patch (first two
attached patches), mostly because IIUC there is still no final version
of it ([1], [2]). This means index skip scan could be reviewed and
discussed (and I'm planning to review the current design to see if it's
possible to improve it in the view of the latest changes), but
independently of the UniqueKey integration as it's subject to change.
But I'm afraid if things will go as it is and there will be not much
progress with the UniqueKey patch, I will have to withdraw this one
until everything is sorted out there.


Attachment Content-Type Size
v38-0001-Introduce-RelOptInfo-notnullattrs-attribute.patch text/x-diff 4.8 KB
v38-0002-Introduce-UniqueKey-attributes-on-RelOptInfo-str.patch text/x-diff 59.3 KB
v38-0003-Extend-UniqueKeys.patch text/x-diff 13.0 KB
v38-0004-Index-skip-scan.patch text/x-diff 45.0 KB
v38-0005-Btree-implementation-of-skipping.patch text/x-diff 44.3 KB
v38-0006-Index-skip-scan-documentation.patch text/x-diff 4.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2021-03-14 14:54:46 Re: pgbench - add pseudo-random permutation function
Previous Message Tatsuo Ishii 2021-03-14 12:14:49 Re: Using COPY FREEZE in pgbench