Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group