Хинты планера в 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)!
    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 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 разных фич планировщика не помогали.

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