Postgres Pro
Downloads
Home   >   mailing lists

Thread: Partial indexes

Partial indexes

From
elein
Date:
In 8.1.2.

Table contains columns:
    keyp1 not null, keyp2 not null, keyp3 nullable.

The queries will be separated into two kinds:
one on those rows where keyp3 is null and
the second on where keyp3 is not null.

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?  I don't think a non-unique index over
all three columns be adequate given the uniqueness constraints.

Opinions?

Thanks,

elein
elein@varlena.com

Re: Partial indexes

From
Tom Lane
Date:
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

Re: Partial indexes

From
"Florian G. Pflug"
Date:
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

Re: Partial indexes

From
elein
Date:
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