SQL-Ex blog

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


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


• Информацию, относящуюся к используемым алгоритмам соединения, таких как хэш-соединение, соединение слиянием или соединение вложенными циклами.

Ключевые параметры для оптимизации запросов в PostgreSQL

ANALYZE

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

EXPLAIN ANALYZE SELECT * FROM users;

QUERY PLAN 

--------------------------------------------------------------

Seq Scan ON users (cost=0.00..18584.82 ROWS=1890082 width=24) 

(actual TIME=0.005..232.380 ROWS=1600000 loops=1)

Total runtime: 269.666 ms

(2 rows)

Если вы хотите выполнить анализ таких операторов, как INSERT, UPDATE или DELETE, без воздействия на данные, то это следует делать внутри транзакции, например, так:

BEGIN;

EXPLAIN ANALYZE UPDATE users set id = 1 where name = 'abc';

ROLLBACK;

VERBOSE

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

COSTS

Опция COSTS включает предварительную начальную и общую стоимость каждого узла плана, а также предварительные число строк и ширину каждой строки в плане запроса. PostgreSQL использует оптимизатор на основе стоимости, означающее, что он оценивает многочисленные стратегии выполнения запроса и выбирает наиболее эффективный с точки зрения стоимости. Более низкая стоимость говорит о более эффективной операции. COSTS отражает усилия, требуемые для выполнения операции в терминах времени и ресурсов.

Замечание: Общее заблуждение касается оценки стоимости в миллисекундах или других единиц времени, но это не так. Стоимость измеряется в произвольных единицах на основе GUC, упомянутых ниже.

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

seq_page_cost = 1.0 

random_page_cost = 4.0 

cpu_tuple_cost = 0.01 

cpu_index_tuple_cost = 0.005 

cpu_operator_cost = 0.0025

Более подробно об этих параметров смотрите здесь.

Но как рассчитывается стоимость запроса? Например:

EXPLAIN SELECT * FROM account;

QUERY PLAN 

-----------------------------------------------------------------

Seq Scan on account(cost=0.00..3372572.84 rows=73953584 width=386)

Стоимость обычно представлена в виде диапазона (cost=a..b). a показывает оценку затрат на получение первой строки, а b - на получение всех строк. В примере выше начальная стоимость последовательного сканирования равна 0.00. Для последовательного сканирования начальная стоимость обычно близка к нулю, т.к. можно сразу получать строки. Напротив, операции типа сортировки имеют более высокую стоимость, поскольку значительная часть работы должны быть выполнена, прежде чем строки могут быть возвращены.

Давайте вычислим общую стоимость операции выше. Сначала нам необходимо найти число кортежей и общее число страниц, содержащих эти кортежи, с помощью таблицы каталога pg_class:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'account';



relpages = 2633037 => Это будет использовано как оценка чтений последовательных страниц

reltuples = 73953584 => Это будет использовано в качестве общего числа кортежей в таблице



Общая стоимость последовательного сканирования = (оценка чтений последовательных страниц * seq_page_cost) + (cpu_tuple_cost + cpu_operator_cost) * общее число кортежей, 

которые должны быть просканированы



Так для нашего примера:

Общая стоимость последовательного сканирования = (2633037 * 1) + (0.0025 * 0.01) * 73953584 = 3372572.8

Одним исключением является предложение LIMIT, когда планировщик использует для оценки возможность досрочного завершения дочерней операции. Например,

EXPLAIN SELECT * FROM users LIMIT 10;



QUERY PLAN 

-----------------------------------------------------------------

Limit (cost=0.00..0.058 rows=10 width=386) 

-> Seq Scan on users (cost=0.00..3372572.84 rows=73953584 width=386)

Здесь общая стоимость узла Seq Scan (последовательное сканирование) остается 3372572.84, однако общая стоимость операции Limit оценивается в 0,058, поскольку планировщик ожидает, что потребуется обработать лишь небольшое число строк и, как результат, LIMIT остановит сканирование раньше.

BUFFERS

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

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

• hit для блоков означает, что чтение с диска не потребовалось, поскольку требуемый блок уже находился в памяти.


• read для блоков означает, что данные не находились в общих буферах (shared_buffers), поэтому PostgreSQL должен был извлекать данные из кэша ОС/диска (нам неизвестно).


• dirtied для блоков говорит о числе блоков, которые были изменены этим запросом (может происходить при вставке/обновлении или выборке битов хинтов).


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

Пример:

Buffers: shared hit=4785 READ=4541 dirtied=204 written=663, temp READ=788

Это означает:

PostgreSQL обнаружил 4785 блоков в памяти и извлек 4541 блоков из кэша ОС/диска.
204 блока были изменены (например, новые строки, обновления, удаления, биты хинтов).
663 блока были сброшены из памяти, чтобы освободить пространство.
788 блоков были прочитаны из временных файлов для выполнения операций хеширования/агрегации/сортировки.

WAL

Включает информацию о генерации записей WAL (журнализация). В частности, включает число записей, число образов полных страниц (fpi) и число WAL, генерируемых в байтах. В текстовом формате печатаются только ненулевые значения. Этот параметр может использоваться только вместе с ANALYZE. По умолчанию установлен в FALSE.

FORMAT

Задает формат вывода, которым может быть TEXT, XML, JSON или YAML. Нетекстовый вывод содержит ту же самую информацию, что и текстовый формат вывода, но он легче в применении для программ парсинга. По умолчанию установлен в TEXT.

Примеры: планы запросов PostgreSQL

Первый запрос

explain (analyze,buffers)

SELECT 

"r_partner".id 

FROM 

"r_partner" 

WHERE 

(

("r_partner"."active" = true) 

AND (

"r_partner"."x_studio_driver_application_id" = 405300

)

) 

AND (

(

(

(

"r_partner"."partner_share" IS NULL 

or "r_partner"."partner_share" = false

) 

OR (

"r_partner"."company_id" in (1)

)

) 

OR "r_partner"."company_id" IS NULL

) 

AND (

(

(

(

"r_partner"."type" != 'private'

) 

OR "r_partner"."type" IS NULL

) 

OR "r_partner"."type" IS NULL=

) 

OR ("r_partner"."type" = 'private')

)

) 

ORDER BY 

"r_partner"."display_name"

Limit 1;

План запроса до использования индекса

QUERY PLAN 

-----------------------------------------------------------------

Limit (cost=0.42..6322.54 rows=1 width=23) (actual time=398.392..398.393 rows=1 loops=1)

Buffers: shared hit=373305

-> Index Scan using r_partner_display_name_index on r_partner (cost=0.42..101154.35 rows=16 width=23) (actual time=398.390..398.391 rows=1 loops=1)

Filter: (active AND ((partner_share IS NULL) OR (NOT partner_share) OR (company_id = 1) OR (company_id IS NULL)) AND (x_studio_driver_application_id = 405300) AND (((type)::text <> 'private'::text) OR (type IS NULL) OR (type IS NULL) OR ((type)::text = 'private'::text)))

Rows Removed by Filter: 370671

Buffers: shared hit=373305

Planning Time: 0.144 ms

Execution Time: 398.417 ms

Давайте получим информацию из приведенного выше плана:

• Полное время выполнения = 398,417 мс


• Shared hit=373305, это означает, что мы получили 373305 блоков/страниц из памяти. Каждый блок в PostgreSQL имеет размер 8Кб, т.е. мы получили доступ к 373305 * 8/1000 = 3Гб данных, только чтобы вернуть 1 строку.


• Rows Removed by Filter: 370671, это означает, что 99% строк были отброшены после чтения.

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

Давайте создадим индекс на столбце x_studio_driver_application_id, поскольку он имеет высокую кардинальность и почти уникальный. Не забудьте выполнить анализ таблицы для обновления статистики.

create index concurrently idx_r_partner_driver_application_id on r_partner(x_studio_driver_application_id);

ANALYZE r_partner;

План запроса после создания индекса

QUERY PLAN 

-----------------------------------------------------------------

Limit (cost=6.26..6.26 rows=1 width=23) (actual time=0.026..0.027 rows=1 loops=1)

Buffers: shared hit=4

-> Sort (cost=6.26..6.30 rows=16 width=23) (actual time=0.025..0.026 rows=1 loops=1)

Sort Key: display_name

Sort Method: quicksort Memory: 25kB

Buffers: shared hit=4

-> Index Scan using idx_r_partner_driver_application_id on r_partner (cost=0.42..6.18 rows=16 width=23) (actual time=0.018..0.019 rows=1 loops=1)

Index Cond: (x_studio_driver_application_id = 405300)

Filter: (active AND ((partner_share IS NULL) OR (NOT partner_share) OR (company_id = 1) OR (company_id IS NULL)) AND (((type)::text <> 'private'::text) OR (type IS NULL) OR (type IS NULL) OR ((type)::text = 'private'::text)))

Buffers: shared hit=4

Planning Time: 0.149 ms

Execution Time: 0.047 ms

Теперь мы видим, что

• idx_r_partner_driver_application_id используется для столбца x_studio_driver_application_id.


• Shared_hit = 4, т.е. теперь мы получаем доступ только к 4 * 8Кб = 32Кб набору данных.


• Время выполнения уменьшилось с 398мс до 0,047мс.

Второй запрос

SELECT count(1) FROM accounts WHERE ((accounts.state = 'cancel') 

AND (accounts.date <= '2023-12-31'))

AND (accounts.company_id IS NULL OR (accounts.company_id in (1)))

План запроса после создания индекса

QUERY PLAN 

-----------------------------------------------------------------

Finalize Aggregate (cost=997128.91..997128.92 rows=1 width=8) (actual time=988.165..990.682 rows=1 loops=1)

Buffers: shared hit=896417

-> Gather (cost=997128.49..997128.90 rows=4 width=8) (actual time=988.066..990.675 rows=5 loops=1)

Workers Planned: 4

Workers Launched: 4

Buffers: shared hit=896417

-> Partial Aggregate (cost=996128.49..996128.50 rows=1 width=8) (actual time=985.039..985.039 rows=1 loops=5)

Buffers: shared hit=896417

-> Parallel Seq Scan on accounts (cost=0.00..996114.35 rows=5654 width=0) (actual time=0.071..984.457 rows=5084 loops=5)

Filter: ((date <= '2023-12-31'::date) AND ((company_id IS NULL) OR (company_id = 1)) AND ((state)::text = 'cancel'::text))

Rows Removed by Filter: 4550296

Buffers: shared hit=896417

Planning:

Buffers: shared hit=4

Planning Time: 0.167 ms

Execution Time: 990.718 ms

Давайте получим информацию из этого плана:

• Полное время выполнения = 990,718


• Shared hit = 896417, что означает обращение к блокам/страницам, находящимся в памяти. Поскольку каждый блок в PostgreSQL занимает 8Кб, то для получения количества строк нам потребовался доступ к 7,2Гб (896417 * 8/1000) данных.


• Параллельное последовательное сканирование выполнялось с loops = 5, что означает 5 параллельных рабочих потоков (1 ведущий и 4 запущенных рабочих потоков, как видно из Workers Launched: 4). Большую часть времени запрос проводит в этом режиме. Число отфильтрованных строк равно 4550296, а число возвращенных строк - 5084. Заметим, что это средние значения строк на один цикл. Поскольку мы имеем loops = 5, можно сказать, что примерно 4550296 * 5 = 22,751,480 строк было отфильтровано (99% всей таблицы) и 5084 * 5 = 25,420 строк было возвращено в этом параллельном сканировании.

Поскольку этот запрос ежедневно выполняется с высокой частотой, можем ли мы оптимизировать его при использовании подходящего индекса? Давайте проверим сначала столбец state, т.к. он используется как фильтр.

select count(*),state from account_move group by state;



count     |   state

----------------------

25436     |  cancel

118014    |  draft

22633447  |  posted

Имеется только 3 уникальных значения в столбце state и 95% данных имеют состояние posted. Это выглядит так, что мы можем выгадать при частичном индексе на столбце state.

CREATE INDEX CONCURRENTLY idx_account_move_state on account_move(state) where state in ('cancel','draft');

ANALYZE account_move;

Мы могли бы создать полный индекс на столбце state, но:

• Это увеличит время создания индекса, а также его размер.


• Индекс будет включать строки, которые никогда не будут присутствовать в поиске и, возможно, что планировщик никогда не будет использовать его при условии state = posted.

План запроса после создания индекса

QUERY PLAN 

-----------------------------------------------------------------

Aggregate (cost=2268.88..2268.89 rows=1 width=8) (actual time=24.444..24.445 rows=1 loops=1)

Buffers: shared hit=23040

-> Index Scan using idx_account_move_state on account_move (cost=0.29..2199.06 rows=27930 width=0) (actual time=0.026..22.491 rows=25419 loops=1)

Index Cond: ((state)::text = 'cancel'::text)

Filter: ((date <= '2023-12-31'::date) AND ((company_id IS NULL) OR (company_id = 1)))

Rows Removed by Filter: 17

Buffers: shared hit=23040

Planning:

Buffers: shared hit=4

Planning Time: 0.192 ms

Execution Time: 24.476 ms

Здесь мы видим, что:

• idx_account_move_state используется для столбца state.


• Shared_hit = 23040, т.е. мы теперь получаем доступ только к набору данных 23040 * 8Кб = 184Мб.


• Время выполнения сократилось с 990мс до 24мс.

Замечание. Помните, что индексы не следует использовать всякий раз, когда вы хотите оптимизировать запрос. Каждый индекс подлежит обновлению при каждой записи, они занимают пространство в зависимости от набора данных и могут избежать ГОРЯЧЕЙ оптимизации.

Оптимизация запросов с использованием планов EXPLAIN в PostgreSQL

Чтение объяснения плана может оказаться сложным, особенно для новичков в PostgreSQL. Однако, фокусируясь на некоторых фундаментальных понятиях, вы можете постепенно стать более опытным в интерпретации предоставляемой информации и в определении потенциальных проблем производительности ваших запросов.

• Отдельно EXPLAIN показывает оценки стоимости. Вместе с ANALYZE вы фактически выполняете запрос и видите время, затраченное на создание плана выполнения, плюс время, требуемое на выполнения запроса согласно этому плану. При использовании BUFFERS вы можете отследить количество блоков, прочитанных из памяти, запрошенных с диска и другие подробности, связанные с вводом/выводом.


• Таблицы, задействованные в запросе, должны быть вакуумированы и проанализированы. Вы можете проверить столбцы last_*vacuum и last_*analyze в таблице pg_stat_all_tables для каждой таблицы. Дополнительную информацию относительно вакуумации читайте здесь.


• Ищите узлы со следующими операциями и стиатистикой. Они могут потенциальны быть узкими местами.


• Высокая стоимость


• Плохая оценка числа строк (планируемых по сравнению с возвращаемыми)


• Высокое число операций с буфером (чтение и попадание)


• Фильтры, которые отсеивают множество строк


• Последовательное сканирование больших таблиц


• Операция слияния с внешним диском


• Записи и чтение из временных таблиц

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


• Следующие параметры следует установить в postgresql.conf для лучшей производительности:


• shared_buffers


• effective_cache_size


• work_mem


• maintenance_work_mem


• random_page_cost

Больше информации об оптимизации памяти в PostgreSQL смотрите здесь.

Для визуализации планов запросов вы можете использовать инструменты DALIBO и DEPESZ.

Ссылки по теме
1. Анатомия плана запроса в PostgreSQL
2. Получение плана выполнения запроса в PostgreSQL
3. Обзор соединений в PostgreSQL
4. Кардинальное число (мощность множества)
5. PostgreSQL (auto) vacuum - уже не тайна
6. PostgreSQL для администраторов SQL Server: первые четыре настройки для проверки