On 17 July 2012 16:21, David E. Wheeler <da...@justatheory.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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers