Re: Covering Indexes

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 <david@justatheory.com> 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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +