Столкнулся с такой проблемой: есть большая таблица (порядка 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 и нужно ждать пока разработчки исправят это?
--
С уважением, Дмитрий