Re: Covering Indexes

Le vendredi 6 juillet 2012 15:41:01, Bruce Momjian a écrit :
> On Fri, Jun 29, 2012 at 08:10:03AM +0200, Csaba Nagy wrote:
> > Hi all,
> > 
> > > On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler
> > >  wrote: I don't see the virtue of this in this
> > > case.  Since the index is not unique, why not just put the index on
> > > (a,b,c,d) and be done with it? Is there some advantage to be had in
> > > inventing a way to store c and d in the index without having them
> > > usable for indexing?
> > 
> > Why not restrict it to UNIQUE indexes ?
> > 
> > For not unique indexes it has no advantages (it could be in fact indexed
> > on all columns anyway as an implementation detail).
> > 
> > For the unique case the problem of many identical entries mentioned by
> > Tom is not relevant, so the additional data will only bloat the index
> > but not otherwise affect the index performance.
> > 
> > Would this get close enough to index-covered table ? _That_ would be
> > interesting - I have a really big table (table/index size: 370G/320G,
> > ~8*10^9 rows) which is basically using double space because it's primary
> > key is covering all columns of the table.
> 
> I was wondering if there was some way to specify an expression index
> that did a unique index check on some columns but included more columns
> not part of the unique index.

I haven't tryed it, but I suppose that Exclusion Constraint should allow that.

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation