Index selection (and partial index) for BYTEA field - Mailing list 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