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