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

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