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;
tomka
  • 967
  • 1
  • 10
  • 16

1 Answers1

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).

tomka
  • 967
  • 1
  • 10
  • 16