F.37. pg_hint_plan

F.37.1. Имя

pg_hint_plan — модуль, позволяющий управлять планом выполнения с указаниями, записываемыми в комментариях особого вида.

F.37.2. Синтаксис

В Postgres Pro Enterprise используется оптимизатор запросов, учитывающий стоимость их выполнения, и при этом он использует статистику данных, а не жёсткие правила. Планировщик (оптимизатор) оценивает стоимость всех возможных планов выполнения оператора SQL, и в итоге выбирает для выполнения план с наименьшей стоимостью. Планировщик делает всё возможное, чтобы выбрать лучший план выполнения, но он не идеален, так как он не учитывает некоторые свойства данных, например корреляции между столбцами.

Модуль pg_hint_plan позволяет корректировать планы выполнения, применяя так называемые «указания», записываемые в виде простых описаний в SQL-комментариях особого вида.

F.37.3. Описание

F.37.3.1. Простое использование

Модуль pg_hint_plan считывает фразы-указания в комментариях особого вида, записанных в теле целевого оператора SQL. Эта особая запись начинается с последовательности символов /*+ и заканчивается последовательностью */. Фразы указаний состоят из имени указания и последующих параметров, которые заключаются в скобки и разделяются пробелами. Такие указания могут размещаться в нескольких строках для улучшения читаемости.

В следующем примере в качестве метода соединения выбирается соединение по хешу, а pgbench_accounts сканируется последовательным способом.

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

postgres=#

F.37.4. Таблица указаний

В предыдущем разделе говорилось, что указания задаются в комментариях особого вида. Однако это неудобно, когда нет возможности редактировать запросы. В таких случаях указания можно внести в специальную таблицу hint_plan.hints. Эта таблица содержит следующие столбцы:

СтолбецОписание
idУникальный номер строки с указанием. Этот столбец заполняется автоматически генератором последовательности.
norm_query_stringШаблон для выбора запросов, к которым будет относиться указание. Константы, фигурирующие в целевом запросе, должны заменяться знаками ?, как в примере ниже. Пробельные символы в шаблоне являются значимыми.
application_nameЗначение переменной application_name (имя приложения), выбирающее сеансы, в которых будет действовать указание. В приведённом примере указание будет действовать на сеансы, установленные приложением psql. С пустой строкой будут выбираться сеансы с любым значением application_name.
hintФраза указания. Это поле должно содержать указания без обрамляющей разметки комментариев.

Следующий пример показывает, как манипулировать указаниями в таблице.

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres-#     VALUES (
postgres(#         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
postgres(#         'psql',
postgres(#         'SeqScan(t1)'
postgres(#     );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 1
postgres=#

Таблица указаний принадлежит пользователю, создавшему расширение, и для неё назначаются права доступа, установленные по умолчанию в момент CREATE EXTENSION. Указания, заданные в таблице, имеют больший приоритет, чем указания в комментариях.

F.37.4.1. Типы указаний

Фразы указаний подразделяются на шесть типов по видам объектов, на которые они могут воздействовать, и видам воздействия: методы сканирования, методы соединения, порядок соединения, корректировка количества строк, параллельные запросы и параметры GUC. Списки фраз указаний для каждого типа приведены в Подразделе F.37.10.

F.37.4.1.1. Указания для методов сканирования

Указания для методов сканирования принудительно устанавливают метод сканирования для заданной таблицы. В качестве имени целевой таблицы обработчик pg_hint_plan может распознать и её псевдоним, если он определён. К этому виду относятся указания SeqScan, IndexScan и т. д.

Такие указания применимы к обычным таблицам, таблицам с наследованием, нежурналируемым таблицам, временным таблицам и системным каталогам. Они не применяются к внешним (сторонним) таблицам, табличным функциям, результатам VALUES, CTE, представлениям и вложенным запросам.

postgres=# /*+
postgres*#     SeqScan(t1)
postgres*#     IndexScan(t2 t2_pkey)
postgres*#  */
postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
F.37.4.1.2. Указания для методов соединения

Указания для методов соединения принудительно выбирают определённый метод для соединения заданных таблиц.

Эти указания могут работать с обычными таблицами, таблицами с наследованием, нежурналируемыми и временными таблицами, внешними (сторонними) таблицами, системными каталогами, табличными функциями, результатами команд VALUES и CTE. Однако на представления и подзапросы они не воздействуют.

Указания для методов соединения рекомендуется использовать вместе с указанием для порядка соединения Leading, так как оно гарантирует применение порядка, указанного в запросе.

В запросе с указанием ниже будет применена структура соединения NestLoop(MergeJoin(c b) a):

postgres=# /*+ Leading(((c b) a)) MergeJoin(c b) NestLoop(a b c) */ EXPLAIN(COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c WHERE a.f1 = b.f1 AND b.f1 = c.f1;

                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Nested Loop
         ->  Merge Join
               Merge Cond: (c.f1 = b.f1)
               ->  Index Only Scan using t1_idx1 on t1 c
               ->  Materialize
                     ->  Index Only Scan using t1_idx1 on t1 b
         ->  Memoize
               Cache Key: b.f1
               Cache Mode: logical
               ->  Index Only Scan using t1_idx1 on t1 a
                     Index Cond: (f1 = b.f1)
F.37.4.1.3. Указание для порядка соединения

Указание Leading устанавливает порядок соединения двух и более таблиц. Выбрать порядок можно двумя способами. Первый вариант — установить определённый порядок соединения, но не ограничивать направление на каждом уровне:

postgres=# /*+
postgres*#     NestLoop(t1 t2)
postgres*#     MergeJoin(t1 t2 t3)
postgres*#     Leading(t1 t2 t3)
postgres*#  */
postgres-# SELECT * FROM table1 t1
postgres-#     JOIN table table2 t2 ON (t1.key = t2.key)
postgres-#     JOIN table table3 t3 ON (t2.key = t3.key);

Второй вариант — ограничить также и направление соединения. При выборе показанного выше порядка соединения, планировщик может выбрать не то направление (внешняя таблица — внутренняя таблица), которое вам бы хотелось. Чтобы в этом случае задать направление явно, используйте следующий формат:

postgres=# / * + Leading ((t1 (t2 t3))) * / SELECT ...

В этом формате скобки, окружающие пару элементов, определяют вложенность, при этом внутри одних скобок первый элемент будет в соединении внешней таблицей, а второй — внутренней.

Обратите внимание на то, что указание Leading (как и методы соединений) не может функционировать с модулем GEQO, если число указанных в запросе таблиц превышает geqo_threshold.

Ниже представлены дополнительные примеры использования указания для порядка соединения:

postgres=# /*+ Leading(((c b) a)) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c WHERE a.f1 = b.f1 AND b.f1 = c.f1;
                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (b.f1 = a.f1)
         ->  Nested Loop
               ->  Index Only Scan using t1_idx1 on t1 c
               ->  Memoize
                     Cache Key: c.f1
                     Cache Mode: logical
                     ->  Index Only Scan using t1_idx1 on t1 b
                           Index Cond: (f1 = c.f1)
         ->  Hash
               ->  Seq Scan on t1 a

И ещё один пример запроса:

postgres=# /*+ Leading(((d c) (b a))) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM t1 a, t1 b, t1 c, t1 d WHERE a.f1 = b.f1 AND b.f1 = c.f1 AND c.f1 = d.f1;

                          QUERY PLAN
---------------------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (c.f1 = a.f1)
         ->  Nested Loop
               ->  Index Only Scan using t1_idx1 on t1 d
               ->  Memoize
                     Cache Key: d.f1
                     Cache Mode: logical
                     ->  Index Only Scan using t1_idx1 on t1 c
                           Index Cond: (f1 = d.f1)
         ->  Hash
               ->  Hash Join
                     Hash Cond: (b.f1 = a.f1)
                     ->  Seq Scan on t1 b
                     ->  Hash
                           ->  Seq Scan on t1 a

За подробностями обратитесь к Подразделу F.37.10.

F.37.4.1.4. Указание для корректировки числа строк

Указание Rows корректирует неверную оценку количества строк при соединениях, возможно вызванную ограничениями планировщика.

postgres=# /*+ Rows(a b #10) */ SELECT... ; Устанавливает число строк результата соединения, равным 10
postgres=# /*+ Rows(a b +10) */ SELECT... ; Увеличивает число строк на 10
postgres=# /*+ Rows(a b -10) */ SELECT... ; Вычитает 10 из числа строк
postgres=# /*+ Rows(a b *10) */ SELECT... ; Увеличивает число строк в 10 раз
F.37.4.1.5. Указания для параллельных планов

Указание Parallel устанавливает конфигурацию параллельного выполнения при сканировании. Третий параметр определяет режим изменений конфигурации. В режиме «soft» pg_hint_plan меняет только max_parallel_workers_per_gather и в остальном оставляет свободу выбора планировщику. В режиме «hard» меняются и другие параметры планировщика, чтобы принудительно установить количество параллельных исполнителей. Это указание может воздействовать на обычные таблицы, родительские таблицы в дереве наследования, нежурналируемые таблицы и системные каталоги. На внешние таблицы, табличные функции, предложения VALUES, CTE, представления и вложенные запросы оно не действует. Обращаться в данном указании к внутренней таблице в представлении можно по имени или псевдониму этой таблицы. Следующий пример показывает, как в одном запросе можно выбрать разные конфигурации для разных таблиц.

postgres=# explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
       SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join  (cost=2.86..11406.38 rows=101 width=4)
   Hash Cond: (c1.a = c2.a)
   ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
         Workers Planned: 3
         ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
   ->  Hash  (cost=1.59..1.59 rows=101 width=4)
         ->  Gather  (cost=0.00..1.59 rows=101 width=4)
               Workers Planned: 5
               ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

postgres=# EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
   ->  Gather  (cost=693.00..693.01 rows=5 width=8)
         Workers Planned: 5
         ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
               ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)
F.37.4.1.6. Указания для хранимых процедур

Указания можно использовать c хранимыми процедурами. Обратите внимание на то, что указания могут наследоваться.

postgres=# CREATE OR REPLACE FUNCTION test_1() RETURNS bool AS $$
BEGIN
EXECUTE 'SELECT count(*) FROM t1 WHERE f1 < 2' ;
RETURN true;
END; $$ language plpgsql;

postgres=# CREATE OR REPLACE FUNCTION test_2() RETURNS void AS $$
BEGIN
EXECUTE 'SELECT /*+ SET(enable_bitmapscan off)*/ test_1()' ;
END;
$$ language plpgsql;

postgres=# SELECT test_2();

    Query Text: SELECT count(*) FROM t1 WHERE f1 < 2
    Aggregate  (cost=18.00..18.01 rows=1 width=8) (actual time=0.511..0.512 rows=1 loops=1)
      ->  Seq Scan on t1  (cost=0.00..17.50 rows=200 width=0) (actual time=0.105..0.457 rows=200 loops=1)
            Filter: (f1 < 2)
            Rows Removed by Filter: 800
F.37.4.1.7. Указания для подготовленных операторов

Расширение pg_hint_plan позволяет использовать указания с подготовленными операторами. Указания следует записывать в операторе PREPARE, в операторе EXECUTE они игнорируются.

Ниже представлены примеры запросов. Первый пример с указанием IndexOnlyScan(t1):

postgres=# /*+ IndexOnlyScan(t1) */ PREPARE stmt AS SELECT count(*) FROM t1 WHERE f1 < 2;

EXPLAIN EXECUTE stmt;
EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE stmt;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)

И второй — с указанием BitmapScan(t1):

postgres=# /*+ BitmapScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE stmt;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)
F.37.4.1.8. Временное переопределение параметров GUC

Указание Set меняет параметры GUC только на время планирования. Желаемое влияние на планирование могут оказывать параметры GUC, показанные в Подразделе 19.7.2, если только какое-либо другое указание не конфликтует с заданными параметрами метода планирования. Если для одного параметра GUC задано несколько указаний, в силу вступает последнее. Это указание позволяет поменять и параметры GUC для pg_hint_plan, но результат может не соответствовать вашим ожиданиям.

postgres=# /*+ Set(random_page_cost 2.0) */
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';
...

F.37.4.2. Параметры GUC для настройки pg_hint_plan

На поведение pg_hint_plan влияют следующие описанные ниже параметры GUC.

Таблица F.25. Параметры GUC

Имя параметраОписаниеЗначение по умолчанию
pg_hint_plan.enable_hintЗначение True включает pg_hint_plan.on (вкл.)
pg_hint_plan.enable_hint_tableЗначение True включает использование указаний из таблицы.on (вкл.)
pg_hint_plan.parse_messagesЗадаёт уровень, с которым будут попадать в журнал ошибки разбора указаний. Допустимые значения: error (ошибка), warning (предупреждение), notice (уведомление), info (информация), log (протоколирование), debug (отладка).INFO
pg_hint_plan.debug_printУправляет выводом и детализацией отладочной информации. Допустимые значения: off, on, detailed и verbose.off (выкл.)
pg_hint_plan.message_levelЗадаёт уровень, с которым будут попадать в журнал отладочные сообщения. Допустимые значения: error, warning, notice, info, log, debug.LOG
pg_hint_plan.hints_anywhereЕсли имеет значение on, pg_hint_plan читает указания, игнорируя синтаксис SQL. Это позволяет размещать указания в любом месте запроса, но имейте в виду, что в этом случае указания могут считываться некорректно.off (выкл.)

F.37.5. Установка

Расширение pg_hint_plan предоставляется вместе с Postgres Pro Enterprise в виде отдельного пакета. Подробные инструкции по установке приведены в Главе 17. После установки Postgres Pro Enterprise активируйте расширение pg_hint_plan:

postgres=# LOAD 'pg_hint_plan';
LOAD
postgres=#

Вы также можете загрузить его глобально, добавив pg_hint_plan в параметр shared_preload_libraries в файле postgresql.conf.

shared_preload_libraries = 'pg_hint_plan'

Для автоматической загрузки определённых сеансов используйте ALTER USER SET/ALTER DATABASE SET.

По сути, pg_hint_plan не требует выполнения CREATE EXTENSION, но если вы планируете использовать таблицу указаний, создайте расширение и включите параметр enable_hint_table:

CREATE EXTENSION pg_hint_plan;
SET pg_hint_plan.enable_hint_table TO on;

F.37.6. Подробное описание указаний

F.37.6.1. Синтаксис и расположение

Обработчик pg_hint_plan считывает указания только из первого блочного комментария и немедленно прекращает разбор, обнаруживая недопустимый символ. Допустимыми символами являются буквы, цифры, пробелы, подчёркивания, запятые и скобки. В следующем примере HashJoin(a b) и SeqScan(a) воспринимаются в качестве указаний, а IndexScan(a) и MergeJoin(a b) — нет.

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# /*+ IndexScan(a) */
postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

postgres=#

Однако если для параметра hints_anywhere задано значение on, pg_hint_plan считывает указания из любого места в запросе, поэтому следующие варианты использования указания будут равнозначными:

EXPLAIN /*+ SeqScan(t1)*/
SELECT * FROM table1 t1 WHERE a < 10;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)


EXPLAIN
SELECT * FROM table1 t1 WHERE a < 10 AND '/*+SeqScan(t1)*/' <> '';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)


EXPLAIN
SELECT * FROM table1 t1 WHERE a < 10 /*+SeqScan(t1)*/;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on table1 t1  (cost=0.00..17.50 rows=9 width=8)
   Filter: (a < 10)
(2 rows)

F.37.6.2. Использование с PL/pgSQL

pg_hint_plan может работать с запросами в скриптах PL/pgSQL с некоторыми ограничениями.

  • Указания воздействуют только на следующие типы запросов:

    • Запросы, возвращающие одну строку (SELECT, INSERT, UPDATE и DELETE)

    • Запросы, возвращающие множество строк (RETURN QUERY)

    • Динамические операторы SQL (EXECUTE)

    • Запрос, открывающий курсор (OPEN)

    • Цикл по результату запроса (FOR)

  • Комментарий с указанием должен добавляться после первого слова запроса, так как комментарии, идущие перед ним, в составе запроса не передаются.

postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
postgres$# DECLARE
postgres$#     id  integer;
postgres$#     cnt integer;
postgres$# BEGIN
postgres$#     SELECT /*+ NoIndexScan(a) */ aid
postgres$#         INTO id FROM pgbench_accounts a WHERE aid = $1;
postgres$#     SELECT /*+ SeqScan(a) */ count(*)
postgres$#         INTO cnt FROM pgbench_accounts a;
postgres$#     RETURN id + cnt;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;

F.37.6.3. Регистр букв в именах объектов

В отличие от PostgreSQL, pg_hint_plan, разбирая имена объектов в указаниях, сравнивает их с внутренними именами объектов с учётом регистра. Таким образом, имени TBL в указании будет соответствовать только объект "TBL" в базе данных, но не будут соответствовать объекты с именами без кавычек TBL, tbl или Tbl.

F.37.6.4. Экранирование спецсимволов в именах объектов

Если имя объекта включает в себя скобки, кавычки или пробелы, оно должно заключаться в кавычки. При этом действуют те же правила экранирования, что и в PostgreSQL.

F.37.6.5. Различение нескольких вхождений таблицы

pg_hint_plan может выбирать целевые объекты по псевдонимам, если они заданы. Это позволяет обратиться к одному определённому вхождению таблицы, используемой в запросе неоднократно.

postgres=# /*+ HashJoin(t1 t1) */
postgres-# EXPLAIN SELECT * FROM s1.t1
postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO:  hint syntax error at or near "HashJoin(t1 t1)"
DETAIL:  Relation name "t1" is ambiguous.
...
postgres=# /*+ HashJoin(pt st) */
postgres-# EXPLAIN SELECT * FROM s1.t1 st
postgres-# JOIN public.t1 pt ON (st.id=pt.id);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=64.00..1112.00 rows=28800 width=8)
   Hash Cond: (st.id = pt.id)
   ->  Seq Scan on t1 st  (cost=0.00..34.00 rows=2400 width=4)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)

F.37.6.6. Нижележащие таблицы представлений или правил

Указания не применяются непосредственно к представлениям, но могут воздействовать на выполняемые запросы, если имена объектов в указаниях совпадают с именами объектов в развёрнутом запросе представления. К таблицам внутри представления можно обращаться снаружи по назначенным им псевдонимам.

postgres=# CREATE VIEW v1 AS SELECT * FROM t2;
postgres=# EXPLAIN /*+ HashJoin(t1 v1) */
          SELECT * FROM t1 JOIN v1 ON (c1.a = v1.a);
                            QUERY PLAN
------------------------------------------------------------------
 Hash Join  (cost=3.27..18181.67 rows=101 width=8)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1  (cost=0.00..14427.01 rows=1000101 width=4)
   ->  Hash  (cost=2.01..2.01 rows=101 width=4)
         ->  Seq Scan on t2  (cost=0.00..2.01 rows=101 width=4)

И ещё один пример:

postgres=# CREATE VIEW v1 AS SELECT count(*) FROM t1 WHERE f1 < 2;
/*+ IndexOnlyScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM v1;
                QUERY PLAN
-------------------------------------------
 Aggregate
   ->  Index Only Scan using t1_idx1 on t1
         Index Cond: (f1 < 2)

Не выбирайте таблицы с одинаковыми именами в разных представлениях, так как такие таблицы могут неправильно обрабатываться. Чтобы избежать такой ситуации, используйте уникальные псевдонимы, например сочетание наименования представления и наименования таблицы.

postgres=# /*+ SeqScan(t1) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM v2;

             QUERY PLAN
-------------------------------------
 Nested Loop Semi Join
   Join Filter: ((count(*)) = t1.f1)
   ->  Aggregate
         ->  Seq Scan on t1 t1_1
               Filter: (f1 < 2)
   ->  Seq Scan on t1

F.37.6.7. Таблицы с наследованием

Указания могут быть нацелены только на родителя в дереве наследования, однако при этом они воздействуют на всё дерево. Указания, нацеленные на потомков в этом дереве, не будут действовать.

F.37.6.8. Указания с составными операторами

Для одного описания составного оператора может задаваться только один комментарий, и записанные в нём указания будут распространяться на все отдельные операторы внутри этого составного. Заметьте: то, что выглядит в интерактивном интерфейсе psql как составной оператор, внутри представляется как последовательность одиночных операторов, так что указания в комментарии перед операторами действуют только на первый из них.

F.37.6.9. Выражения VALUES

Все выражения VALUES в предложении FROM имеют внутреннее обозначение *VALUES*, так что к ним можно обращаться, только если в запросе фигурирует только одно выражение VALUES. Два или более выражений VALUES в запросе могут представляться различными в выводе EXPLAIN, но в действительности это лишь визуальное улучшение, по сути различить их нельзя.

postgres=# /*+ MergeJoin(*VALUES*_1 *VALUES*) */
      EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
      JOIN (VALUES (1, 5), (2, 8), (3, 4)) w (a, c) ON v.a = w.a;
INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES*_1 *VALUES*) "
DETAIL:  Relation name "*VALUES*" is ambiguous.
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=0.05..0.12 rows=2 width=16)
   Hash Cond: ("*VALUES*_1".column1 = "*VALUES*".column1)
   ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.04 rows=3 width=8)
   ->  Hash  (cost=0.03..0.03 rows=2 width=8)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)

F.37.7. Подзапросы

В указаниях можно обращаться к подзапросам в следующем контексте по имени ANY_subquery.

IN (SELECT ... {LIMIT | OFFSET ...} ...)
= ANY (SELECT ... {LIMIT | OFFSET ...} ...)
= SOME (SELECT ... {LIMIT | OFFSET ...} ...)

С этими конструкциями планировщик внутри даёт имя подзапросу, планируя соединения таблиц с этим подзапросом, так что в указаниях соединений можно обращаться к нему по этому неявному имени следующим образом:

postgres=# /*+HashJoin(a1 ANY_subquery)*/
postgres=# EXPLAIN SELECT *
postgres=#    FROM pgbench_accounts a1
postgres=#   WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
                                          QUERY PLAN

 ---------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=0.49..2903.00 rows=1 width=97)
    Hash Cond: (a1.aid = a2.bid)
    ->  Seq Scan on pgbench_accounts a1  (cost=0.00..2640.00 rows=100000 width=97)
    ->  Hash  (cost=0.36..0.36 rows=10 width=4)
          ->  Limit  (cost=0.00..0.26 rows=10 width=4)
                ->  Seq Scan on pgbench_accounts a2  (cost=0.00..2640.00 rows=100000 width=4)

F.37.7.1. Использование указания IndexOnlyScan

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

F.37.7.2. Поведение указания NoIndexScan

Указание NoIndexScan подразумевает NoIndexOnlyScan.

F.37.7.3. Указание Parallel и UNION

Предложение UNION может выполняться в параллельном режиме, только когда все нижележащие подзапросы безопасны для распараллеливания. С другой стороны, если параллельное выполнение принудительно выбирается для любого из подзапросов, всё предложение UNION будет обрабатываться параллельно, если это возможно. При этом в случае выбора в указании Parallel нулевого количества исполнителей выполнение в параллельном режиме будет запрещено.

F.37.7.4. Установка параметров pg_hint_plan в указаниях Set

Параметры pg_hint_plan меняют поведение самого обработчика указаний, поэтому некоторые параметры работают не так, как можно ожидать.

  • Указания, изменяющие enable_hint и enable_hint_table, игнорируются несмотря на то, что в отладочном выводе они отмечаются как «использованные указания».

  • Изменение debug_print и message_level начинает действовать с середины процедуры обработки целевого запроса.

F.37.8. Ошибки

Обработчик pg_hint_plan останавливает разбор в случае ошибки и в большинстве случаев применяет указания, уже разобранные к этому моменту. Ниже перечислены типичные ошибки.

F.37.8.1. Синтаксические ошибки

Любые ошибки в записи или неправильные имена указаний считаются ошибками синтаксиса. Эти ошибки выводятся в журнал сообщений сервера с уровнем, заданным в параметре pg_hint_plan.message_level, если параметр pg_hint_plan.debug_print имеет значение, отличное от off.

F.37.8.2. Неправильные обращения к объектам

Указания с неправильными обращениями к объектам просто игнорируются. Ошибки такого типа отмечаются в журнале как «неиспользованные указания» при тех же условиях, что и синтаксические ошибки.

F.37.8.3. Избыточные или конфликтующие указания

Когда указания избыточны или одно указание конфликтует с другим, действовать будет последнее указание. Ошибки такого типа отмечаются как «дублирующиеся указания» в журнале сообщений сервера при тех же условиях, что и синтаксические ошибки.

F.37.8.4. Вложенные комментарии

Комментарий с указаниями не может содержать в себе другой блочный комментарий. Если pg_hint_plan сталкивается с такой ситуацией, то, в отличие от случаев других ошибок, он прекращает разбор и отбрасывает все уже разобранные указания.

F.37.9. Функциональные ограничения

F.37.9.1. Влияние некоторых параметров GUC на планирование

Планировщик не будет рассматривать порядок соединения для предложений FROM, в которых больше чем from_collapse_limit элементов. В таких случаях pg_hint_plan не может повлиять на порядок соединения ожидаемым образом.

F.37.9.2. Указания, принудительно выбирающие невыполнимые планы

В случаях, когда принудительно выбранный план выполнить нельзя, планировщик выбирает любые исполнимые планы.

  • Использовать для FULL OUTER JOIN вложенный цикл

  • Использовать индексы, столбцы которых не задействуются в условиях

  • Выполнять сканирования TID для запросов без условий с ctid

F.37.9.3. Запросы в ECPG

ECPG убирает комментарии из запросов, записанных в виде встраиваемого SQL, так что указания с такими запросами передать нельзя. Единственным исключением является команда EXECUTE, которая передаёт данную строку неизменённой. В таком случае могут быть полезны таблицы с указаниями.

F.37.9.4. Совместимость с pg_stat_statements

pg_stat_statements генерирует идентификатор запроса, игнорируя комментарии, поэтому одинаковые запросы с разными указаниями будут консолидироваться как один и тот же запрос.

F.37.10. Поддерживаемые указания

Ниже перечислены все поддерживаемые указания.

Таблица F.26. Список указаний

ГруппаФорматОписание
Метод сканированияSeqScan(таблица)Принудительно выбирает последовательное сканирование таблицы.
 TidScan(таблица)Принудительно выбирает сканирование таблицы по TID.
 IndexScan(таблица [ индекс...])Принудительно выбирает сканирование таблицы по индексу (при добавлении индексов сканирование ограничивается ими).
 IndexOnlyScan(таблица [ индекс...])Принудительно выбирает сканирование таблицы только по индексу (при добавлении индексов сканирование ограничивается ими). Если сканирование только по индексу невозможно, может использоваться обычное сканирование по индексу.
 BitmapScan(таблица[ индекс...])Принудительно выбирает сканирование таблицы по битовой карте (при добавлении индексов сканирование ограничивается ими).
 IndexScanRegexp(таблица [ регулярное выражение POSIX...])Принудительно выбирает сканирование таблицы по индексу. Сканирование ограничивается индексами с именами, соответствующими указанному регулярному выражению POSIX.
 IndexOnlyScanRegexp(таблица [ регулярное выражение POSIX...])Принудительно выбирает сканирование таблицы только по индексу. Сканирование ограничивается индексами с именами, соответствующими указанному регулярному выражению POSIX.
 BitmapScanRegexp(таблица[ регулярное выражение POSIX...])Принудительно выбирает сканирование таблицы по битовой карте. Сканирование ограничивается индексами с именами, соответствующими указанному регулярному выражению POSIX.
 NoSeqScan(таблица)Отключает выбор последовательного сканирование таблицы.
 NoTidScan(таблица)Отключает выбор сканирования таблицы по TID.
 NoIndexScan(таблица)Отключает выбор сканирования по индексу и сканирования только по индексу для заданной таблицы.
 NoIndexOnlyScan(таблица)Принудительно отключает выбор сканирования только по индексу для заданной таблицы.
 NoBitmapScan(таблица)Отключает выбор сканирования по битовой карте для таблицы.
Метод соединенияNestLoop(таблица таблица[ таблица...])Принудительно выбирает вложенный цикл для соединений с заданными таблицами.
 HashJoin(таблица таблица[ таблица...])Принудительно выбирает соединение по хешу для соединений с заданными таблицами.
 MergeJoin(таблица таблица[ таблица...])Принудительно выбирает соединение слиянием для соединений с заданными таблицами.
 NoNestLoop(таблица таблица[ таблица...])Отключает выбор вложенного цикла для соединений с заданными таблицами.
 NoHashJoin(таблица таблица[ таблица...])Отключает выбор соединения по хешу для соединений с заданными таблицами.
 NoMergeJoin(таблица таблица[ таблица...])Отключает выбор соединения слиянием для соединений с заданными таблицами.
Порядок соединенияLeading(таблица таблица[ таблица...])Принудительно выбирает заданный порядок соединения.
 Leading(<соединяемая пара>)Принудительно выбирает заданный порядок и направления соединения. Соединяемая пара в данном случае — это пара таблица и/или других соединяемых пар, заключённая в скобки, что позволяет образовывать вложенные структуры.
Корректировка числа строкRows(таблица таблица[ таблица...] корректировка)Корректирует число строк, получаемых в результате соединения указанных таблиц. Для корректировки можно задать абсолютное значение (#<n>) или использовать сложение (+<n>), вычитание (-<n>) и умножение (*<n>). Здесь <n> — это строка, которую сможет воспринять функция strtod().
Настройка параллельных запросовParallel(таблица<число исполнителей> [soft|hard])Принудительно включает или отключает параллельную обработку заданной таблицы. Параметр <число исполнителей> в этом указании определяет желаемое количество параллельных исполнителей (значение 0 отключает параллельное выполнение). Если третий параметр равен soft (по умолчанию), меняется только значение параметра сервера max_parallel_workers_per_gather, а в остальном планировщику остаётся свобода выбора. Со значением hard заданное количество исполнителей устанавливается принудительно.
GUCSet(параметр-GUC значение)Устанавливает значение для параметра GUC на время планирования запроса.

F.37.11. См. также

EXPLAIN, SET, Глава 19, Раздел 15.3