Re: Index selection (and partial index) for BYTEA field

Tom Lane wrote:
>>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%';
>
>> explain select * from partition where id like '\\000\\001%';
>>says the query is using the PK index, not the partial index. Why is this so?
>
> 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).

--
dave