From: Cédric Villemain <cedric@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: sthomas@optionshouse.com, Eyal Wilde <eyal@impactsoft.co.il>
Subject: Re: index-only scan is missing the INCLUDE feature
Date: Mon, 25 Jun 2012 17:22:00 +0200
Message-ID: <201206251722.05175.cedric@2ndquadrant.com> (raw)
In-Reply-To: <4FE28AD5.5090702@ringerc.id.au>
References: <CAMiEbcjxN7cRYGtRMZuxEoztEOMB4us+pSdanwSkRY=ocJiQ8Q@mail.gmail.com>
<4FE1ED03.3090709@optionshouse.com>
<4FE28AD5.5090702@ringerc.id.au>
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
Attachments:
signature.asc (application/pgp-signature, 489B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cedric@2ndquadrant.com, ringerc@ringerc.id.au, eyal@impactsoft.co.il
Subject: Re: index-only scan is missing the INCLUDE feature
In-Reply-To: <201206251722.05175.cedric@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox