Re: Covering Indexes

On 17 July 2012 16:54, David E. Wheeler <david(at)justatheory(dot)com> wrote:
> On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote:
>
>> 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.
>
> Yeah, but that index is unnecessarily big if one will never use c or d in the search. The nice thing about covering indexes as described for SQLite 4 and implemented in MSSQL is that you can specify additional columns that just come along for the ride, but are not part of the indexed data:
>
> CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
>
> Yes, you can do that by also indexing c and d as of 9.2, but it might be nice to be able to include them in the index as additional row data without actually indexing them.

Can you explain what you mean by "without actually indexing them"?
ISTM that it is a non-feature if the index is already non-unique, and
the difference is simply down to the amount of snake oil applied to
the descriptive text on the release notes.

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