From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Index selection (and partial index) for BYTEA field |
Date: | 2004-03-19 14:26:08 |
Message-ID: | 405B0300.8090606@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Table of 2mil records, two columns: id (BYTEA/GUID, PK) and i (INT,
UNIQUE INDEX). i values range from 1 to 2000000.
I'm creating several partial index for i as follows:
create unique index i_partition_i_1to100k on partition(i)
where i>=0 and i<=100000;
create unique index i_partition_i_100k1to200k on partition(i)
where i>=100001 and i<=200000;
When I do this:
explain select * from partition where i=1;
or
explain select * from partition where i=150000;
explain tells me it is using the partial index. But when I create
partial index on the id column (BYTEA):
create unique index i_partition_id_000 on partition(id)
where id like '\\000%';
create unique index i_partition_id_001 on partition(id)
where id like '\\001%';
then:
explain select * from partition where id like '\\000\\001%';
or
explain select * from partition where id like '\\000234567890123456';
says the query is using the PK index, not the partial index. Why is this so?
--
dave
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2004-03-19 14:43:11 | Re: sequential scan when using bigint value |
Previous Message | Robert Bernier | 2004-03-19 14:21:54 | another postgres article |