Перейти к основному содержимому

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

Описание​

Каждый пользователь, подключенный к СУБД Pangolin, взаимодействует с сервером с помощью SQL-предложений: SELECT, INSERT, UPDATE, DELETE и других. Получение данных из СУБД возможно также с использованием подготовленных предложений (PREPARE), а в программном коде на процедурных языках — через курсоры и встроенные SQL-вставки.

При настройке производительности под оптимизируемым запросом понимают ту часть любого из этих предложений, которая выполняет выборку данных из СУБД. Один и тот же запрос может быть реализован разными физическими способами при одинаковом логическом результате. Например, доступ к таблице можно получить напрямую или с помощью индекса, а соединение трех и более таблиц — выполнить в различном порядке. Различные физические способы доступа к данным, методы формирования результата, а также порядок и метод выполнения соединений называются «путями». Любое их сочетание, соответствующее логике исходного запроса, называется «планом запроса».

Для возможности ручной оптимизации планов запросов в Pangolin используются расширения pg_outline, pg_hint_plan и pg_store_plans.

Расширения pg_store_plans, pg_outline и pg_hint_plan входят в состав СУБД Pangolin. Эти расширения, в совокупности, предоставляют возможность фиксации и подмены планов запросов для оптимизации работы планировщика запросов.

Для автоматического переиспользования планов запросов между сессиями применяется механизм Shared Pool, дополнительную информацию смотрите в разделе Shared Pool для планов запросов.

Настройка​

Расширение pg_hint_plan​

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

Функциональные возможности:

  • подсказки (hint) планировщику запросов для управления планами выполнения запросов, в части:

    • методов сканирования таблиц;
    • методов соединения таблиц;
    • порядка соединения;
    • корректировки числа строк результата соединения;
    • настройки параллельности обработки таблиц;
    • установки значений параметров конфигурации на время планирования запроса;

  • управление планами подготовленных запросов (prepared statements) через заданные в тексте этих запросов подсказок (hint);

  • управление планами запросов в составе PL/pgSQL-блока через подсказки (hint), заданные в тексте запросов в PL/pgSQL-блоках;

  • дополнение запросов подсказками «на лету» на этапе планирования запроса на уровне всей СУБД в составе СУБД Pangolin. Это позволяет выполнять:

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

    • компенсационную корректировку для:

      • запросов с динамическими параметрами (подсказкой подстановщиков, вместо конкретных значений);
      • подготовленных запросов;

  • управление (через настроечные параметры) на уровне сессии пользователя или всей СУБД в составе СУБД Pangolin:

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

С настройкой расширения и его объектами можно ознакомиться в одноименном разделе расширения pg_hint_plan документа «Описание расширений продукта СУБД Pangolin».

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

Использование подсказок описано в документе «Описание расширений продукта СУБД Pangolin», раздел «pg_hint_plab», подраздел «Использование подсказок».

Расширение pg_outline​

Расширение pg_outline предназначено для изменения плана выполнения запросов.

Функциональные возможности:

  • управление (добавить, заменить, удалить, включить, выключить) правилами подмены (фиксациями подсказок pg_hint_plan) планов запросов;
  • возврат информации о созданных правилах подмены планов запросов;
  • возврат идентификатора запроса (queryId) для текста запроса (queryText).

С настройкой расширения и его объектами можно ознакомиться в одноименном разделе расширения pg_outline документа «Описание расширений продукта СУБД Pangolin».

Расширение pg_store_plans​

Расширение pg_store_plans предназначено для накапливания статистики по планам выполнения всех SQL-запросов, выполняемых в БД.

С настройкой расширения и его объектами можно ознакомиться в одноименном разделе расширения pg_store_plans документа «Описание расширений продукта СУБД Pangolin».

Shared Pool для планов запросов​

Внимание!

Данная функциональность не предназначена для использования в производственной среде.

Shared Pool для планов запросов — механизм автоматического кэширования и переиспользования планов выполнения SQL-запросов между сессиями через разделяемую память.

Механизм позволяет снизить длительность выполнения транзакций за счет исключения повторного планирования однотипных запросов, отличающихся только значениями параметров или констант. Кешированные планы хранятся в общей памяти экземпляра СУБД и доступны всем серверным процессам.

Область применения

Механизм применяется при планировании:

  • подготовленных запросов (prepared statements);
  • запросов, поступающих по простому протоколу запросов (simple protocol);
  • вызовов через SPI, включая SPI_execute.

Общий алгоритм работы

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

  1. После завершения этапа анализа запроса (parse + analyze) вычисляется идентификатор плана (pool_id), который формируется на основании структуры запроса и характеристик его параметров.

  2. По вычисленному pool_id осуществляется поиск записи в Shared Pool.

  3. Если запись найдена:

    • из Shared Pool загружается сериализованный план выполнения;
    • план десериализуется и возвращается в качестве результата этапа планирования.

  4. Если запись не найдена:

    • выполняется стандартный процесс планирования запроса;
    • полученный план сериализуется в бинарном виде;
    • сериализованное представление помещается в Shared Pool по вычисленному pool_id.

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

Инвалидация записей Shared Pool

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

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

После удаления записи при повторном поступлении соответствующего запроса выполняется его перепланирование с последующим сохранением нового плана в Shared Pool.

Реальная очистка пула происходит только при выполнении COMMIT транзакции (явной или неявной). Если транзакция, содержащая DDL, откатывается, Shared Pool не очищается.

Обработка запросов по simple protocol

Для запросов, поступающих по simple protocol, реализуется предварительная нормализация: все константы заменяются на параметризованные плейсхолдеры, и запрос приводится к форме, эквивалентной prepared statement. Планирование выполняется для нормализованной формы запроса.

Это позволяет использовать одну и ту же запись в Shared Pool для запросов, имеющих одинаковую структуру, но отличающихся значениями констант.

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

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

примечание

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

Настройка​

Конфигурационные параметры​

Подробное описание параметров приведено в разделе «shared_pool_view. Кеширования результатов маскирования и сериализации».

Управление​

Включение функции​

  1. Добавьте строку enable_shared_pool_for_plans = on в конфигурационный файл postgresql.conf.
  2. Установите shared_pool_entry_count в значение больше ноля.
  3. Выполните перечитывание конфигурации СУБД Pangolin.

Выключение функции​

  1. Измените значение параметра enable_shared_pool_for_plans на off в файле postgresql.conf.
  2. Выполните перечитывание конфигурации СУБД Pangolin.

Диагностика​

Для диагностики состояния Shared Pool в расширение shared_pool_view добавлена функция shared_pool_explain_plan(), которая по pool_id возвращает текстовое представление плана, хранящегося в ячейке Shared Pool.

С дополнительной информацией об объектах расширения shared_pool_view можно ознакомиться в разделе shared_pool_view документа «Описание расширений продукта СУБД Pangolin».

Ограничения​

Следующие GUC-параметры запрещено изменять:

  • enable_shared_pool_plan_binary_serialize;
  • enable_shared_pool_local_cache.

Список GUC-параметров, состояние которых используются для расчета хеша плана (если у пользователя параметры разные, то и хеши разные):

  • seq_page_cost;
  • random_page_cost;
  • cpu_tuple_cost;
  • cpu_index_tuple_cost;
  • cpu_operator_cost;
  • parallel_setup_cost;
  • parallel_tuple_cost;
  • effective_cache_size;
  • effective_io_concurrency;
  • min_parallel_table_scan_size;
  • min_parallel_index_scan_size;
  • max_parallel_workers_per_gather;
  • max_parallel_workers;
  • work_mem;
  • geqo_threshold;
  • join_collapse_limit;
  • from_collapse_limit;
  • enable_seqscan;
  • enable_indexscan;
  • enable_indexonlyscan;
  • enable_bitmapscan;
  • enable_tidscan;
  • enable_nestloop;
  • enable_mergejoin;
  • enable_hashjoin;
  • enable_sort;
  • enable_incremental_sort;
  • enable_material;
  • enable_memoize;
  • enable_hashagg;
  • enable_gathermerge;
  • enable_parallel_append;
  • enable_parallel_hash;
  • enable_geqo;
  • enable_partitionwise_aggregate;
  • enable_partitionwise_join;
  • parallel_leader_participation.

Превью планов созданных в другой БД недоступно.

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

Совместная работа с pg_hint_plan и pg_outline не реализована. Хинт плана заданный через механизм этих расширений не учитывается при подсчете pool id.

Права на функции взаимодействия с shared pool на данный момент есть у PUBLIC.

В полной мере не реализовано кеширование запросов UPDATE, MERGE и INSERT.

Ограничения для prepared statements​

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

  • параметр <операция сравнения> константа;
  • константа <операция сравнения> параметр.

Где <операция сравнения> включает стандартные операторы сравнения: =, <>, <, <=, >, >= и аналогичные.

Примеры поддерживаемых запросов:

PREPARE q(int) AS
SELECT * FROM t WHERE id = $1;

PREPARE q(int) AS
SELECT * FROM t WHERE $1 >= created_at;

PREPARE q(int, text) AS
SELECT * FROM t WHERE amount > $1 AND $2 <> 'Jim';

Переиспользование планов не выполняется, если параметр используется в арифметических выражениях, функциях, выражениях LIMIT/OFFSET или выражениях, влияющих на структуру запроса:

-- Следующие запросы НЕ будут кешироваться
PREPARE q(int) AS
SELECT * FROM t WHERE id = $1 + 1;

PREPARE q(int) AS
SELECT * FROM t ORDER BY id LIMIT $1;

PREPARE q(text) AS
SELECT * FROM t WHERE lower(name) = $1;
Внимание!

Кеширование планов запросов с выражением IN выполняется тогда и только тогда, когда аргумент-список в выражении состоит только из параметров подготовленных запросов:

PREPARE q(int, int, int) AS SELECT * FROM t WHERE id IN ($1, $2, $3);  -- план будет закеширован
PREPARE q(int, int, int) AS SELECT * FROM t WHERE id IN ($1, $2, $3, 42);  -- план НЕ будет закеширован

Ограничения для simple protocol​

Для запросов по simple protocol шаринг планов возможен только в том случае, если константы используются исключительно в простых сравнительных предикатах вида:

  • имя_колонки <операция сравнения> константа;
  • константа <операция сравнения> имя_колонки.

Примеры поддерживаемых запросов:

SELECT * FROM t WHERE id = 10;
SELECT * FROM t WHERE created_at > '2024-01-01';
SELECT * FROM t WHERE 100 <= amount;

Шаринг планов не применяется, если константные значения используются в выражениях LIMIT/OFFSET, арифметических выражениях, функциях или выражениях, влияющих на структуру запроса:

-- Следующие запросы НЕ будут кешироваться
SELECT * FROM t ORDER BY id LIMIT 10;
SELECT * FROM t WHERE id = 10 + 5;
SELECT * FROM t WHERE lower(name) = 'abc';
Внимание!

Кеширование планов запросов с выражением IN выполняется тогда и только тогда, когда аргумент-список в выражении состоит только из констант:

SELECT * FROM t WHERE id IN (1, 2, 3);  -- план будет закеширован
SELECT * FROM t WHERE id IN (1, 2, (SELECT 3));  -- план НЕ будет закеширован
Подсказка

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

Ограничение при использовании partial index​

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

Это влияет на возможность использования частичного индекса (partial index), если условие индекса содержит выражение, зависящее от параметра запроса. В результате планировщик может не распознать возможность применения частичного индекса, и вместо Index Scan может быть выбран альтернативный способ доступа (например, Seq Scan).

При отключенном Shared Pool планировщик может выполнять более агрессивные оптимизации, включая использование partial index.

примечание

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