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

David Garamond <lists@zara.6.isreserved.com> writes:
> 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.

            regards, tom lane