On Thu, Jul 26, 2012 at 12:25 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian <br...@momjian.us> wrote: >> On Tue, Jul 17, 2012 at 06:00:37PM +0100, Simon Riggs wrote: >>> > 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)". >>> >>> There is a use case, already discussed, whereby that is useful for >>> create unique index on foo (a,b) covering (c,d) >>> >>> but there really isn't any functional difference between >>> create index on foo (a,b) covering (c,d) >>> >>> and >>> create index on foo (a,b,c,d) >>> >>> There is a potential performance impact. But as Tom says, that might >>> even be negative if it is actually measurable. >> >> So, do we want a TODO item about adding columns to a unique index that >> will not be used for uniqueness checks? > > I think so. The case where you want a wide multiple column primary > key to be extended to cover that one extra commonly grabbed value is > not super common but entirely plausible. With the existing > infrastructure to get the advantages of index covering you'd have to > duplicate the entire index for the extra field which really sucks: > aside from the huge waste of time and space, you force the planner to > play the 'which index do i use?' game.
I think it is going to take several years before we really understand how index-only scans play out in the real world, and what factors limit their usefulness. This one has come up a few times because it's sort of an obvious thing to want to do and we don't have it, but I think that there's room for some skepticism about how well it will actually work, for reasons that have already been mentioned, and of course also because indexing more columns potentially means defeating HOT, which I suspect will defeat many otherwise-promising applications of index-only scans. That having been said, it would be unwise to speculate too much in advance of the data, and we're not going to get any data until someone tries implementing it, so +1 from me for putting something on the TODO list. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
