pg_hint_plan. Управление планами запросов в Pangolin

Версия: 1.5.0.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: отсутствуют.

Схема размещения: hint_plan.

Расширение pg_hint_plan управляет планом выполнения с помощью подсказывающих фраз (подсказок, hint), записываемых в виде простых описаний в SQL-комментариях особого вида.

Объекты

Настроечные параметры pg_hint_plan

Для Pangolin расширение pg_hint_plan модифицировано: запросы создания, изменения и удаления ролей не обрабатываются расширением pg_hint_plan и не попадают в лог даже при включенном детальном логировании расширения.

Наименование	Описание	Значение по умолчанию	Рекомендуемое значение
pg_hint_plan.enable_hint	Включает/выключает pg_hint_plan	on	on
pg_hint_plan.enable_hint_table	Разрешает/запрещает использование подсказок (hint) для запросов из таблицы подсказок	on
pg_hint_plan.parse_messages	Определяет уровень логирования, с которым в журнал будут попадать сообщения об ошибках разбора подсказок. Допустимые значения*: error, warning, notice, info, log, debug	info	warning
pg_hint_plan.debug_print	Указывает детализацию отладочных сообщений. Допустимые значения**: off, on, detailed, verbose		off
pg_hint_plan.message_level	Определяет уровень логирования для отладочных сообщений. Допустимые значения*: error, warning, notice, info, log, debug		info

* — Уровни важности сообщений ошибок синтаксического анализа и отладочных сообщений. Описание допустимых значений:

• ERROR — сообщает об ошибке, из-за которой прервана текущая команда;
• WARNING — предупреждения о возможных проблемах;
• NOTICE — информация, которая может быть полезной пользователям;
• INFO — неявно запрошенная пользователем информация;
• LOG — информация, полезная для администраторов;
• DEBUG — максимальный уровень детализации для разработчиков.

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

• off — 0 (сообщения выключены);
• on — 1;
• detailed — 2;
• verbose — 3.

Список подсказок (hints)

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

Группа	Формат	Описание
Метод сканирования	SeqScan(таблица)	Принудительное последовательное сканирование таблицы
	TidScan(таблица)	Принудительное сканирование таблицы по TID
	IndexScan(таблица[ индекс...])	Принудительное сканирование таблицы по индексу. Сканирование ограничивается заданными индексами
	IndexOnlyScan(таблица[ индекс...])	Принудительное сканирование таблицы по индексу. Сканирование ограничивается заданными индексами. Если сканирование только по индексу невозможно, может использоваться обычное сканирование по индексу
	BitmapScan(таблица[ индекс...])	Принудительное сканирование таблицы по битовой карте. Сканирование ограничивается заданными индексами
	IndexScanRegexp(таблица[ регулярное выражение POSIX...])	Принудительно выбирает сканирование таблицы по индексу. Сканирование ограничивается индексами с именами, соответствующими указанному регулярному выражению POSIX
	IndexOnlyScanRegexp(table[ регулярное выражение POSIX...])	Принудительно выбирает сканирование таблицы только по индексу. Сканирование ограничивается индексами с именами, соответствующими указанному регулярному выражению POSIX
	BitmapScanRegexp(table[ регулярное выражение 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 заданное количество исполнителей устанавливается принудительно
GUC	Set(GUC-параметр значение)	Установка значения для GUC-параметра на время планирования запроса

Примечание:

Подробная информация о расширении pg_hint_plan приведена в официальной документации.

Особенности

В данном разделе приведены особенности работы с расширением pg_hint_plan.

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

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

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

  • запросы, возвращающие одну запись (SELECT, INSERT, UPDATE и DELETE);
  • запросы, возвращающие множественные записи (RETURN QUERY);
  • динамические SQL-выражения (EXECUTE);
  • открытие курсора (OPEN);
  • итерация по результату запроса (LOOP FOR);

• комментарий с подсказкой (hint) должен быть помещен после первого слова запроса, так как обработка выражений PL/pgSQL отбрасывает комментарии вне выражений (комментарий перед выражением также будет отброшен).

  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;

Строчные и прописные символы в именах объектов

Имена объектов в подсказках (hint) регистрозависимы.

Например, имя объекта TBL в подсказке соответствует только имени TBL в базе данных и не соответствует именам без кавычек TBL, tbl и Tbl.

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

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

Конкретный вход таблицы при множественном использовании

Расширение pg_hint_plan идентифицирует целевые объекты подсказок (hint) с использованием псевдонимов, если они заданы.

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

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

Управление планом запроса в представлении или правиле перезаписи

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

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

Если имена не совпадают, объектам в представлениях можно назначить псевдонимы. Использование псевдонимов в запросах позволит обращаться к представлениям и влиять на поведение запроса.

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)

Наследование таблиц

Подсказки (hint) могут указывать только на родителя наследования, и подсказка влияет на всех наследников. Подсказки, одновременно указывающие прямо на наследников, игнорируются.

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

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

Примечание:

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

Выражение VALUES

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

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

Подзапросы

Подзапросы в следующем контексте иногда можно указать в подсказке (hint), используя имя 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)

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

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

Поведение подсказки NoIndexScan

Подсказка NoIndexScan включает NoIndexOnlyScan.

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

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

Между тем, подсказка PARALLEL с нулевыми количеством рабочих процессов запрещает параллельное сканирование.

Установка значений параметров pg_hint_plan через подсказку Set

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

• подсказки по изменению enable_hint, enable_hint_tables игнорируются, хотя в логах отладки они указываются как примененные «used hints»;
• установка debug_print и message_level начинает работать с середины обработки целевого запроса.

Доработка

Доработка:

1. Адаптация под длину имен объектов в 128 символов - для тестов.
2. Интеграция с функциональностью разделяемых подготовленных запросов.
3. Адаптация для работы с pg_outline.
4. Исключено логирование запросов CREATE ROLE/ALTER ROLE.

Версия: 4.5.0.

Ограничения

Ограничения отсутствуют.

Установка

Сведения

Установка расширения может быть произведена в процессе развертывании СУБД Pangolin при использовании настроек по умолчанию (документ «Руководство по установке», раздел «Автоматизированная установка при помощи Ansible-скриптов»).

Расширение включается в БД template1 для того, чтобы расширение появлялось во всех создаваемых базах данных. Установка расширения производится в схему расширений hint_plan, изменение схемы установки невозможно.

Установка в ручном режиме

В некоторых случаях (например, создана новая база, или расширения не были установлены во время работы инсталлятора Pangolin) установку расширений необходимо произвести вручную, для этого:

1. Добавьте значения pg_stat_statements, pg_hint_plan и pg_outline в настроечный параметр shared_preload_libraries в конфигурационном файле postgresql.conf.

2. Подключите расширения:

   CREATE EXTENSION pg_stat_statements;
   CREATE EXTENSION pg_hint_plan;
   CREATE EXTENSION pg_outline;

3. Включите расширения pg_outline и pg_hint_plan.

Активация расширения

Расширение pg_hint_plan по умолчанию выключено.

Чтобы включить расширение pg_hint_plan, используйте настроечные параметры pg_hint_plan.enable_hint и pg_hint_plan.enable_hint_table:

• для применения ко всем сессиям — установите значение on в postgresql.conf;
• для конкретных сессий — установите значение true через команды SET или ALTER USER SET/ALTER DATABASE SET.

Пример:

SET pg_hint_plan.enable_hint = TRUE;
SET pg_hint_plan.enable_hint_table = TRUE;

Отключение функциональности

Чтобы отключить функциональность расширения, установите значение off для настроечных параметров pg_hint_plan.enable_hint и pg_hint_plan.enable_hint_table.

Для полного отключения функциональности исключите загрузку библиотеки для всего экземпляра Pangolin одним из следующих способов:

• исключите библиотеку из параметра shared_preload_libraries;
• измените настройки конкретных сессий через ALTER USER SET/ALTER DATABASE SET.

После отключения функциональности удалите расширение командой:

DROP EXTENSION pg_hint_plan

Ошибки

Расширение pg_hint_plan прекращает синтаксический анализ при любой ошибке и в большинстве случаев использует подсказки (hint), уже проанализированные на момент ошибки. Далее приведены типичные ошибки.

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

• Неверно указан объект — если объект указан неверно, подсказки (hint) будут игнорироваться без уведомления. Об этом виде ошибки сообщается в журнале сервера как «not used hints», при условии, что pg_hint_plan.debug_print имеет значение отличное от off.

• Избыточные или конфликтующие подсказки — при повторяющихся или конфликтующих подсказках (hint), применяться будет последняя подсказка (hint). Об ошибках такого типа в журнале сервера сообщается как о «duplication hints» при условии, что pg_hint_plan.debug_print имеет значение отличное от off.

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

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

Далее приведены функциональные ограничения для расширения pg_hint_plan:

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

• Подсказки, пытающиеся задать невыполнимые планы — в случае, когда указанный подсказкой (hint) план выполнить нельзя, планировщик выбирает любые исполнимые планы:

  • использовать вложенный цикл для FULL OUTER JOIN;
  • использовать индексы, столбцы которых не указаны в условиях;
  • выполнить сканирование по TID для запросов без условий по ctid.

• Запросы в ECPG — ECPG удаляет комментарии в запросах, написанных как embedded SQL, поэтому подсказки (hint) не могут передаваться из этих запросов. Единственное исключение — команда EXECUTE передает заданную строку без изменений. Для таких случаев используйте таблицу подсказок.

• Работа совместно с pg_stat_statements — расширение pg_stat_statements генерирует идентификатор запроса (query id), игнорируя комментарии. В результате одинаковые запросы с разными подсказками (hint) объединяются в один и тот же запрос.

Настройка

Включение журналирования

Чтобы включить журналирование, в конфигурации сервера необходимо применить рекомендуемые настройки:

pg_hint_plan.parse_messages = warning
pg_hint_plan.debug_print = off
pg_hint_plan.message_level = debug

Методы доступа

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

Метод доступа	Узел в плане	Подсказка для включения	Подсказка для выключения
Последовательное сканирование таблицы	Seq Scan	SeqScan(таблица)	NoSeqScan(таблица)
Индексное сканирование таблицы	Index Scan Index Scan Backward	IndexScan(таблица[ индекс...]) IndexScanRegexp(таблица[ POSIX Regexp...])	NoIndexScan(таблица)
Строгое индексное сканирование таблицы	Index Only Scan	IndexOnlyScan(таблица[ индекс...]) IndexOnlyScanRegexp(таблица[ POSIX Regexp...])	Любая подсказка из NoIndexOnlyScan(таблица) или NoIndexScan(таблица)
Сканирование таблицы по битовой карте индекса	Bitmap Index Scan → Bitmap Heap Scan BitmapAnd, BitmapOr	BitmapScan(таблица[ индекс...]) BitmapScanRegexp(таблица[ POSIX Regexp...])	NoBitmapScan(таблица)
Сканирование таблицы по TID	Tid Scan	TidScan(таблица)	NoTidScan(таблица)

Здесь POSIX Regexp... – список регулярных выражений, используемых для того, чтобы не перечислять поодиночке множество индексов с похожими названиями.

Чтобы зафиксировать метод доступа, в подсказке указывается псевдоним таблицы-источника и, если в методе используются индексы — их также нужно перечислить, разделяя список пробелами.

Внимание!

Подсказка NoIndexScan включает в себя действие подсказки NoIndexOnlyScan.

Наличие карты видимости таблицы (слой visibility map) необходимо для метода доступа Index Only Scan. Этот слой по умолчанию не создается при создании таблицы, поэтому до первого AUTOVACUUM / VACUUM / VACUUM FULL по новой таблице не получится осуществить доступ к таблице методом Index Only Scan.

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

Примеры:

• Указание использовать сканирование по конкретному индексу:

  /*+ IndexScan(t1 ix_table1_2ind_f1_f2) */ select ctid, * from table1_2ind t1 where f1 = 10;

• Указание не использовать последовательное сканирование:

  /*+ NoSeqScan(t1) */ select * from table1 t1 where t1.f1 > 1;

Закрепить в запросе выбранный метод объединения можно при помощи подсказок:

Метод объединения источников	Узел в плане	Подсказка для включения	Подсказка для выключения
Nested Loop – вложенный цикл	Nested Loop	NestLoop(таблица таблица[ таблица...])	NoNestLoop(таблица таблица[ таблица...])
Hash Loin - хеш-соединение	Hash Join Hash Semi Join Hash Anti Join	HashJoin(таблица таблица[ таблица...])	NoHashJoin(таблица таблица[ таблица...])
Merge Join - соединение слиянием сортированных списков	Merge Join Merge Left Join Merge Right Join	MergeJoin(таблица таблица[ таблица...])	NoMergeJoin(таблица таблица[ таблица...])

Примеры:

• Указание использовать объединение nested loop:

  /*+ NestLoop(t1 t2)*/ select t1.f2, t2.f2 from table1 t1, table2 t2 where t1.f1=t2.f1;

• Указание не использовать объединение hash join:

  /*+ NoHashJoin(t1 t2) */ select t1.f2 from table1 t1 where t1.f2 in (select f2 from table2 t2 where f2 > '10000');

Использование модуля

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

Подсказки (hint) должны быть заданы в первом комментарии вида /* */ в тексте запроса. Первым символом в теле комментария должен быть +.

Пример запроса с подсказками (в качестве метода соединения выбрано соединение по хешу, а 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)

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

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

Фиксация метода сканирования

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

• Sequential Scan — последовательное сканирование таблицы.

  Полный перебор всех блоков таблицы с извлечением 100% данных.

  Подходит для:

  • очень маленьких таблиц (десятки блоков);
  • полностью кешированных таблиц, которые помещаются в области разделяемых буферов сервера и файловом кеше ОС;
  • всех остальных случаев, когда требуется извлечь данные полностью или почти полностью.

  Не подходит для:

  • извлечения малой доли данных (до 1-3%) из объемной таблицы.

• Index Scan — индексное сканирование таблицы.

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

  Подходит для:

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

  Не подходит для:

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

• Index-only Scan — cтрогое индексное сканирование таблицы.

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

  Подходит для:

  • такого индексного сканирования, когда запрос не требует данных, не входящих в индекс.

  Неприменим, когда:

  • данных в индексе недостаточно для получения результатов запроса;
  • у таблицы нет карты видимости, не выполнялся AUTOVACUUM / VACUUM.

• Bitmap Index Scan — сканирование таблицы по битовой карте индекса.

  Промежуточный вариант между последовательным сканированием таблицы и поиском по индексу.

  Пример:

  Для предикатов с не самой высокой селективностью невыгодно читать таблицу полностью (90% блоков — лишние, не содержат интересных данных) или извлекать блоки таблицы в случайном порядке. Пример такого предиката: id between 100 and 1000000 на таблице с десятком млн записей, где:

  • при последовательном сканировании пришлось бы прочесть с диска множество лишних блоков;
  • при поиске по индексу — обработать порядка миллиона ссылок из индекса на записи в хаотично разбросанных блоках таблицы.

  Поэтому лучше действовать в две стадии:

  1. Сформировать битовую карту всей таблицы, в которой 1 биту будет соответствовать 1 запись (точная битовая карта, exact) или 1 блок таблицы (грубая карта, lossy), для чего пройти весь индекс, выставляя в 1 биты, указывающие на искомые записи или блоки, содержащие искомые записи.
  2. Один раз последовательно пройти таблицу, интересуясь только отмеченными в полученной карте записями / блоками.

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

• TID Scan — сканирование таблицы по TID (tuple identifier).

  Быстрое прямое извлечение записи по ее внутреннему физическому адресу, например: where ctid='(1000,10)'::tid, где:

  • ctid — это псевдоколонка любой из таблиц, физический идентификатор записи. Он состоит из номера блока (в примере 1000) и номера записи в блоке (в примере 10).

Примечание:

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

Исключение – строгое индексное сканирование, которое может выполняться и без первых полей в ключе. В этом случае индекс служит не в качестве указателя для поиска записей, а в качестве частичного «снимка» таблицы или кеша часто запрашиваемых полей.

Фиксация метода объединения результатов

Методы объединения источников в общий результат:

• Nested Loop – вложенный цикл.

  В цикле открывается перебор подходящих записей ведущего (внешнего) источника. Для каждой из записей внешнего источника выбираются все записи из внутреннего (ведомого) источника, подходящие по условию соединения. Далее происходит переход к следующей записи ведущего источника. Для внешних соединений ведущим (первым по порядку, во внешнем цикле) всегда будет внешний источник, левый для LEFT OUTER JOIN или правый для RIGHT OUTER JOIN.

  Подходит для:

  • очень небольших наборов данных, особенно когда поддерживается индексами с обеих сторон соединения;
  • условий соединения, которые не являются равенствами (ON a.id >= b.first_a_id) (более того, это единственный метод выполнения таких условий).

  Не подходит:

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

• Hash Join – хеш-соединение.

  Выбирается полностью ключ объединения из обоих источников. Для этого строится хеш-таблица выборки ключа из ведущего источника, размещается в памяти (work_mem), если места не хватает - то во временных файлах на диске. Из ведомого источника отбирать значения ключа, хешировать их и искать совпадения значений хеш-функций ключа: hash(a.id) = hash(b.a_id). При совпадении хешей сравнить ключи по данным из таблиц, разрешая конфликты хеширования и извлекая те данные, которых нет в ключе объединения.

  Подходит:

  • для объединения больших объемов данных, в том числе без поддержки индексов;
  • для условий объединения по присутствию в списке (EXISTS, IN) – называется Hash Semi Join;
  • и по отсутствию в списке (NOT EXISTS, NOT IN) — это Hash Anti Join.

  Не подходит:

  • когда ограничены физические ресурсы: недостаточно work_mem, мало свободного пространства для временных файлов – приходится хешировать ведущий источник порциями, повторять проходы ведомого источника по каждой из порций;
  • для внешних соединений — не поддерживает их.

• Merge Join – соединение слиянием сортированных списков.

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

  Подходит для:

  • внешних соединений средних по объемам источников, особенно при поддержке индексов с обеих сторон.

  Не подходит:

  • когда ограничены физические ресурсы: недостаточно work_mem, мало места на диске, ограничены процессорные ресурсы для сортировки. Столь же чувствителен к work_mem, как и хеш-соединение. Предполагает размещение в памяти / на диске полных списков значений ключа для выполнения сортировки.

Фиксация порядка соединения таблиц

Порядок таблиц в соединении имеет большое значение для планировщика. Например, 10 раз просканировать таблицу из 1 млн записей или 1 млн раз просканировать таблицу из 10 записей — операции разного порядка, по времени и по затратам ресурсов они существенно различаются.

Если количество соединяемых источников не превышает 3-5, то планировщику легко перебрать все варианты соединяемых пар. Если же источников более 12 (порог настраивается), то из-за огромного количества вариантов сочетаний будет применяться эвристический алгоритм (GEQO). Его задача — найти не самый оптимальный план, но сделать это быстро.

Если оптимальный порядок соединения найден, то зафиксировать его позволяет подсказка Leading.

• Leading(таблица таблица[ таблица...]) — указание планировщику: объединять источники в указанном порядке, от ведущей таблицы до ближайшей к результату.

• Leading(<пара для объединения>) — указание объединять пару источников в указанном порядке и направлении. Пара для объединения — пара, в которую могут входить таблицы и/или другие пары. Описания пар, заключенные в скобки, могут образовывать вложенные структуры.

Примеры:

• Указание использовать порядок объединения

  /*+ Leading(t2 t3 t1) */ select t1.f2, t2.f2 from table1 t1 join table2 t2 on t1.f1=t2.f1 join table1 t3 on t3.f1=t2.f1 where t2.f1<1000;

• Указание использовать порядок объединения с группировкой

  /*+ Leading((t2 (t3 t1))) */ select t1.f2, t2.f2 from table1 t1 join table2 t2 on t1.f1=t2.f1 join table1 t3 on t3.f1=t2.f1 where t2.f1<1000;

Ссылки на документацию разработчика

Дополнительно поставляемый модуль pg_hint_plan:

• https://github.com/ossc-db/pg_hint_plan;
• http://pghintplan.osdn.jp/hint_list.html.