From: =?utf-8?q?C=C3=A9dric_Villemain?= <cedric@2ndquadrant.com>
Reply-To: cedric@2ndquadrant.com
Organization: 2ndQuadrant
To: pgsql-performance@postgresql.org
Subject: Re: index-only scan is missing the INCLUDE feature
Date: Mon, 25 Jun 2012 17:22:00 +0200
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-amd64; KDE/4.7.4; x86_64; ; )
Cc: Craig Ringer <ringerc@ringerc.id.au>, sthomas@optionshouse.com,
	Eyal Wilde <eyal@impactsoft.co.il>
References: 
 <CAMiEbcjxN7cRYGtRMZuxEoztEOMB4us+pSdanwSkRY=ocJiQ8Q@mail.gmail.com>
	<4FE1ED03.3090709@optionshouse.com>
	<4FE28AD5.5090702@ringerc.id.au>
In-Reply-To: <4FE28AD5.5090702@ringerc.id.au>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart5244094.qlHyqfAUSH";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <201206251722.05175.cedric@2ndquadrant.com>

--nextPart5244094.qlHyqfAUSH
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Le jeudi 21 juin 2012 04:45:41, Craig Ringer a =C3=A9crit :
> 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?
> >=20
> > 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.
>=20
> 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.
>=20
> 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.
>=20
> > 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.
>=20
> 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=20
columns only. The objective was near the one you describe here.
So you're not alone looking after that.

=2D-=20
C=C3=A9dric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - D=C3=A9veloppement, Expertise et Formation

--nextPart5244094.qlHyqfAUSH
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)

iQEcBAABAgAGBQJP6IIYAAoJENsgH0yzVBeb1vkH/3KxYjTU6stMcApiZPp7LS6g
MUQv+CB5xkIClTUqLMccX3eFQS/0jeeT3RkzeEvy9a+Oq4ArVBUSKjBmKjpN2z0+
joDv1HHFg0r+7za8yctKlxjfL6O49iv79OwpszruOtdgCz+ZT489xgbKBlywTrDL
UyitUAoHyn0RzaHeHMrJb5UuHwidz0XXColQGHhA7wXV/fmIlWkOyugOH+DtyvV2
F8fEAyp3Qwzn6ad8lI0lEujSjXhSWHmsXAhe3WodFBlO7qWjpb62Gr2ubyy80BF1
2ELzEARNFaSF69mLHoiQ6Rp7QUsybnp5HfaUGGKAnYlPiYka6qAnY35PYGdDe2E=
=Z9qn
-----END PGP SIGNATURE-----

--nextPart5244094.qlHyqfAUSH--