From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Covering Indexes |
Date: | 2012-07-17 15:54:58 |
Message-ID: | 6F6BA247-3BAD-4906-B95B-751B604BA64E@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2012-07-17 16:08:59 | Re: Covering Indexes |
Previous Message | Simon Riggs | 2012-07-17 15:32:28 | Re: Covering Indexes |