Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?
1
vote
1
answer
307
views
I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the [documentation](https://www.postgresql.org/docs/current/indexes-index-only-scans.html) 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;
Asked by tomka
(967 rep)
Jan 16, 2020, 11:23 PM
Last activity: Jan 17, 2020, 06:26 PM
Last activity: Jan 17, 2020, 06:26 PM