0,0
рейтинг
18 февраля 2013 в 18:56

Разработка → Хинты планера в PostgreSQL

Известно, что SQL — декларативный язык, который указывает, «что» мы хотим выбрать из базы, а «как» это сделать — СУБД решает сама. Задачу выбора для SQL-запроса конкретного способа его выполнения(плана) решает планировщик запросов, который есть практически в любой СУБД. Но иногда он выбирает не самый лучший план. Многие коммерческие СУБД предоставляют на этот случай «хинты», которые позволяют в ручном режиме подсказывать базе, как лучше выполнить запрос. В Open Source СУБД PostgreSQL такого механизма не было.

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

P.S. За наводку на модуль спасибо Олегу Бартунову (aka zen)!
Александр Коротков @smagen
карма
32,5
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (28)

  • +3
    Черт! Это действительно круто! Я очень рад.
  • 0
    Да — это круто!
    Да — я тоже рад!
    Но!!!
    Было бы намного круче и радостнее, если бы хинты сделали совместимыми с оракловыми.
    • +2
      Нужно делать скидку на то, что хинты реализованы в виде модуля, что накладывает определенные ограничения (лично я вообще удивился тому, что это оказалось возможно). А в ядро их не очень-то хотят добавлять.
  • НЛО прилетело и опубликовало эту надпись здесь
    • +4
      SQL — декларативный язык, он указывает, «что» мы хотим выбрать из базы, а «как» это сделать — база решает сама. Эту задачу в базе решает планировщик. Но иногда он выбирает не самый лучший способ выполнить запрос. «Хинты» позволяют в ручном режиме подсказывать базе, как лучше выполнить запрос.
      • НЛО прилетело и опубликовало эту надпись здесь
        • 0
          Пост написан для тех, кто, как говориться, уже «в теме». Советую начать, например, вот с этой презентации.
          • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    Интересно, спасибо.
    Этим я хотел намекнуть на то, что стоит два раза подумать, прежде чем использовать хинты в реальных проектах.

    Оно и понятно. Такой тонкий тюнинг вряд ли когда пригодится, практически это значит, что всё остальное оптимизировано почти до совершенства. А как показывает практика, узкие места всплывают везде, если копнуть.
    • 0
      Не, ну еще есть откровенные загоны планировщика в конкретных местах. Так что очень даже ура!
      • 0
        Может быть, не спорю, хотя сам с прямо конкретными загонами не сталкивался.
        • 0
          Вот здесь можете ознакомиться. Хотя это не загон, а скорее особенность.
          • 0
            Спасибо, поэкспериментирую.
    • 0
      Мы в Оракле его часто используем. Не исключено, что и в Постргресе где-то пригодится.
  • 0
    Идея хорошая, но да, навевает сомнения в использовании в реальных проектах )

    С другой стороны, можно одни и те же результаты выборки получать несколько по разному формируя запрос и тем самым вызывая разные Scan'ы
    • 0
      эээ врядли. Дело в том что планировщик сам занимается переписыванием запросов, так что зачастую (если конечно запросы эквиваленты) ручное переписывание много не поможет. Другое дело что вы занимаясь таким переписыванием ненароком улучшите/оптимизируете сам запрос.
      • 0
        Ну так речь как раз об оптимизации )
        Имеется ввиду, например, использование тех же индексов получая вместо фуллскана индексскан
  • 0
    Спасибо за статью. Подскажите, с какими параметрами производили установку? У меня
    make USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config
    фейлится.
    • 0
      У меня на Ubuntu 12.04 установилось без явного указания параметров, просто «make». С какой ошибкой у Вас фейлится?
      • 0
        «Makefile», line 18: Could not find
        make: fatal errors encountered — cannot continue

        ОС — FreeBSD 8.2
        • 0
          Разобрались, нужен был GNU Make вместо BSD Make.
          • 0
            + параметры для сборки указать:
            gmake USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config gmake USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config install
            Спасибо вам еще раз.
  • 0
    Лучше бы докомошники партиционирование нормальное впилили или MPP. Или хинты по чилдам: «не чекай 100500 чилдов на constraint exclusion, а сразу топай к таким то» или SQL/MED допиливали быстрее, причем с оптимизацией выполнения запросов для PG-PG связки. Вообщем хочу PostgresXC взрослый и в основном бранче.

    А «хинты» и до этого были: «SET enable_seqscan=OFF;» работает в сессии. Можно набор функций по сохранению/включению/выключению допилить и вуаля. Причем профит в том что не указываешь, как именно надо делать, а показываешь чего делать не стоит.
    Хотя если честно ни разу не сталкивался еще с тем, что ручное указание плана при правильной статистике дало профит. Всегда это было или криво сконфигуреный автоваакуум/аналайз, или проблемы с индексами, или кривая структура. Планировщик в PG достаточно эффективный.
  • 0
    Судя по исходному коду, эта штука просто использует существовавшие и до этого параметры сессии: enable_seqscan, enable_indexscan, enable_bitmapscan, enable_tidscan и т.д., просто оборачивает их в подобие oracle hints
    • 0
      В хинтах, отвечающих за метод сканирования таблицы, всё именно так и происходит. Значение параметров изменяется на момент получения информации о соответствующей таблице. Соединение таблиц реализуется немного сложнее.
  • 0
    Хотелось бы всё-таки увидеть пример положительного использования хинтов. Но, думаю, если планировщик можно обмануть (заставить выполнить запрос не оптимально), то нужно писать багрепорт.
    • 0
      Положительный пример постараюсь привести. Обмануть планировщик вполне возможно, т.к.:
      • Не учитываются всевозможные корреляции, и их учесть — очень трудная задача, пока не понятно как её решать.
      • Для ряда типов и операций адекватных оценок селективности нет.
    • 0
      Прикольная тулза, пример реальный простой. запрос с кучей join как inner так и left, в конце сортировка и лимитирование, сортировка по полю из таблицы указанной во from, а не в joun'ах. Используя Leading в корне меняем результат:
      1. сбор данных, сортировка, лимитирование
      2. сбор данных, лимитирование

      причем без Leading игры с =off разных фич планировщика не помогали.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.