Re: index-only scan is missing the INCLUDE feature - Mailing list pgsql-performance

From Cédric Villemain
Subject Re: index-only scan is missing the INCLUDE feature
Date
Msg-id 201206251722.05175.cedric@2ndquadrant.com
Whole thread Raw
In response to Re: index-only scan is missing the INCLUDE feature  (Craig Ringer <ringerc@ringerc.id.au>)
List 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

Attachment

pgsql-performance by date:

Previous
From: David Kerr
Date:
Subject: Re: "global/pgstat.stat" corrupt
Next
From: Frits Jalvingh
Date:
Subject: Postgres delete performance problem