Re: Index Skip Scan

From: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, florisvannee(at)optiver(dot)com, pg(at)bowt(dot)ie, jesper(dot)pedersen(at)redhat(dot)com, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, jtc331(at)gmail(dot)com, rafia(dot)pghackers(at)gmail(dot)com, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, bhushan(dot)uparkar(at)gmail(dot)com, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Subject: Re: Index Skip Scan
Date: 2020-03-24 16:39:19
Message-ID: 20200324163919.7nwqe72xzfatix4y@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> On Wed, Mar 11, 2020 at 11:17:51AM +1300, David Rowley wrote:
> Yes, I was complaining that a ProjectionPath breaks the optimisation
> and I don't believe there's any reason that it should.
> I believe the way to make that work correctly requires paying
> attention to the Path's uniquekeys rather than what type of path it
> is.

Thanks for the suggestion. As a result of the discussion I've modified
the patch, does it look similar to what you had in mind?

In this version if all conditions are met and there are corresponding
unique keys, a new index skip scan path will be added to
unique_pathlists. In case if requested distinct clauses match with
unique keys, create_distinct_paths can choose this path without needen
to know what kind of path is it. Also unique_keys are passed through
ProjectionPath, so optimization for the example mentioned in this thread
before now should work (I've added one test for that).

I haven't changed anything about UniqueKey structure itself (one of the
suggestions was about Expr instead of EquivalenceClass), but I believe
we need anyway to figure out how two existing imlementation (in this
patch and from [1]) of this idea can be connected.


Attachment Content-Type Size
v33-0001-Unique-key.patch text/x-diff 25.7 KB
v33-0002-Index-skip-scan.patch text/x-diff 80.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2020-03-24 16:42:21 Re: Index Skip Scan
Previous Message Konstantin Knizhnik 2020-03-24 16:24:15 Re: Built-in connection pooler