Re: Index selection (and partial index) for BYTEA field - Mailing list pgsql-general

David Garamond wrote:
> Tom Lane wrote:
>> The partial index matcher is not omniscient.  It knows a few things
>> about btree-compatible comparison operators, but nothing about LIKE.
>> Accordingly, this partial index will only get matched to queries that
>> contain *exactly* "id like '\\000%'" in their WHERE clauses.
>
> So I guess there's not a way that the partial index can be used instead
> of the PK index (I couldn't find in FAQ/archives whether one can force
> usage of one index over another).

There's no way to force use of either index, but you can probably get
your partial index picked if you define the index like:

   create unique index i_partition_id_000 on partition(id)
   where id like '\\000\\001%';

   (with query
    "select * from partition where id like '\\000\\001%';")

or else define your query like:

   select * from partition where id like '\\000%';

   (with index
    create unique index i_partition_id_000 on partition(id)
    where id like '\\000%';)


Joe