Index selection (and partial index) for BYTEA field

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

Responses

Browse pgsql-general by date

  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