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.
pgsql-performance by date
|Next:||From: Eyal Wilde||Date: 2012-06-20 06:01:13|
|Subject: Re: scale up (postgresql vs mssql)|
|Previous:||From: Tim Jacobs||Date: 2012-06-19 21:34:56|
|Subject: Why is a hash join being used?|