I would like to better understand when covering indexes can be useful to make possible single-index scans in Postgres 11+. As the documentation says, given the coverage rate
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
queries like this can use it for single index scans:
SELECT y FROM tab WHERE x = 'key';
Now I wonder if such coverage index could also allow single index scans when coverage columns appear as conditions. For example, assume a coverage index:
CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);
Would this allow single index scans for subsequent 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;