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(at)justatheory(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +