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

Table of 2mil records, two columns: id (BYTEA/GUID, PK) and i (INT,
UNIQUE INDEX). i values range from 1 to 2000000.

I'm creating several partial index for i as follows:

  create unique index i_partition_i_1to100k on partition(i)
  where i>=0 and i<=100000;

  create unique index i_partition_i_100k1to200k on partition(i)
  where i>=100001 and i<=200000;

When I do this:

  explain select * from partition where i=1;

or

  explain select * from partition where i=150000;

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%';

  create unique index i_partition_id_001 on partition(id)
  where id like '\\001%';

then:

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

or

  explain select * from partition where id like '\\000234567890123456';

says the query is using the PK index, not the partial index. Why is this so?

--
dave

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

Tom Lane wrote:
>>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.

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).

--
dave

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

Joe Conway <mail@joeconway.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

Tom Lane wrote:
>>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.

That is one cool trick. And it worked!

--
dave