From: Craig Ringer <ringerc@ringerc.id.au>
To: sthomas@optionshouse.com
Cc: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: index-only scan is missing the INCLUDE feature
Date: Thu, 21 Jun 2012 10:45:41 +0800
Message-ID: <4FE28AD5.5090702@ringerc.id.au> (raw)
In-Reply-To: <4FE1ED03.3090709@optionshouse.com>
References: <CAMiEbcjxN7cRYGtRMZuxEoztEOMB4us+pSdanwSkRY=ocJiQ8Q@mail.gmail.com>
<4FE1D9F9.1020805@ringerc.id.au>
<4FE1ED03.3090709@optionshouse.com>
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
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: ringerc@ringerc.id.au, sthomas@optionshouse.com, eyal@impactsoft.co.il
Subject: Re: index-only scan is missing the INCLUDE feature
In-Reply-To: <4FE28AD5.5090702@ringerc.id.au>
* 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