Postgres docs state the following about Index-Only Scans and Covering-Indexes:
if you commonly run queries like
SELECT y FROM tab WHERE x = 'key';
the traditional approach to speeding up such queries would be to create an index on x only. However, an index defined as
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
could handle these queries as index-only scans, because y can be obtained from the index without visiting the heap.
Because column y is not part of the index's search key, it does not have to be of a data type that the index can handle; it's merely stored in the index and is not interpreted by the index machinery. Also, if the index is a unique index, that is
CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);
the uniqueness condition applies to just column x, not to the combination of x and y. (An INCLUDE clause can also be written in UNIQUE and PRIMARY KEY constraints, providing alternative syntax for setting up an index like this.)
Question 1: If the data type of
y can be added in index and there is no uniqueness requirement then is there any advantage of using
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y) over
CREATE INDEX tab_x_y ON tab(x, y) for queries like
SELECT y FROM tab WHERE x = 'key';?
It's wise to be conservative about adding non-key payload columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches.
Question 2: Can someone explain with an example what
wide columns mean?
Question 3: Can someone explain the below statement in context of
INCLUDE supports index only scans then
y will also have to be stored in index. Then how does the below statement not hold for
In any case, non-key columns duplicate data from the index's table and bloat the size of the index