David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)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