Re: Covering Indexes

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.

Best,

David