> -----Original Message----- > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > ow...@postgresql.org] On Behalf Of David E. Wheeler > Sent: Tuesday, July 17, 2012 11:55 AM > To: Simon Riggs > Cc: Pg Hackers > Subject: Re: [HACKERS] 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
Concretely, I would presume that the contents of a covering index could then look like the following (a,b,c,d): (2,1,2,A) (2,1,5,A) <-- the 5 is out of natural order but exists in the "covering" part (2,1,3,A) Whereas PostgreSQL would be forced to have the index ordered as such: (2,1,2,A) (2,1,3,A) (2,1,5,A) Either way the data in "c" and "d" are IN THE INDEX otherwise in neither case could the data values be returned while strictly querying the index. So the question that needs to be asked is what kind of performance increase can be had during DML (insert/update) statements and whether those gains are worth pursuing. Since these other engines appear to allow both cases you should be able to get at least a partial idea of the performance gains between "index (a,b,c,d)" and "index (a,b) covering (c,d)". Vik's concurrent point regarding "non-indexable" values makes some sense but the use case there seems specialized as I suspect that in the general case values that are non-indexable (if there truly are any) are generally those that would be too large to warrant sticking into an index in the first place. But, XML values do ring true in my mind (particularly frequently used fragments that are generally quite small). But again whether that is a reasonable use case for a covering index I do not know. It feels like trying to solve the remaining 10% when it took a long while to even muster up enough support and resources to solve the 90%. David J. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
