pg_hint_plan — управление планом выполнения с фразами-подсказками

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

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

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

---

---

Установка

Модуль pg_hint_plan для QHB поставляется в виде пакета qhb-1.5.2-pg-hint-plan.

Подключите репозиторий пакетов QHB и установите пакет расширения для выбранной платформы со страницы загрузки.

---

---

Применение

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

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

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

qhb=#

---

---

Таблица подсказок

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

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

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

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

qhb=# EXPLAIN (VERBOSE, COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
               QUERY PLAN
----------------------------------------
 Seq Scan on public.t1
   Output: id, id2
   Filter: (t1.id = 1)
 Query Identifier: -7164653396197960701
(4 rows)
qhb=# INSERT INTO hint_plan.hints(query_id, application_name, hints)
qhb-#     VALUES (-7164653396197960701, '', 'SeqScan(t1)');
INSERT 0 1
qhb=# UPDATE hint_plan.hints
qhb-#     SET hints = 'IndexScan(t1)'
qhb-#     WHERE id = 1;
UPDATE 1
qhb=# DELETE FROM hint_plan.hints WHERE id = 1;
DELETE 1
qhb=#

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

Идентификатор запроса можно получить с помощью модуля pg_stat_statements либо выполнив команду EXPLAIN (VERBOSE).

---

Типы подсказок

Фразы-подсказки подразделяются на несколько типов в зависимости от вида объекта и того, как они могут влиять на планировщик. Более подробную информацию см. в параграфе Список подсказок).

---

Подсказки для методов сканирования

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

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

qhb=# /*+
qhb*#     SeqScan(t1)
qhb*#     IndexScan(t2 t2_pkey)
qhb*#  */
qhb-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

---

Подсказки для методов соединения

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

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

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

Данная подсказка указывает на структуру соединения NestLoop(MergeJoin(c b) a):

qhb=# /*+ Leading(((c b) a)) MergeJoin(c b) NestLoop(a b c) */ EXPLAIN(COSTS OFF, TIMING OFF, SUMMARY OFF)
qhb-# 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)

qhb=#

---

Подсказка для порядка соединения

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

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

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

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

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

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

Дополнительный пример использования подсказки для порядка соединения:

qhb=# /*+ Leading(((c b) a)) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
qhb-# 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

qhb=#

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

qhb=# /*+ Leading(((d c) (b a))) */ EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF)
qhb-# 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

qhb=#

Подробную информацию см. в параграфе Список подсказок.

---

Подсказки для управления поведением соединения

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

qhb=# /*+ NoMemoize(a b) */
qhb-# 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)

qhb=#

---

Подсказка для корректировки количества строк

Подсказка Rows корректирует неверную оценку количества строк соединений, возникающую из-за ограничений планировщика. Например:

qhb=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10
qhb=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10
qhb=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.
qhb=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.

---

Подсказка для параллельных планов

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

qhb=# EXPLAIN /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
qhb-#        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)

qhb-# 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)

qhb=#

---

Подсказки для хранимых процедур

Подсказки можно использовать с хранимыми процедурами. Будьте осторожны, поскольку подсказки могут наследоваться.

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

---

Подсказки для подготовленных операторов

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

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

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

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

qhb=#

И с подсказкой BitmapScan(t1):

qhb=#  /*+ 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)

---

Установка параметров GUC при планировании

Подсказка Set изменяет параметры GUC прямо во время планирования. Параметр GUC, показанный в подразделе Константы стоимости для планировщика, может оказать ожидаемое влияние на планирование, если только какие-либо другие подсказки не конфликтуют с параметрами конфигурации метода планировщика. Из нескольких подсказок для одного параметра GUC в силу вступает последняя. С помощью этой подсказки можно также установить параметры GUC для pg_hint_plan, но она может работать не так, как вы ожидаете. Подробную информацию см. в параграфе Функциональные ограничения.

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

---

Параметры GUC для pg_hint_plan

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

Имя параметра	Описание	Значение по умолчанию
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.	info
pg_hint_plan.hints_anywhere	При значении on pg_hint_plan считывает подсказки, игнорируя синтаксис SQL. Это позволяет размещать подсказки в любом месте запроса, но учтите, что при этом подсказки могут считываться некорректно.	off (выкл.)

---

---

Подробное описание подсказок

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

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

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

qhb=#

Однако когда в параметре 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)

---

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

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

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

  • Запросы, возвращающие одну строку (SELECT, INSERT, UPDATE и DELETE)
  • Запросы, возвращающие несколько строк (RETURN QUERY)
  • Динамические операторы SQL (EXECUTE)
  • Открытие курсора (OPEN)
  • Цикл по результату запроса (FOR)

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

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

---

Обработка верхнего и нижнего регистра букв в именах объектов

В отличие от того, как обрабатываются имена объектов в QHB, pg_hint_plan сравнивает непосредственно имена объектов в подсказках с внутренними именами объектов базы данных с учетом регистра. Таким образом, имени TBL в подсказке соответствует только «TBL» в базе данных, и не соответствуют имена без кавычек вроде TBL, tbl или Tbl.

---

Экранирование специальных символов в именах объектов

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

---

Разграничение между несколькими вхождениями таблицы

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

qhb=# /*+ HashJoin(t1 t1) */
qhb-# EXPLAIN SELECT * FROM s1.t1
qhb-# 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.
...
qhb=# /*+ HashJoin(pt st) */
qhb-# EXPLAIN SELECT * FROM s1.t1 st
qhb-# 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)

---

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

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

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

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

qhb=# CREATE VIEW v1 AS SELECT count(*) FROM t1 WHERE f1 < 2;
qhb=# /*+ 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)

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

qhb=# /*+ 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

---

Таблицы в иерархии наследования

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

---

Подсказки для составных операторов

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

---

Выражения VALUES

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

qhb=# /*+ 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)

---

Подзапросы

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

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

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

qhb=# /*+HashJoin(a1 ANY_subquery)*/
qhb=# EXPLAIN SELECT *
qhb=#    FROM pgbench_accounts a1
qhb=#   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)

---

Использование подсказки IndexOnlyScan

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

---

Поведение NoIndexScan

Подсказка NoIndexScan подразумевает NoIndexOnlyScan.

---

Подсказки Parallel и UNION

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

---

Установка параметров pg_hint_plan с помощью подсказок Set

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

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

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

---

---

Ошибки

При любой ошибке pg_hint_plan останавливает синтаксический анализ подсказки и использует уже проанализированные подсказки. Ниже приведены типичные ошибки.

---

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

Все синтаксические ошибки или неправильные имена подсказок протоколируются как синтаксические ошибки. Этих ошибки протоколируются в журнал сервера с уровнем сообщения, заданным в параметре pg_hint_plan.message_level, если параметр pg_hint_plan.debug_print имеет значение on (включен) и выше.

---

Неправильные определения объекта

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

---

Излишние или противоречивые подсказки

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

---

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

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

---

---

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

Влияние GUC-параметров планировщика

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

---

Подсказки, пытающиеся принудительно выбрать невыполнимые планы

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

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

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

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

---

Идентификаторы запросов

При включенном параметре compute_query_id модуль pg_stat_statements генерирует идентификатор запроса, игнорируя комментарии. В результате для идентичных запросов с разными подсказками вычисляется один идентификатор запроса.

---

---

Список подсказок

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

Группа	Формат	Описание
Метод сканирования	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])	Принудительно выбирает или запрещает параллельное выполнение указанной таблицы. <количество рабочих процессов> — желаемое количество параллельных рабочих процессов, где ноль означает запрет параллельного выполнения. Если третий параметр равен soft (по умолчанию), он просто изменяет параметр max_parallel_workers_per_gather, а все остальные параметры оставляет на усмотрение планировщика. Со значением hard заданное количество рабочих процессов устанавливается принудительно.
GUC	Set(параметр-GUC значение)	Устанавливает значение для параметра GUC на время работы планировщика.

---

---

См. также

EXPLAIN, SET, глава Конфигурация сервера, раздел Параллельные планы, Документация по pg_hint_plan на Github