1

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

queries like this can use it for index-only scans:

SELECT y FROM tab WHERE x = 'key';

Now I am wondering if such a covering index could also allow index-only scans when the covering columns appear as conditions. For instance, assume a covering index:

CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);

Would this allow for index-only scans for the following queries?

SELECT z FROM tab WHERE x = 'key' AND y = 1;

SELECT x, y, z FROM (VALUES ('key1'),('key2'),('key3')) sub(id)
JOIN tab ON tab.x = sub.id WHERE y = 1;

1 Answer 1

2

I should have tried a bit more first. Now that I run a few examples, I seems like yes, the above queries can all use an index-only scan. As soon as a column outside of the covering index is referred to, a regular access method is used (e.g. an index scan id a fitting index is in place).

It would be nice if the Postgres documentation would make this more clear (or I overlooked the information).

3
  • 3
    Listing every SQL construct where an index scan can be used would be tiresome. Potentially, an index only scan can be used wherever an index scan can be used (they are really the same, minus the table lookup). Note that columns in the INCLUDE clause cannot be compared during the index scan, but have to be filtered afterwards, which makes the index scan part less efficient than if the column were an indexed key. Commented Jan 17, 2020 at 7:36
  • Thank you, that's understandable. I suppose I just misinterpreted "create an index in which some columns are just “payload” and are not part of the search key" in the documentation and took it for "payload columns can't be used for any comparison, only for the projection (SELECT).
    – tomka
    Commented Jan 17, 2020 at 16:10
  • 1
    That means that they cannot be used for a comparison during the scan itself, but of course they can feature in a filter condition afterwards. Commented Jan 17, 2020 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.