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

index-only scan is missing the INCLUDE feature

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: pgsql-performance(at)postgresql(dot)org
Subject: index-only scan is missing the INCLUDE feature
Date: 2012-06-20 04:46:48
Message-ID: CAMiEbcjxN7cRYGtRMZuxEoztEOMB4us+pSdanwSkRY=ocJiQ8Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

as far as i looked around about the new feature: index-only scan, i guess
this feature will not include the option such as ms-sql INCLUDE.

well, i have a table with columns: a,b,c
i query the table like this: select a,c from table where a=x and b=y
as for now, i have unique-index on (a,b)

in the future (when upgrading to 9.2), i would like to have unique-index on
(a,b, INCLUDE c). but that wont be possible (right?).

so... in order to have index-only scan, i will have to create an index like
(a,b,c), but this has problems:
1. i lose the uniqueness enforcement of (a,b), unless, i will create 2
indexes: (a,b) and (a,b,c).
2. every update to column c would result in an unnecessary index-key-update
(or what ever you call that operation), which is not just updating a tuple,
but also an attempt to re-ordering it(!).
3. i just wonder: practically there is uniqueness of (a,b). now, if i
create index like (a,b,c) the optimizer dose not know about the uniqueness
of (a,b), therefore i afraid, it may not pick the best query-plan..

Thanks for any comment.

Responses

pgsql-performance by date

Next:From: Eyal WildeDate: 2012-06-20 06:01:13
Subject: Re: scale up (postgresql vs mssql)
Previous:From: Tim JacobsDate: 2012-06-19 21:34:56
Subject: Why is a hash join being used?

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