SavePearlHarbor

Хинты планера в PostgreSQL

—

от автора

admin
Случилось то, о чем многие мечтали и чего уже устали ждать, а другие боялись. Японские разработчики из 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’а, получить ответы на вопросы «Что было бы если?».
  • Иногда планер всё таки сильно ошибается. Например, когда есть сильная корреляция между полями таблицы, которую он не умеет учитывать. Из-за этого получается неверная оценка селективности условия, и может быть плохой план.

ссылка на оригинал статьи http://habrahabr.ru/post/169751/

Комментарии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *