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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Index selection (and partial index) for BYTEA field
Date: 2004-03-20 16:06:57
Message-ID: 10455.1079798817@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2004-03-20 16:11:11 Re: How reliable are the stats collector stats?
Previous Message Robert Creager 2004-03-20 15:30:33 CREATE TABLE LIKE question