Re: Covering Indexes

On 17 July 2012 17:41, David Johnston <polobo(at)yahoo(dot)com> wrote:

> 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)".

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.

> 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.

I think it would be easy enough to add noop operators for sorts if you
wanted to do that.

> 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%.

The main thing is that we definitely already do have covering indexes
and we will be announcing we have that soon. The fact we have chosen
to implement that without adding new syntax strikes me as a selling
point as well, so all client tools still work.

So the feature we are talking about here needs to be called something
else, otherwise we will be confusing people. "Unsorted trailing index
columns"...

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