0
PostgreSQL EXPLAIN – Какова стоимость запросов?
EXPLAIN очень полезен для понимания производительности запроса Postgres. Он возвращает план выполнения, созданный планировщиком запросов PostgreSQL для данного оператора. Команда EXPLAIN указывает, будет ли поиск таблиц, на которые ссылается оператор, с использованием сканирования индекса или последовательного сканирования.
Понимание стоимости Postgres EXPLAIN
Первое, что вы заметите при просмотре вывода команды, EXPLAIN— это статистика затрат, поэтому вполне естественно задаться вопросом, что они означают, как рассчитываются и как используются.
Короче говоря, планировщик запросов PostgreSQL оценивает, сколько времени займет выполнение запроса (в произвольных единицах измерения), включая как стоимость запуска, так и общую стоимость каждой операции. Подробнее об этом позже. Когда у него есть несколько вариантов выполнения запроса, он использует эти затраты, чтобы выбрать самый дешевый и, следовательно, мы надеемся, самый быстрый вариант.
В каких единицах указаны затраты?
Затраты указаны в произвольных единицах. Распространенным заблуждением является то, что они измеряются в миллисекундах или какой-либо другой единице времени, но это не так.
Единицы стоимости привязаны (по умолчанию) к одному последовательному чтению страницы, стоимость которого составляет 1,0 единицы (seq_page_cost). Каждая обработанная строка добавляет 0,01 (cpu_tuple_cost), а каждое непоследовательное чтение страницы добавляет 4,0 ( random_page_cost). Существует еще много подобных констант, и все они настраиваются. Последний вариант особенно распространен, по крайней мере, на современном оборудовании. Мы рассмотрим это подробнее чуть позже.
Затраты на запуск
Первые цифры, которые вы увидите после, cost= известны как «стартовая стоимость». Это оценка того, сколько времени потребуется для получения первой строки . Таким образом, начальная стоимость операции включает стоимость ее дочерних элементов.
Для последовательного сканирования стоимость запуска обычно будет близка к нулю, поскольку получение строк может начаться сразу же. Для операции сортировки оно будет выше, поскольку большая часть работы должна быть выполнена до того, как строки начнут возвращаться.
Чтобы рассмотреть пример, давайте создадим простую тестовую таблицу с 1000 именами пользователей:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username text NOT NULL);
INSERT INTO users (username)
SELECT 'person' || n
FROM generate_series(1, 1000) AS n;
ANALYZE users;
Давайте посмотрим на простой план запроса с парой операций:
EXPLAIN SELECT * FROM users ORDER BY username; QUERY PLAN | --------------------------------------------------------------+ Sort (cost=66.83..69.33 rows=1000 width=17) | Sort Key: username | -> Seq Scan on users (cost=0.00..17.00 rows=1000 width=17)|
В приведенном выше плане запроса, как и ожидалось, предполагаемая стоимость выполнения оператора для is Seq Scan и 0.00 для Sortis 66.83.
Суммарные затраты
Вторая статистика затрат, следующая за стоимостью запуска и двумя точками, известна как «общая стоимость». Это приблизительная оценка того, сколько времени потребуется для возврата всех строк .
Давайте еще раз посмотрим на этот пример плана запроса:
QUERY PLAN | --------------------------------------------------------------+ Sort (cost=66.83..69.33 rows=1000 width=17) | Sort Key: username | -> Seq Scan on users (cost=0.00..17.00 rows=1000 width=17)|
Мы видим, что общая стоимость операции Seq Scan равна 17.00. Стоимость операции Sortсоставляет 69,33, что ненамного больше ее стартовой стоимости (как и ожидалось).
Общие затраты обычно включают стоимость предшествующих им операций. Например, общая стоимость операции сортировки, указанной выше, включает стоимость последовательного сканирования.
Важным исключением являются LIMITположения, которые планировщик использует для оценки возможности досрочного прерывания. Если ему требуется только небольшое количество строк, условия для которых являются общими, он может посчитать, что более простой вариант сканирования дешевле (вероятно, будет быстрее).
Например:
EXPLAIN SELECT * FROM users LIMIT 1; QUERY PLAN | --------------------------------------------------------------+ Limit (cost=0.00..0.02 rows=1 width=17) | -> Seq Scan on users (cost=0.00..17.00 rows=1000 width=17)|
Как вы можете видеть, общая стоимость, указанная в узле Seq Scan, по-прежнему равна 17,00, но полная стоимость операции Limit равна 0,02. Это связано с тем, что планировщик ожидает, что ему придется обработать только 1 строку из 1000, поэтому стоимость в этом случае оценивается как тысячная от общей суммы.
Как рассчитываются затраты
Для расчета этих затрат планировщик запросов Postgres использует как константы (некоторые из которых мы уже видели), так и метаданные о содержимом базы данных. Метаданные часто называют «статистикой».
Статистика собирается через ANALYZE(не путать с EXPLAIN одноименным параметром) и сохраняется в pg_statistic. Они также автоматически обновляются в рамках автоочистки .
Эта статистика включает в себя ряд очень полезных вещей, например, примерное количество строк в каждой таблице и наиболее распространенные значения в каждом столбце.
Давайте рассмотрим простой пример, используя те же данные запроса, что и раньше:
EXPLAIN SELECT count(*) FROM users; QUERY PLAN | -------------------------------------------------------------+ Aggregate (cost=19.50..19.51 rows=1 width=8) | -> Seq Scan on users (cost=0.00..17.00 rows=1000 width=0)|
В нашем случае статистика планировщика показала, что данные таблицы хранятся в пределах 7 страниц (или блоков) и что будет возвращено 1000 строк. Параметры стоимости seq_page_cost, cpu_tuple_cost, и cpu_operator_costостались по умолчанию 1, 0.01, и 0.0025 соответственно.
Таким образом, общая стоимость Seq Scan была рассчитана как:
Total cost of Seq Scan = (estimated sequential page reads * seq_page_cost) + (estimated rows returned * cpu_tuple_cost) = (7 * 1) + (1000 * 0.01) = 7 + 10.00 = 17.00
А для агрегата как:
Total cost of Aggregate = (cost of Seq Scan) + (estimated rows processed * cpu_operator_cost) + (estimated rows returned * cpu_tuple_cost) = (17.00) + (1000 * 0.0025) + (1 * 0.01) = 17.00 + 2.50 + 0.01 = 19.51
Как планировщик использует затраты
Поскольку мы знаем, что Postgres выберет план запроса с наименьшей общей стоимостью, мы можем использовать это, чтобы попытаться понять сделанный им выбор. Например, если запрос не использует ожидаемый индекс, вы можете использовать такие настройки, как enable_seqscanмассовое запрещение выбора определенного плана запроса. К этому моменту вы не должны удивляться, узнав, что подобные настройки работают за счет увеличения затрат!
Номера строк являются чрезвычайно важной частью оценки затрат. Они используются для расчета оценок для различных порядков соединения, алгоритмов соединения, типов сканирования и многого другого. Значительное отклонение оценок затрат по строкам может привести к значительному отклонению оценки затрат, что в конечном итоге может привести к выбору неоптимального плана.
Использование EXPLAIN ANALYZE для получения плана запроса
Когда вы пишете операторы SQL в PostgreSQL, эта ANALYZEкоманда играет ключевую роль в оптимизации запросов, делая их быстрее и эффективнее. Помимо отображения плана запроса и оценок PostgreSQL, этот EXPLAIN ANALYZEпараметр выполняет запрос (будьте осторожны с UPDATEи DELETE!) и показывает фактическое время выполнения и количество строк для каждого шага процесса выполнения. Это необходимо для мониторинга производительности SQL.
Вы можете использовать его EXPLAIN ANALYZEдля сравнения предполагаемого количества строк с фактическими строками, возвращаемыми каждой операцией.
Давайте посмотрим на пример, снова используя те же данные:
QUERY PLAN | -----------------------------------------------------------------------------------------------------------+ Sort (cost=66.83..69.33 rows=1000 width=17) (actual time=20.569..20.684 rows=1000 loops=1) | Sort Key: username | Sort Method: quicksort Memory: 102kB | -> Seq Scan on users (cost=0.00..17.00 rows=1000 width=17) (actual time=0.048..0.596 rows=1000 loops=1)| Planning Time: 0.171 ms | Execution Time: 20.793 ms |
Мы видим, что общая стоимость выполнения по-прежнему составляет 69,33, причем большая часть из них приходится на операцию сортировки, а 17,00 — на последовательное сканирование. Обратите внимание, что время выполнения запроса составляет чуть менее 21 мс.
Последовательное сканирование и индексное сканирование
Теперь давайте добавим индекс, чтобы избежать дорогостоящей сортировки всей таблицы:
CREATE INDEX people_username_idx ON users (username); EXPLAIN ANALYZE SELECT * FROM users ORDER BY username; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------+ Index Scan using people_username_idx on users (cost=0.28..28.27 rows=1000 width=17) (actual time=0.052..1.494 rows=1000 loops=1)| Planning Time: 0.186 ms | Execution Time: 1.686 ms |
Как видите, планировщик запросов теперь выбрал сканирование индекса, поскольку общая стоимость этого плана составляет 28,27 (меньше 69,33). Похоже, сканирование индекса оказалось более эффективным, чем последовательное сканирование, поскольку время выполнения запроса теперь составляет чуть менее 2 мс.
Помощь планировщику в более точной оценке
Мы можем помочь планировщику оценить более точно двумя способами:
- Помогите ему собрать лучшую статистику
- Настройте константы, которые он использует для вычислений.
Статистика может быть особенно плохой после большого изменения данных в таблице. ANALYZEТаким образом, при загрузке большого количества данных в таблицу вы можете помочь Postgres, запустив для нее руководство . Статистика также не сохраняется при обновлении основной версии, так что это еще один важный момент, чтобы сделать это.
Естественно, таблицы также меняются со временем, поэтому настройка параметров автоочистки так, чтобы она запускалась достаточно часто для вашей рабочей нагрузки, может оказаться очень полезной.
Если у вас возникли проблемы с неверными оценками для столбца с асимметричным распределением, вам может быть полезно увеличить объем информации, собираемой Postgres, с помощью этой команды ALTER TABLE SET STATISTICSили даже default_statistics_targetдля всей базы данных.
Другая распространенная причина плохих оценок заключается в том, что по умолчанию Postgres предполагает, что два столбца независимы. Это можно исправить, попросив его собрать данные корреляции по двум столбцам одной таблицы с помощью расширенной статистики .
Что касается постоянной настройки, существует множество параметров, которые вы можете настроить в соответствии с вашим оборудованием. Предполагая, что вы работаете на твердотельных накопителях, вам, скорее всего, потребуется как минимум настроить параметры random_page_cost. По умолчанию установлено значение 4, что в 4 раза дороже, чем seq_page_costмы рассматривали ранее. Это соотношение имело смысл на вращающихся дисках, но на твердотельных накопителях оно имеет тенденцию слишком сильно наказывать случайный ввод-вывод. Поэтому установка ближе к 1 или между 1 и 2 может иметь больше смысла. В ScaleGrid мы по умолчанию имеем значение 1.
Могу ли я удалить затраты из планов запросов?
По многим причинам, упомянутым выше, большинство людей оставляют расходы включенными при запуске EXPLAIN. Однако при желании их можно отключить с помощью COSTS параметра.
EXPLAIN (COSTS OFF) SELECT * FROM users LIMIT 1; QUERY PLAN | -----------------------+ Limit | -> Seq Scan on users|
Заключение
Напомним, что затраты в планах запросов — это оценки Postgres того, сколько времени займет SQL-запрос, в произвольных единицах.
Он выбирает план с наименьшей общей стоимостью на основе некоторых настраиваемых констант и некоторой собранной статистики.
Очень важно помочь ему более точно оценить эти затраты, чтобы помочь ему сделать правильный выбор и обеспечить производительность ваших запросов.
