Re: Index Skip Scan - Mailing list pgsql-hackers

From Jesper Pedersen
Subject Re: Index Skip Scan
Date
Msg-id 3e5f8a7d-67a4-46c8-e113-0a7e2dad2db6@redhat.com
Whole thread Raw
In response to Re: Index Skip Scan  (Thomas Munro <thomas.munro@gmail.com>)
Responses Re: Index Skip Scan
List pgsql-hackers
Hi,

On 7/4/19 6:59 AM, Thomas Munro wrote:
>> For the MIN query you just need a path with Pathkeys: { i ASC, j ASC
>> }, UniqueKeys: { i, j }, doing the MAX query you just need j DESC.
> 

David, are you thinking about something like the attached ?

Some questions.

* Do you see UniqueKey as a "complete" planner node ?
   - I didn't update the nodes/*.c files for this yet

* Is a UniqueKey with a list of EquivalenceClass best, or a list of 
UniqueKey with a single EquivalenceClass

Likely more questions around this coming -- should this be a separate 
thread ?

Based on this I'll start to update the v21 patch to use UniqueKey, and 
post a new version.

> While updating the Loose Index Scan wiki page with links to other
> products' terminology on this subject, I noticed that MySQL can
> skip-scan MIN() and MAX() in the same query.  Hmm.  That seems quite
> desirable.  I think it requires a new kind of skipping: I think you
> have to be able to skip to the first AND last key that has each
> distinct prefix, and then stick a regular agg on top to collapse them
> into one row.  Such a path would not be so neatly describable by
> UniqueKeys, or indeed by the amskip() interface in the current patch.
> I mention all this stuff not because I want us to run before we can
> walk, but because to be ready to commit the basic distinct skip scan
> feature, I think we should know approximately how it'll handle the
> future stuff we'll need.
> 

Thomas, do you have any ideas for this ? I can see that MySQL did the 
functionality in two change sets (base and function support), but like 
you said we shouldn't paint ourselves into a corner.

Feedback greatly appreciated.

Best regards,
  Jesper

Attachment

pgsql-hackers by date:

Previous
From: James Coleman
Date:
Subject: Re: [PATCH] Incremental sort (was: PoC: Partial sort)
Next
From: Antonin Houska
Date:
Subject: Re: [HACKERS] WIP: Aggregation push-down