Случилось то, о чем многие мечтали и чего уже устали ждать, а другие боялись. Японские разработчики из NTT реализовали хинты планера PostgreSQL. Причем, им удалось это сделать, не меняя ядро, в виде отдельного модуля pg_hint_plan, поддерживающего версии PostgreSQL 9.1 и 9.2. Модуль реализует хинты, позволяющие устанавливать методы сканирования и соединения таблиц, установку значений GUC. За деталями установки и использования добро пожаловать под кат.
С сайта можно скачать архивы исходников отдельно под версии 9.1 и 9.2, которые, правда, не отличаются абсолютно ничем и одинаково собираются под обе версии. Ну да ладно. Сборка и установка модуля проблем не вызывает: make && make install. Для сборки потребуется dev-пакет PostgreSQL от вашего любимого дистрибутива. Для того, чтобы PostgreSQL подхватил модуль, никакого SQL выполнять не нужно, достаточно добавить pg_hint_plan в переменную shared_preload_libraries в файле postgresql.conf (вместо этого можно подгружать модуль в каждую сессию, где это необходимо, с помощью команды LOAD). После перезапуска сервера станут доступны три новые GUC переменные: pg_hint_plan.enable_hint, pg_hint_plan.debug_print, pg_hint_plan.parse_messages. Первая из них отвечает за доступность хинтов (по-умолчанию включены), оставшиеся две — за логирование.
Хинты указываются в комментариях к запросу, оформленных с помощью /* и */. Чтобы комментарий интерпретировался как хинт, у него в начале должен стоять знак +, например /*+ SeqScan(t1) */. Хинты бывают следующих видов.
Хинты, отвечающие за метод сканирования таблицы
- SeqScan (имя таблицы)
- TidScan (имя таблицы)
- IndexScan (имя таблицы [имя индекса])
- IndexOnlyScan (имя таблицы [имя индекса])
- BitmapScan (имя таблицы [имя индекса])
- NoSeqScan (имя таблицы)
- NoTidScan (имя таблицы)
- NoIndexScan (имя таблицы)
- NoIndexOnlyScan (имя таблицы)
- NoBitmapScan (имя таблицы)
В качестве имени таблицы можно указывать как имя самой таблицы, так и её алиас в запросе.
Хинты, отвечающие за метод соединение таблиц
- NestLoop (список имен таблиц)
- HashJoin (список имен таблиц)
- MergeJoin (список имен таблиц)
- NoNestLoop (список имен таблиц)
- NoHashJoin (список имен таблиц)
- NoMergeJoin (список имен таблиц)
Список имен таблиц указывается через пробел. Он чувствителен к порядку, то есть соединение осуществляется именно в том порядке, в котором таблицы указаны.
Также отдельно выделены два хинта:
- Leading (список имен таблиц) — устанавливает порядок соединения таблиц без указания конкретного способа соединения
- Set(GUC значение) — устанавливает значение GUC переменной на время выполнения запроса. Вобщем-то никакой новой функциональности не несет, GUC и так можно было установить, просто хинт позволяет сделать это более лаконично (и быстро?).
Настало время попробовать всё это в деле. Создадим тестовые таблицы, индексы, соберем статистику.
CREATE TABLE test1 AS (SELECT id, (random()*1000)::int AS id_2, random() AS value1, random() AS value2 FROM generate_series(1,1000000) id);
CREATE TABLE test2 AS (SELECT id, random() AS value FROM generate_series(1,1000) id);
CREATE INDEX test1_id_idx ON test1 (id);
CREATE INDEX test1_id_2_idx ON test1 (id_2);
CREATE INDEX test1_value1_idx ON test1 (value1);
CREATE INDEX test1_value2_idx ON test1 (value2);
CREATE INDEX test2_id_idx ON test2 (id);
CREATE INDEX test2_value_idx ON test2 (value);
VACUUM ANALYZE;
Предположим у нас есть запрос, фильтрующий данные по значениям двух полей.
SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;
Планер решает объединить результаты сканирования индексов по каждому из полей с помощью Bitmap Scan.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test1 (cost=319.82..514.76 rows=52 width=24) (actual time=9.575..9.736 rows=59 loops=1)
Recheck Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision) AND (value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
-> BitmapAnd (cost=319.82..319.82 rows=52 width=0) (actual time=9.529..9.529 rows=0 loops=1)
-> Bitmap Index Scan on test1_value1_idx (cost=0.00..113.54 rows=5318 width=0) (actual time=2.839..2.839 rows=5072 loops=1)
Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
-> Bitmap Index Scan on test1_value2_idx (cost=0.00..206.00 rows=9764 width=0) (actual time=5.385..5.385 rows=10070 loops=1)
Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
Total runtime: 9.805 ms
Однако мы можем заставить его использовать обычный Index Scan.
/*+ IndexScan(test1) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using test1_value1_idx on test1 (cost=0.00..15198.71 rows=52 width=24) (actual time=0.124..10.704 rows=59 loops=1)
Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
Filter: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
Total runtime: 10.776 ms
И даже заставить его использовать другой индекс.
/*+ IndexScan(test1 test1_value2_idx) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using test1_value2_idx on test1 (cost=0.00..22463.60 rows=52 width=24) (actual time=0.787..15.757 rows=59 loops=1)
Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision))
Filter: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision))
Total runtime: 15.816 ms
(4 rows)
Пример по-сложнее. Соединение двух таблиц с фильтрацией по полю одной таблицы, сортировкой по полю другой и LIMIT.
SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;
Планер выбирает план c Index Scan по test1_value1_idx и Nested Loop.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4.33..2149.77 rows=100 width=36) (actual time=0.274..34.784 rows=100 loops=1)
-> Nested Loop (cost=4.33..171467.82 rows=7992 width=36) (actual time=0.271..34.753 rows=100 loops=1)
Join Filter: (t1.id_2 = t2.id)
-> Index Scan using test1_value1_idx on test1 t1 (cost=0.00..51457.05 rows=1000000 width=24) (actual time=0.022..10.338 rows=11873 loops=1)
-> Materialize (cost=4.33..10.80 rows=8 width=12) (actual time=0.000..0.001 rows=8 loops=11873)
-> Bitmap Heap Scan on test2 t2 (cost=4.33..10.76 rows=8 width=12) (actual time=0.035..0.046 rows=8 loops=1)
Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
-> Bitmap Index Scan on test2_value_idx (cost=0.00..4.33 rows=8 width=0) (actual time=0.026..0.026 rows=8 loops=1)
Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
Total runtime: 34.870 ms
Предположим, мы хотим использовать другой тип соединения таблиц: HashJoin.
/*+ HashJoin(t1 t2) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;
Планер подчиниться, добавив внутрь Bitmap Index Scan по test2, а снаружи — сортировку с Limit.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20516.23..20516.48 rows=100 width=36) (actual time=156.219..156.230 rows=100 loops=1)
-> Sort (cost=20516.23..20536.21 rows=7992 width=36) (actual time=156.217..156.225 rows=100 loops=1)
Sort Key: t1.value1
Sort Method: top-N heapsort Memory: 32kB
-> Hash Join (cost=10.86..20210.78 rows=7992 width=36) (actual time=0.248..154.286 rows=7889 loops=1)
Hash Cond: (t1.id_2 = t2.id)
-> Seq Scan on test1 t1 (cost=0.00..16370.00 rows=1000000 width=24) (actual time=0.013..63.210 rows=1000000 loops=1)
-> Hash (cost=10.76..10.76 rows=8 width=12) (actual time=0.066..0.066 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Bitmap Heap Scan on test2 t2 (cost=4.33..10.76 rows=8 width=12) (actual time=0.044..0.057 rows=8 loops=1)
Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
-> Bitmap Index Scan on test2_value_idx (cost=0.00..4.33 rows=8 width=0) (actual time=0.034..0.034 rows=8 loops=1)
Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
Total runtime: 156.335 ms
Если, к примеру, задать тип соединения MergeJoin и IndexScan по индексу test2_value_idx, то планер, опять таки добавит необходимые сортировки и Limit.
/*+ MergeJoin(t1 t2) IndexScan (t2 test2_value_idx) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=54410.09..54410.34 rows=100 width=36) (actual time=446.031..446.041 rows=100 loops=1)
-> Sort (cost=54410.09..54430.07 rows=7992 width=36) (actual time=446.029..446.032 rows=100 loops=1)
Sort Key: t1.value1
Sort Method: top-N heapsort Memory: 32kB
-> Merge Join (cost=71.79..54104.65 rows=7992 width=36) (actual time=12.501..444.501 rows=7889 loops=1)
Merge Cond: (t1.id_2 = t2.id)
-> Index Scan using test1_id_2_idx on test1 t1 (cost=0.00..51460.24 rows=1000000 width=24) (actual time=0.033..377.392 rows=900401 loops=1)
-> Sort (cost=24.52..24.54 rows=8 width=12) (actual time=0.074..0.545 rows=6927 loops=1)
Sort Key: t2.id
Sort Method: quicksort Memory: 25kB
-> Index Scan using test2_value_idx on test2 t2 (cost=0.00..24.40 rows=8 width=12) (actual time=0.026..0.047 rows=8 loops=1)
Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision))
Total runtime: 446.182 ms
Можно заметить, что во всех приведенных примерах, от использования хинтов ситуация только ухудшалась. Этим я хотел намекнуть на то, что стоит два раза подумать, прежде чем использовать хинты в реальных проектах. Даже если у Вас получился план, который в данном конкретном случае выполняется быстрее, задайте себе следующие вопросы:
- Настраивали ли Вы параметры планера *_cost, effective_cache_size, geqo* и т.д. в соответствии с имеющимися ресурсами сервера?
- На каких данных у Вас получился план, который выполняется быстрее? На продакшене такое же распределение данных? Вы готовы переписывать хинты, когда распределение данных изменится?
- План выполнился быстрее, когда всё, что надо, оказалось в кэше? А на момент выполнения данного запроса на продакшене всё тоже будет в кэше?
И всё-таки хинты очень полезны как-минимум в двух ситуациях:
- Хочется глубже понять работу планера/executer'а, получить ответы на вопросы «Что было бы если?».
- Иногда планер всё таки сильно ошибается. Например, когда есть сильная корреляция между полями таблицы, которую он не умеет учитывать. Из-за этого получается неверная оценка селективности условия, и может быть плохой план.
Автор: smagen
