Некоторое время смотрел на эту статью от Cybertec и думал — переводить её или нет? Но решил-таки перевести, было интересно. И несколько новых для себя моментов нашел.
Ссылка на оригинал.
Для подробного анализа производительности SQL-запроса вам потребуется вывод EXPLAIN (ANALYZE, BUFFERS). Бывает сложно построить план запроса, в котором фигурирует параметризованный оператор. Иногда вы даже не знаете значения этого параметров. Сейчас я покажу вам, как можно получить хотя бы простой EXPLAIN для общего (generic) параметризованного плана. Такой план выполнения лучше, чем ничего и, может быть, даже достаточен, чтобы догадаться, в чем проблема.
Параметризованные операторы
При помощи протокола расширенных запросов, PostgreSQL позволяет разделять SQL-операторы и константы, которые используются в запросе. Это повышает безопасность, поскольку делает SQL-инъекцию невозможной но, в основном, это функция для улучшения производительности. Такие параметризованные операторы могут быть объявлены и повторно использованы с разными параметрами, тем самым избавляя базу данных от повторного анализа одного и того же оператора. Более того, используя эту функциональность, PostgreSQL иногда может избежать гораздо больших накладных расходов, связанных с созданием планов выполнения для каждого оператора.
Обычно вы сталкиваетесь с параметризованными операторами в двух ситуациях:
- подготовленные операторы с параметрами (обычно используются через клиентский API);
- статические операторы SQL в функциях PL/pgSQL, которые используют переменные.
В подготовленных операторах параметры обозначаются символами $1, $2 и т. д. А вот в случае PL/pgSQL вы их не видите; обработчик вызовов PL/pgSQL заменит эти сочетания символов именами переменных. Обратите внимание, что параметризованные операторы могут использоваться только с SELECT, INSERT, UPDATE, DELETE и VALUES.
Общие (generic) планы для параметризованных операторов
Обычно PostgreSQL генерирует план выполнения всякий раз, когда выполняется запрос. Однако PostgreSQL может кэшировать планы выполнения для параметризованных подготовленных операторов. Планы кэшируются только внутри одного сеанса базы данных; общего кэша планов запросов в PostgreSQL нет.
Если PostgreSQL думает, что может сделать это без ущерба для производительности, он начнет использовать общий план для оператора. Такой план выполнения не учитывает значения параметров и может использоваться повторно. Решение о переходе на общий план основано на эвристике и обычно принимается, когда оператор выполняется в шестой раз. Вы можете узнать такой план по тому, как в нём описываются параметры — либо конкретные значения, либо символы-заменитель $1, $2 и тд. Вот пример использования подготовленного оператора:
PREPARE stmt(text) AS SELECT oid FROM pg_class WHERE relname = $1;
EXPLAIN (COSTS OFF) EXECUTE stmt('pg_proc');
QUERY PLAN
═════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class
Index Cond: (relname = 'pg_proc'::text)
(2 rows)
Следующие четыре выполнения EXPLAIN выглядят одинаково, но затем мы видим:
EXPLAIN (COSTS OFF) EXECUTE stmt('pg_attribute');
QUERY PLAN
═════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class
Index Cond: (relname = $1)
(2 rows)
PostgreSQL начал использовать общий план! С этого момента время планирования запроса станет намного меньше (я бы тут немного привязался к словам — время планирования же будет ноль — запрос уже спланирован, а вот (при прочих равных) время выполнения может быть меньше).
Принудительный общий план для параметризованного оператора
Вы можете использовать параметр plan_cache_mode, чтобы повлиять на поведение, описанное в предыдущем разделе. Параметр по умолчанию «auto» выбирает эвристику, описанную выше, когда PostgreSQL после нескольких выполнений решает, будет ли выгоден общий план или нет.
С настройкой «force_custom_plan» вы можете попросить PostgreSQL никогда не использовать общий план (т. е. он будет всегда использовать частный (custom) план). Это хорошая идея, если общий план окажется не таким хорошим, как думал PostgreSQL. Это также хорошая настройка для хранилищ данных, где обычно выполняются дорогостоящие аналитические запросы, а экономия времени на планирование менее важна, чем получение наилучшего плана выполнения.
Наконец, значение «force_generic_plan» заставляет PostgreSQL немедленно использовать общий план. Мы воспользуемся этой настройкой позже.
Где можно встретить параметризованный оператор?
Параметризованные операторы в журнале PostgreSQL
Параметризованный оператор выглядит в журнале следующим образом:
LOG: duration: 0.012 ms execute stmt: SELECT oid FROM pg_class WHERE relname = $1 DETAIL: parameters: $1 = 'pg_proc'
Обычно параметры регистрируются в виде подробного сообщения, но если их много, может потребоваться много работы, чтобы заменить все $n..n+1 значениями параметров. Вы также не увидите тип данных параметра в журнале, поэтому вам, возможно, придется посмотреть определение таблицы, чтобы узнать, следует ли писать 42 или ’42’. Если оператор вызвал ошибку, а вы не установили для log_parameter_max_length_on_error ненулевое значение — вы вообще не получите описания параметров:
ERROR: canceling statement due to statement timeout STATEMENT: SELECT oid FROM pg_class WHERE relname = $1
Параметризованные операторы в pg_stat_statements
pg_stat_statements — это швейцарский нож для анализа рабочих нагрузок БД. Одной из его особенностей является то, что он игнорирует значения констант, так что операторы, отличающиеся только константами, объединяются вместе. Поэтому, если вы запросите представление pg_stat_statements, вы увидите заменители даже в операторах, которые изначально не были параметризованы. Кроме того, поскольку pg_stat_statements собирает статистику большого количества выполнения оператора, оно не собирает фактические значения параметров ни для одного из них.
Необходимость общего плана
Если вы нашли сообщение об ошибке в журнале или в pg_stat_statements, вы хотите проанализировать его производительность. Для этого вам нужно угадать соответствующие значения параметров, чтобы вы могли получить план выполнения с помощью EXPLAIN (ANALYZE, BUFFERS). Это может быть утомительно и занять много времени.
Для первоначального анализа полезно увидеть план выполнения, сгенерированный EXPLAIN (без ANALYZE). Поскольку «обычный» EXPLAIN не выполняет запрос, он не должен зависеть от фактических значений параметров — и это нас устраивает, если нам нужен именно общий план. К сожалению, EXPLAIN отказывается генерировать общий план для параметризованного оператора:
EXPLAIN SELECT oid FROM pg_class WHERE relname = $1; ERROR: there is no parameter $1 LINE 1: EXPLAIN SELECT oid FROM pg_class WHERE relname = $1;
Мы получаем эту ошибку, даже если установим plan_cache_mode = force_generic_plan.
Генерация общего плана для параметризованного оператора с помощью PREPARE
Мы можем улучшить эту ситуацию. Используя PREPARE, мы можем создать параметризованный подготовленный оператор:
PREPARE stmt(name) AS SELECT oid FROM pg_class WHERE relname = $1;
Теперь мы можем принудительно использовать общий план и выполнить EXPLAIN подготовленного оператора. Мы можем указать NULL в качестве значения параметра, так как NULL существует для каждого типа данных, а значение параметра в любом случае игнорируется:
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE stmt(NULL);
QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.28..8.29 rows=1 width=4)
Index Cond: (relname = $1)
(2 rows)
DEALLOCATE stmt;
Единственная оставшаяся трудность заключается в том, что нам нужно определить соответствующие типы данных для параметров.
Использование псевдотипа unknown с параметризованным оператором
Псевдотипы — это типы данных, которые нельзя использовать в определениях таблиц. Один из таких типов данных «unknown»: он используется во время синтаксического анализа запроса для строковых констант, тип данных которых должен быть определён позже, в зависимости от контекста. Мы можем использовать unknown в качестве типа данных для параметров и позволить PostgreSQL самому определить подходящий тип данных:
PREPARE stmt(unknown) AS SELECT oid FROM pg_class WHERE relname = $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE stmt(NULL);
QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.28..8.29 rows=1 width=4)
Index Cond: (relname = $1)
(2 rows)
DEALLOCATE stmt;
Объединяем все это в расширение
Теперь у нас есть простой алгоритм для получения общего плана параметризованного оператора:
- подсчитать количество параметров;
- создать подготовленный оператор с этим количеством «неизвестных» параметров;
- установить для «plan_cache_mode» значение «force_generic_plan»;
- выполнить EXPLAIN подготовленных операторов, используя NULL в качестве аргументов.
Я засунул всё это в функцию и написал расширение generic_plan. Оно написано на PL/pgSQL и не требует прав суперпользователя для установки. А вот как она работает:
CREATE EXTENSION IF NOT EXISTS generic_plan;
SELECT generic_plan('SELECT * FROM pg_sequences WHERE max_value < last_value + $1');
generic_plan
═════════════════════════════════════════════════════════════════════════════════════════════
Subquery Scan on pg_sequences (cost=1.09..24.10 rows=1 width=245)
Filter: (pg_sequences.max_value < (pg_sequences.last_value + $1))
-> Nested Loop (cost=1.09..24.09 rows=1 width=245)
Join Filter: (c.oid = s.seqrelid)
-> Seq Scan on pg_sequence s (cost=0.00..1.06 rows=6 width=49)
-> Materialize (cost=1.09..22.76 rows=3 width=136)
-> Hash Join (cost=1.09..22.74 rows=3 width=136)
Hash Cond: (c.relnamespace = n.oid)
-> Seq Scan on pg_class c (cost=0.00..21.62 rows=8 width=76)
Filter: (relkind = 'S'::"char")
-> Hash (cost=1.05..1.05 rows=3 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=3 width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
(13 rows)
Вывод
Бывает сложно собрать значения параметров для анализа выполнения параметризованного оператора, но с помощью расширения generic_plan мы можем, по крайней мере, легко получить общий план. Используемые трюки — это подготовленный оператор с параметрами типа «unknown», настройка plan_cache_mode и использование NULL в качестве значений параметров.
Если вам интересно узнать больше о параметрах, посмотрите мою статью про Типы данных параметров запроса и производительности.
–
От переводчика: в данном случае можно ничего не скачивать и не устанавливать, достаточно перейти на его страницу в git, там открыть sql-файл (generic_plan—1.0.sql), скопировать определение функции generic_plan и выполнить в psql’e — готово! Можно функцией пользоваться.
UPD: А с 16-й версии сервера уже и не нужно ничего придумывать дополнительно, потому что Лоренц Альбе написал патч (который закоммитил Том Лейн). Можно будет написать вот так — EXPLAIN (generic_plan) и получить общий план для параметризованного запроса.
–
Лоренц Альбе — старший консультант и инженер службы поддержки CYBERTEC. Он работает с PostgreSQL и вносит свой вклад в развитие PostgreSQL с 2006 года.
Еще раз ссылка на оригинал.
Leave a Reply