Модуль 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=# 

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

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

Для этих параметров в PostgreSQL 9.1 должен быть определён нестандартный класс переменных. За подробностями обратитесь к Разделу 19.17.

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

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

Указания по соединениям принимают в качестве параметров два или более объектов, образующих соединение. Если задаются три объекта, это указание будет применяться, когда для двух любых объектов, соединённых ранее, нужно выполнить соединение с оставшимся объектом. В следующем примере сначала соединяются таблицы table1 и table2 с применением вложенного цикла, а затем результат соединяется с table3 с применением соединения слиянием.

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);

Хотя возможен случай, когда таблицы table2 и table3 будут соединяться сначала, а table1 потом, так что указание NestLoop никак не подействует. В таких случаях можно задать порядок соединений с помощью указания «Leading». В данном примере указание Leading устанавливает порядок соединений table1, table2, table3, так что своё влияние окажут оба указания по методу соединения.

В показанном выше виде указание Leading принудительно устанавливает порядок соединений, но выбор направления (внутреннее/внешнее или ведущий/ведомый объект) остаётся за планировщиком. Если же вы хотите задать и направление, вам пригодится вторая форма этого указания.

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

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

В некоторых случаях планировщик может неправильно оценивать количество записей в соединениях. Это указание позволяет скорректировать эту оценку разными способами, в частности, задать абсолютное значение либо применить сложение/вычитание или умножение. В параметрах задаётся сначала список объектов, составляющих целевое соединение, а затем операция. В следующем примере показываются варианты указаний, корректирующие число записей в соединении a и b четырьмя способами.

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 раз

Указание «Set» задаёт значения для параметров GUC на время планирования целевого оператора. В следующем примере планирование запроса выполняется с параметром random_page_cost, равным 2.0.

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

Обработчик 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=# 

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

Дублирования имён, возникающего когда в запросе несколько раз фигурирует один объект или одноимённые объекты из разных пространств имён, можно избежать, назначив псевдонимы для каждого вхождения в целевом запросе, с тем, чтобы использовать эти псевдонимы в нужных местах указаний. В следующем примере первый SQL-оператор оказывается ошибочным, так как в целевом запросе дважды фигурирует одно имя, а второй работает, так как каждому вхождению таблицы t1 назначается отдельный псевдоним, который и задаётся в указании HashJoin.

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.
                            QUERY PLAN
------------------------------------------------------------------
 Merge Join  (cost=337.49..781.49 rows=28800 width=8)
   Merge Cond: (s1.t1.id = public.t1.id)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: s1.t1.id
         ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: public.t1.id
         ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
(8 rows)

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)
(5 rows)

postgres=#

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

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

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

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

В следующем примере в первом запросе одно имя «t1» назначено двум вхождениям таблицы table1, и поэтому указание SeqScan(t1) воздействует на оба сканирования. Во втором же запросе одному из вхождений дано другое имя («t3»), так что это указание воздействует только на первое.

Этот механизм также применяется к запросам, переписываемым правилами.

postgres=# CREATE VIEW view1 AS SELECT * FROM table1 t1;
CREATE TABLE
postgres=# /*+ SeqScan(t1) */
postgres=# EXPLAIN SELECT * FROM table1 t1 JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
                           QUERY PLAN
-----------------------------------------------------------------
 Nested Loop  (cost=0.00..358.01 rows=1 width=16)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
(5 rows)

postgres=# /*+ SeqScan(t3) */
postgres=# EXPLAIN SELECT * FROM table1 t3 JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..187.29 rows=1 width=16)
   ->  Seq Scan on table1 t3  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
   ->  Index Scan using foo_pkey on table1 t1  (cost=0.00..8.28 rows=1 width=8)
         Index Cond: (key = 1)
(5 rows)

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

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

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

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

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

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)
(6 rows)

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

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

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

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

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

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

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

По своей природе, pg_hint_plan не может повлиять на ситуации вне сферы влияния планировщика, например следующие:

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

В PostgreSQL 9.2 и новее для одинаковых запросов с разными комментариями функция pg_stat_statements формирует одинаковые отпечатки, но в более ранних версиях отпечатки оказываются разными. Поэтому в ранних версиях одинаковые запросы с разными указаниями после обобщения остаются разными.