Re: Covering Indexes

On 17 July 2012 16:21, David E. Wheeler <david(at)justatheory(dot)com> wrote:
> On Jul 17, 2012, at 5:18 PM, Simon Riggs wrote:
>
>>> Now that we have index-only scans in 9.2, I'm wondering if it would make sense to add covering index support, too, where additional, unindexed columns are stored alongside indexed columns.
>>
>> Just to be clear, the ability to have covered indexes is already in
>> 9.2, I updated the release notes to explain that a few months back.
>
> You mean this?
>
>> Allow queries to retrieve data only from indexes, avoiding heap access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)
>>
>> This is often called "index-only scans" or "covering indexes". This is possible for heap pages with exclusively all-visible tuples, as reported by the visibility map. The visibility map was made crash-safe as a necessary part of implementing this feature.
>
> That’s not how SQLite is using the term “covering index.” What they mean is the ability to have additional, unindexed columns in an index, so that they can *also* be returned in the event of an index-only scan.

CREATE INDEX ON foo (a, b, c, d);

allows

SELECT c, d FROM foo WHERE a = ? AND b = ?

to use an index only scan.

The phrase "unindexed" seems misleading since the data is clearly in
the index from the description on the URL you gave. And since the
index is non-unique, I don't see any gap between Postgres and
SQLliite4.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services