Классический вопрос, который разработчик задает своему администратору базы данных или владелец бизнеса консультанту PostgreSQL, почти всегда звучит одинаково: «Почему выполнение запросов к базе данных занимает так много времениЭ» Традиционный набор причин:
- неэффективный алгоритм когда вы решите ПРИСОЕДИНИТЬСЯ к нескольким CTE на протяжении пары десятков тысяч записей
- нерелевантная статистика если фактическое распределение данных в таблице уже сильно отличается от собранного ANALYZE в прошлый раз
- «затыкаться» на ресурсы и выделенных вычислительных мощностей ЦП уже не хватает, гигабайты памяти постоянно перекачиваются, либо диск не успевает за всеми «хотелками» базы данных
- блокировка от конкурирующих процессов
) или модуль auto_explain .
Но, как указано в той же документации,
«Понимание плана — это искусство, и чтобы овладеть им, требуется определенный опыт…»Но можно обойтись и без этого, если использовать правильный инструмент! Как обычно выглядит план запроса? Что-то вроде того:
или вот так:Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049.0.050 rows=1 loops=1) Index Cond: (relname = $1) Filter: (oid = $0) Buffers: shared hit=4 InitPlan 1 (returns $0,$1) -> Limit (actual time=0.019.0.020 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on pg_class pg_class_1 (actual time=0.015.0.015 rows=1 loops=1) Filter: (relkind = 'r'::"char") Rows Removed by Filter: 5 Buffers: shared hit=1
"Append (cost=868.60.878.95 rows=2 width=233) (actual time=0.024.0.144 rows=2 loops=1)"
" Buffers: shared hit=3"
" CTE cl"
" -> Seq Scan on pg_class (cost=0.00.868.60 rows=9972 width=537) (actual time=0.016.0.042 rows=101 loops=1)"
" Buffers: shared hit=3"
" -> Limit (cost=0.00.0.10 rows=1 width=233) (actual time=0.023.0.024 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> CTE Scan on cl (cost=0.00.997.20 rows=9972 width=233) (actual time=0.021.0.021 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> Limit (cost=10.00.10.10 rows=1 width=233) (actual time=0.117.0.118 rows=1 loops=1)"
" Buffers: shared hit=2"
" -> CTE Scan on cl cl_1 (cost=0.00.997.20 rows=9972 width=233) (actual time=0.001.0.104 rows=101 loops=1)"
" Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"Но прочитать план в тексте «с листа» очень сложно и непонятно:
- отображается в узле сумма по ресурсам поддерева то есть чтобы понять, сколько времени ушло на выполнение того или иного узла, или насколько именно это чтение из таблицы вынесло данные с диска, нужно как-то вычесть одно из другого
- необходимо время узла умножить на петли да, вычитание - это не самая сложная операция, которую нужно делать "в голове" - ведь время выполнения указано среднее для одного выполнения узла, а их могут быть сотни
- ну и все это вместе мешает нам ответить на главный вопрос - так кто «самое слабое звено» ?
А это значит, что нам нужно.
Инструмент
В нем мы постарались собрать все ключевые механики, помогающие понять «кто виноват и что делать» по плану и запросу.Ну и поделитесь частью своего опыта с сообществом.
Знакомьтесь и пользуйтесь - объяснение.
tensor.ru
Видимость планов
Легко ли понять план, когда он выглядит вот так? Seq Scan on pg_class (actual time=0.009.1.304 rows=6609 loops=1)
Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms
Не хорошо.
А вот так, в сокращенной форме когда ключевые показатели разделены, все гораздо понятнее: 
Но если план посложнее, он придет на помощь График распределения времени по узлам: 
Ну а для самых сложных вариантов он спешит на помощь график прогресса : 
Например, бывают весьма нетривиальные ситуации, когда план может иметь более одного фактического корня: 
Структурные подсказки
Ну а если вся структура плана и его слабые места уже изложены и видны, то почему бы не выделить их разработчику и не объяснить на «русском языке»?
Мы собрали уже пару десятков таких шаблонов рекомендаций.
Построчный профилировщик запросов
Теперь, если наложить исходный запрос на анализируемый план, то можно увидеть, сколько времени было потрачено на каждый отдельный оператор — примерно так:
.
или даже вот так: 
Подстановка параметров в запрос
Если вы «прикрепили» к плану не только запрос, но и его параметры из строки журнала DETAIL, то его можно дополнительно скопировать в один из вариантов:- с подстановкой значений в запросе
для непосредственного исполнения на вашей базе и дальнейшего профилирования
SELECT 'const', 'param'::text;
- с подстановкой значений через PREPARE/EXECUTE
для эмуляции работы планировщика, когда параметрическую часть можно игнорировать — например, при работе с секционированными таблицами
DEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
Архив планов
Вставляйте, анализируйте, делитесь с коллегами! Планы останутся в архиве, и вы сможете вернуться к ним позже: объяснение.tensor.ru/архив
Но если вы не хотите, чтобы ваш план увидели другие, не забудьте поставить галочку «не публиковать в архиве».В следующих статьях я расскажу о трудностях и решениях, которые возникают при анализе плана.
Теги: #базы данных #Администрирование баз данных #postgresql #Визуализация данных #dba #explain #explain.tensor.ru
Вместе с данным постом часто просматривают:
-
Все О Деньгах Adsense
19 Oct, 24 -
Что Следует Прочитать Об Angular 2
19 Oct, 24 -
Сюжет «6 Часов До Запуска Бака»
19 Oct, 24 -
Релиз: Scriptaculous V.1.7.0
19 Oct, 24
