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

Joe Conway <mail(at)joeconway(dot)com> writes:
> David Garamond wrote:
>> So I guess there's not a way that the partial index can be used instead

> 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:
> [ example ]

Probably a more practical way is to generate redundant WHERE conditions.
For instance:

create index i_partition_id_000 on partition(id)
where id like '\\000%';
... etc etc ...

then when you really want to search for \000\001\002% you do:

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

The second WHERE clause is redundant with the first, but it matches
the partial index condition so that the index can be used.

Essentially what we are doing here is putting the knowledge the planner
lacks (that a longer prefix LIKE match implies a shorter one) into the
application's query-writing code. It's a bit grotty since if you decide
to change the partial-index scheme you also have to change the app...

regards, tom lane