Partial indexes

elein <elein@varlena.com> writes:

I think I want to:
create unique index pk on table tbl (keyp1, keyp2);
create unique index range on table tbl (keyp1, keyp2, keyp3) where keyp3 is not null;

Are these indexes redundant given uniqueness requirement and the type of queries
that will be run against the table?

If all (keyp1, keyp2) pairs are distinct then I see no particular use in
the second index; it doesn't check anything that's not implied by the
first index, and it doesn't offer any extra search selectivity either.

regards, tom lane

Tom Lane wrote:

elein <elein@varlena.com> writes:

I think I want to:
create unique index pk on table tbl (keyp1, keyp2);

don't you want
create unique inde pk on table tbl (keyp1, keyp2) where keyp3 is null
here?

create unique index range on table tbl (keyp1, keyp2, keyp3) where keyp3 is not null;

Are these indexes redundant given uniqueness requirement and the type of queries
that will be run against the table?

greetings, Florian Pflug

On Wed, Apr 19, 2006 at 03:51:27AM +0200, Florian G. Pflug wrote:

Tom Lane wrote:

elein <elein@varlena.com> writes:

I think I want to:
create unique index pk on table tbl (keyp1, keyp2);

don't you want
create unique inde pk on table tbl (keyp1, keyp2) where keyp3 is null
here?

create unique index range on table tbl (keyp1, keyp2, keyp3) where
keyp3 is not null;

Are these indexes redundant given uniqueness requirement and the type of
queries
that will be run against the table?

greetings, Florian Pflug

Yes, you are right. I ended up creating a unique constraint on the three
parts of the key and a unique index on keyp1, keyp2 where keyp3 is null.
I think this will cover all cases as simply as possible.

Thanks,

--elein
elein@varlena.com