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 по дате отправления: