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

Re: index-only scan is missing the INCLUDE feature

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: sthomas(at)optionshouse(dot)com
Cc: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>, pgsql-performance(at)postgresql(dot)org
Subject: Re: index-only scan is missing the INCLUDE feature
Date: 2012-06-21 02:45:41
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
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.

Craig Ringer

In response to


pgsql-performance by date

Next:From: Albe LaurenzDate: 2012-06-21 08:34:38
Subject: Re: moving tables
Previous:From: Josh BerkusDate: 2012-06-21 00:28:38
Subject: Re: moving tables

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