Re: [pgsql-ru-general] PostgreSQL не использует существующие индексы при построении индексов

Столкнулся с такой проблемой: есть большая таблица (порядка 5 гигабайт), по
ней построены нужные индексы, в том числе и primary key. Но при построение
дополнительных частичных индексов не используется уже существующие индексы,
а идёт полное сканирование таблицы.

Например есть таблица test_md5 со значениями md5 чисел от 1 до миллиона:
n | md5
----+----------------------------------
1 | c4ca4238a0b923820dcc509a6f75849b
2 | c81e728d9d4c2f636f067f89cc14862c
3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
4 | a87ff679a2f3e71d9181a67b7542122c
5 | e4da3b7fbbce2345d7772b0674a318d5
...

Построить её можно вот таким запросом:

> select *
> into test_md5
> from (select n, md5(n::varchar) from generate_series(1, 1000000) n) as
> test_data
> -- Запрос успешно завершён без результата возврата за 3895 мс.
>

Размер полученной таблицы 65 Мб.

По таблице создадим первичный ключ:

> alter table test_md5 add primary key (n);
> -- NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "test_md5_pkey" for table "test_md5"
> -- Запрос успешно завершён без результата возврата за 2411 мс.
>

Допустим нам известно, что искомые пароли лежат в диапазоне от 1000 до 2000.

Проверяем, что индекс по первичному ключу обеспечивает нам высокую скорость
выполнения:

> explain analyze select * from test_md5 where n between 1000 and 2000;
> -- Index Scan using test_md5_pkey on test_md5 (cost=0.00..45.94 rows=1079
> width=37) (actual time=0.023..0.536 rows=1001 loops=1)
> -- Index Cond: ((n >= 1000) AND (n <= 2000))
> -- Total runtime: *0.729 ms*
>

А теперь внимание: построим частичный индекс по этому диапазону для поиска:

> create index idx_test_md5_n_1000_2000 on test_md5 (md5) where n between
> 1000 and 2000;
> -- Запрос успешно завершён без результата возврата за *541 мс*.
>

Если смотреть по времени построение индекса, то явно видно, что при
построении индекса не используется уже существующий индекс по первичному
ключу, а идёт полное сканирование таблицы.
В данный момент проблему обхожу выборкой нужного диапазона во временную
таблицу, потому что полное сканирование огромной таблицы создаёт слишком
большую нагрузку на сервер.

Можно ли это решить какими-нибудь настройками конфигурации/окружения или же
это особенность PostgreSQL и нужно ждать пока разработчки исправят это?

--
С уважением, Дмитрий

Добрый день,

> Можно ли это решить какими-нибудь настройками конфигурации/окружения или же
> это особенность PostgreSQL и нужно ждать пока разработчки исправят это?

Используйте CREATE INDEX CONCURRENTLY ...

http://www.postgresql.org/docs/8.4/interactive/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802