Postgres Pro
Downloads
Home   >   mailing lists

Thread: Generic Index Skip Scan

Generic Index Skip Scan

From
Floris Van Nee
Date:

Besides the great efforts that Dmitry et al. are putting into the skip scan for DISTINCT queries [1], I’m also still keen on extending the use of it further. I’d like to address the limited cases in which skipping can occur here. A few months ago I shared an initial rough patch that provided a generic skip implementation, but lacked the proper planning work [2]. I’d like to share a second patch set that provides an implementation of the planner as well. Perhaps this can lead to some proper discussions how we’d like to shape this patch further.

 

Please see [2] for an introduction and some rough performance comparisons. This patch improves upon those, because it implements proper cost estimation logic. It will now only choose the skip scan if it’s deemed to be cheaper than using a regular index scan. Other than that, all the features are still there. The skip scan can be used in many more types of queries than in the original DISTINCT patch as provided in [1], making it more performant and also more predictable for users.

 

I’m keen on receiving feedback on this idea and on the patch. I believe it could be a great feature that is useful to many users. However, when I posted the previous version of the patch, only Thomas expressed his explicit interest in the feature. It would be useful for me to know if there’s enough interest here. Please speak out as well if you can’t (currently) review, but do think that this feature is worth the effort.

 

I’m sure there are still plenty of things that need to be improved. I have some in mind, but at the moment it’s hard for me to judge which ones are really important and which ones are not. I think I really need someone with more experience of the code looking at this for feedback.

 

v9-0001 + v9-0002 are Andy’s UniqueKeys patches [3]

v01-0001 is a slightly modified version of Dmitry’s extension of unique keys patch (his lastest patch plus the diff patch that I posted in the original index skip thread)

v01-0002 is the bulk of the work: the skip implementation, indexam interface and implementation for DISTINCT queries

v01-0003 is the additional planner work to add support for skipping in regular index scans (non-DISTINCT)

 

-Floris

 

[1] https://www.postgresql.org/message-id/flat/20200609102247.jdlatmfyeecg52fi@localhost

[2] https://www.postgresql.org/message-id/c5c5c974714a47f1b226c857699e8680%40opammb0561.comp.optiver.com

[3] https://www.postgresql.org/message-id/flat/CAKU4AWrwZMAL=uaFUDMf4WGOVkEL3ONbatqju9nSXTUucpp_pw@mail.gmail.com

Attachment

Re: Generic Index Skip Scan

From
David Rowley
Date:
On Thu, 16 Jul 2020 at 07:52, Floris Van Nee <florisvannee@optiver.com> wrote:
>
> Besides the great efforts that Dmitry et al. are putting into the skip scan for DISTINCT queries [1], I’m also still
keenon extending the use of it further. I’d like to address the limited cases in which skipping can occur here. A few
monthsago I shared an initial rough patch that provided a generic skip implementation, but lacked the proper planning
work[2]. I’d like to share a second patch set that provides an implementation of the planner as well. Perhaps this can
leadto some proper discussions how we’d like to shape this patch further. 
>
> Please see [2] for an introduction and some rough performance comparisons. This patch improves upon those, because it
implementsproper cost estimation logic. It will now only choose the skip scan if it’s deemed to be cheaper than using a
regularindex scan. Other than that, all the features are still there. The skip scan can be used in many more types of
queriesthan in the original DISTINCT patch as provided in [1], making it more performant and also more predictable for
users.
>
> I’m keen on receiving feedback on this idea and on the patch.

I don't think anyone ever thought the feature would be limited to just
making DISTINCT go faster. There's certain to be more usages in the
future.

However, for me it would be premature to look at this now.

David

RE: Generic Index Skip Scan

From
Floris Van Nee
Date:

Attached v02, rebased on latest master. It uses the new nbtree lock/unlock functions by Peter and I’ve verified with Valgrind there’s no cases where it’s trying to access pages without holding the lock. Just for debugging, I ran the Valgrind session on a modified version of the patch that always favors a skip scan over a regular index scan, in order to greatly increase the Valgrind coverage of the new parts.

 

-Floris

 

 

Attachment