Re: index-only scan is missing the INCLUDE feature

From: Cédric Villemain <cedric(at)2ndquadrant(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, sthomas(at)optionshouse(dot)com, Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
Subject: Re: index-only scan is missing the INCLUDE feature
Date: 2012-06-25 15:22:00
Message-ID: 201206251722.05175.cedric@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Le jeudi 21 juin 2012 04:45:41, Craig Ringer a écrit :
> On 06/20/2012 11:32 PM, Shaun Thomas wrote:
> > On 06/20/2012 09:11 AM, Craig Ringer wrote:
> >> For those of us who don't know MS-SQL, can you give a quick
> >> explanation of what the INCLUDE keyword in an index definition is
> >> expected to do, or some documentation references?
> >
> > He's talking about what MS SQL Server commonly calls a "covering
> > index." In these cases, you can specify columns to be included in the
> > index, but not actually part of the calculated hash. This prevents a
> > trip to the table data, so selects can be serviced entirely by an
> > index scan.
>
> Oh, OK, so it's a covering index with added fields that don't form part
> of the searchable index structure to make the index a little less
> expensive than a fully covering index on all the columns of interest.
> Fair enough. Thanks for the explanation.
>
> Eyal, you'll get a better response to questions about other DBMSs if you
> explain what you need/want to do with the desired feature and what that
> feature does in the other DBMS.
>
> > PostgreSQL is about half way there by allowing index-only scans,
> > though I've no idea if they intend on adding further functionality
> > like this.
>
> There's certainly lots of interest in adding more, but not that many
> people with the expertise to be able to do it - and fewer still who're
> paid to work on Pg so they have time to focus on it. Covering indexes
> with Pg's MVCC model seem to be particularly challenging, too.

There was a recent thread on -hackers about index with UNIQUEness of some
columns only. The objective was near the one you describe here.
So you're not alone looking after that.

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Frits Jalvingh 2012-06-25 15:42:27 Postgres delete performance problem
Previous Message David Kerr 2012-06-22 20:11:26 Re: "global/pgstat.stat" corrupt