> On Mon, Aug 5, 2019 at 12:05 PM Floris Van Nee <florisvan...@optiver.com> > wrote: > > The root->distinct_pathkeys is already filtered for redundant keys, so column > 'a' is not in there anymore. Still, it'd be much faster to use the skip scan > here, because a regular scan will scan all entries with a=1, even though > we're really only interested in the first one. In previous versions, this > would be fixed by changing the check in planner.c to use > root->uniq_distinct_pathkeys instead of root->distinct_pathkeys, but things > change a bit now that the patch is rebased on the unique-keys patch. Would it > be valid to change this check to root->query_uniquekeys != NIL to consider > skip scans also for this query?
[including a commentary from Jesper] On Mon, Aug 5, 2019 at 6:55 PM Jesper Pedersen <jesper.peder...@redhat.com> wrote: Yes, the check should be for that. However, the query in question doesn't have any query_pathkeys, and hence query_uniquekeys in standard_qp_callback(), so therefore it isn't supported. Your scenario is covered by one of the test cases in case the functionality is supported. But, I think that is outside the scope of the current patch. > However, currently, the patch just reads the closest and then doesn't > consider this page at all anymore, if the first tuple skipped to turns out to > be not visible. Consider the following sql to illustrate: For the records, the purpose of `_bt_read_closest` is not so much to reduce amount of data we read from a page, but more to correctly handle those situations we were discussing before with reading forward/backward in cursors, since for that in some cases we need to remember previous values for stepping to the next. I've limited number of items, fetched in this function just because I was misled by having a check for dead tuples in `_bt_skip`. Of course we can modify it to read a whole page and leave visibility check for the code after `index_getnext_tid` (although in case if we know that all tuples on this page are visilbe I guess it's not strictly necessary, but we still get improvement from skipping itself).
