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: 4FE28AD5.5090702@ringerc.id.au (view raw or flat)
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

Responses

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-2014 The PostgreSQL Global Development Group