Index selection (and partial index) for BYTEA field

Поиск
Список
Период
Сортировка
От David Garamond
Тема Index selection (and partial index) for BYTEA field
Дата
Msg-id 405B0300.8090606@zara.6.isreserved.com
обсуждение исходный текст
Ответы Re: Index selection (and partial index) for BYTEA field
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Francisco Reyes
Дата:
Сообщение: Re: Huge number of raws
Следующее
От: Bill Moran
Дата:
Сообщение: Re: sequential scan when using bigint value