F.39. pg_hint_plan

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

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

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

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

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

F.39.3. Обзор

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

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

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

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM pgbench_branches b
    JOIN pgbench_accounts a ON b.bid = a.bid
    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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/*+ 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.39.4.1.3. Указание для порядка соединения

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

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

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

 /*+ Leading ((t1 (t2 t3))) * / SELECT ...

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

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

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

/*+ 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

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

/*+ 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.39.10.

F.39.4.1.4. Указания для управления поведением соединения

Указание «Memoize» позволяет соединению запоминать внутренний результат, а указание «NoMemoize» запрещает это. В приведённом ниже примере указание «NoMemoize» запрещает самому верхнему хеш-соединению запоминать результат таблицы a.

/*+ NoMemoize(a b) */
EXPLAIN SELECT * FROM a, b WHERE a.val = b.val;
                             QUERY PLAN
--------------------------------------------------------------------
 Hash Join  (cost=270.00..1412.50 rows=100000 width=16)
   Hash Cond: (b.val = a.val)
   ->  Seq Scan on b  (cost=0.00..15.00 rows=1000 width=8)
   ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
         ->  Seq Scan on a  (cost=0.00..145.00 rows=10000 width=8)
F.39.4.1.5. Указание для корректировки числа строк

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

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

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

 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)

 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.39.4.1.7. Указания для хранимых процедур

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

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

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

 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.39.4.1.8. Указания для подготовленных операторов

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

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

/*+ 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):

 /*+ 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.39.4.1.9. Настройка параметров GUC во время планирования

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

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

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

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

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

Имя параметраОписаниеЗначение по умолчанию
pg_hint_plan.enable_hintЗначение True включает pg_hint_plan.on
pg_hint_plan.enable_hint_tableЗначение True включает использование указаний из таблицы.off
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.39.5. Установка

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

LOAD 'pg_hint_plan';
LOAD

Вы также можете загрузить его глобально, добавив 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.39.6. Подробное описание указаний

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

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

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 /*+ IndexScan(a) */
 EXPLAIN SELECT /*+ MergeJoin(a b) */ *
    FROM pgbench_branches b
    JOIN pgbench_accounts a ON b.bid = a.bid
    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)

Однако если для параметра 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.39.6.2. Использование с PL/pgSQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 /*+ HashJoin(t1 t1) */
 EXPLAIN SELECT * FROM s1.t1
  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.
...
 /*+ HashJoin(pt st) */
 EXPLAIN SELECT * FROM s1.t1 st
  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.39.6.6. Нижележащие таблицы представлений или правил

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

 CREATE VIEW v1 AS SELECT * FROM t2;
 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)

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

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)

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

/*+ 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.39.6.7. Наследование

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

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

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

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

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

/*+ 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.39.7. Подзапросы

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

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

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

 /*+ HashJoin(a1 ANY_subquery)*/
 EXPLAIN SELECT *
  FROM pgbench_accounts a1
 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.39.7.1. Использование указания IndexOnlyScan

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

F.39.7.2. Указание NoIndexScan

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

F.39.7.3. Параллельные указания и UNION

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

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

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

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

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

F.39.8. Ошибки

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

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

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

F.39.8.2. Некорректные определения объектов

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

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

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

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

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

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

F.39.9.1. Влияние параметров GUC на планирование

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

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

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

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

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

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

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

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

F.39.9.4. pg_stat_statements

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

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

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

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

ГруппаФорматОписание
Метод сканирования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(<соединяемая пара>)Принудительно выбирает заданный порядок и направления соединения. Соединяемая пара в данном случае — это пара таблица и/или других соединяемых пар, заключённая в скобки, что позволяет образовывать вложенные структуры.
Управление поведением соединенияMemoize(таблица таблица[ таблица...])Позволяет самому верхнему соединению среди соединений, включающих указанные таблицы, запоминать внутренний результат. Обратите внимание, что запоминание при этом не будет задействовано принудительно.
 NoMemoize(таблица таблица[ таблица...])Запрещает самому верхнему соединению среди соединений, включающих указанные таблицы, запоминать внутренний результат.
Корректировка числа строкRows(таблица таблица[ таблица...] корректировка)Корректирует число строк, получаемых в результате соединения указанных таблиц. Для корректировки можно задать абсолютное значение (#<n>) или использовать сложение (+<n>), вычитание (-<n>) и умножение (*<n>). Здесь <n> — это строка, которую сможет воспринять функция strtod().
Настройка параллельных запросовParallel(таблица<число исполнителей> [soft|hard])Принудительно включает или отключает параллельную обработку заданной таблицы. Параметр <число исполнителей> в этом указании определяет желаемое количество параллельных исполнителей (значение 0 отключает параллельное выполнение). Если третий параметр равен soft (по умолчанию), меняется только значение параметра сервера max_parallel_workers_per_gather, а в остальном планировщику остаётся свобода выбора. Со значением hard заданное количество исполнителей устанавливается принудительно.
GUCSet(параметр-GUC значение)Устанавливает значение для параметра GUC на время планирования запроса.

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

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