[GENERAL] Index selection (and partial index) for BYTEA field

[David Garamond]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly