В статье о буферах в PostgreSQL мы добавляли EXPLAIN (ANALYZE, BUFFERS) почти к каждому запросу, особо не вчитываясь в его вывод. Пора это исправить. PostgreSQL показывает использование буферов для каждого узла плана, и когда вы научитесь читать эти числа, то сможете точно понять, где ваш запрос тратил время на ожидание операций ввода-вывода (I/O), а где этого не происходило. Это один из самых базовых навыков при диагностике проблем с производительностью.
PostgreSQL 18: BUFFERS по умолчанию
Начиная с PostgreSQL 18, EXPLAIN ANALYZE автоматически включает статистику буферов — явно добавлять BUFFERS больше не нужно. В примерах ниже используется полная запись ради совместимости со старыми версиями, но в PG18+ обычный EXPLAIN ANALYZE даст ту же информацию.
Полный пример
В этой статье мы будем использовать следующую схему и заранее подготовленные данные.
CREATE TABLE customers ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);CREATE TABLE orders ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers(id), amount numeric(10,2) NOT NULL, status text NOT NULL DEFAULT 'pending', note text, created_at date NOT NULL DEFAULT CURRENT_DATE);INSERT INTO customers (name)SELECT 'Customer ' || iFROM generate_series(1, 2000) AS i;-- исходные данные: ~100 000 заказов, распределённых по 2022-2025 годамINSERT INTO orders (customer_id, amount, status, note, created_at)SELECT (random() * 1999 + 1)::int, (random() * 500 + 5)::numeric(10,2), (ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)::int], CASE WHEN random() < 0.3 THEN 'Some note text here for padding' ELSE NULL END, '2022-01-01'::date + (random() * 1095)::int -- примерно 3 года данныхFROM generate_series(1, 100000);-- убеждаемся, что статистика актуальнаANALYZE customers;ANALYZE orders;-- индексы намеренно создавать не будем-- и выполним пример запросаselect count(1) from customers;
Начнём со случайного запроса.
EXPLAIN (ANALYZE, BUFFERS)SELECT o.*, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.created_at > '2024-01-01';
И вот его вывод.
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=58.00..2253.87 rows=33784 width=71) (actual time=0.835..26.695 rows=33239.00 loops=1) Hash Cond: (o.customer_id = c.id) Buffers: shared hit=13 read=857 -> Seq Scan on orders o (cost=0.00..2107.00 rows=33784 width=58) (actual time=0.108..18.106 rows=33239.00 loops=1) Filter: (created_at > '2024-01-01'::date) Rows Removed by Filter: 66761 Buffers: shared read=857 -> Hash (cost=33.00..33.00 rows=2000 width=17) (actual time=0.697..0.698 rows=2000.00 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 118kB Buffers: shared hit=13 -> Seq Scan on customers c (cost=0.00..33.00 rows=2000 width=17) (actual time=0.007..0.231 rows=2000.00 loops=1) Buffers: shared hit=13 Planning: Buffers: shared hit=130 read=29 dirtied=3 Planning Time: 1.585 ms Execution Time: 28.067 ms
Так как здесь довольно много информации, разберём её по отдельным категориям.
Общие буферы: hit, read, dirtied и written
Как уже описывалось в предыдущей статье, это наиболее часто встречающиеся показатели статистики буферов.
shared hit — это количество страниц, найденных в общем буферном кеше, то есть уже находившихся в кэше. Это быстрый путь, при котором не требуется дисковый ввод-вывод. Чем выше значение, тем лучше для производительности.
shared read показывает количество страниц, которых не было в общем буферном кеше и которые были загружены с диска (или из кеша операционной системы). Каждая такая операция потенциально добавляет задержку, связанную с вводом-выводом (I/O).
Если вы видите в SELECT-запросе значение dirtied, это не ошибка. PostgreSQL устанавливает так называемые подсказочные биты (hint bits) и выполняет очистку HOT-цепочек во время чтения. Первый процесс, который читает страницу после записи, помечает её как «грязную». Это нормальное поведение и не является проблемой.
shared dirtied — это количество страниц, изменённых данным запросом. Запрос модифицировал данные, уже находившиеся в буферном пуле, и эти страницы в итоге должны быть записаны на диск.
shared written — количество страниц, записанных на диск во время выполнения запроса. Это происходит, когда запросу нужно освободить место в буферах и приходится синхронно вытеснять «грязные» страницы. Если вы регулярно видите это в SELECT-запросах, это может быть тревожным сигналом — фоновый процесс записи не справляется с нагрузкой.
Теперь посмотрим на сводную статистику буферов для нашего запроса:
Buffers: shared hit=13 read=857
Только 13 страниц находились в общем буферном кеше, тогда как 857 пришлось загрузить с диска (или из кеша операционной системы). Ни одна страница не была помечена как «грязная» и не записывалась на диск — это ожидаемо для чистого SELECT-запроса без побочных эффектов.
Но откуда взялись эти 13 попаданий в кеш? Разбивка по узлам плана даёт ответ:
-> Seq Scan on orders o Buffers: shared read=857-> Seq Scan on customers c Buffers: shared hit=13
Таблица customers (в данном случае небольшая — 2 000 строк, 13 страниц) полностью находилась в кеше — вероятно, к ней часто обращались или, как в нашем случае, её недавно читали. Таблица orders (100 000 строк, 857 страниц) не дала ни одного попадания — каждая страница потребовала обращения к вводу-выводу (I/O). Это типично после перезапуска или при сканировании таблицы, которая не помещается в shared buffers.
Интерпретация отношения
В рамках этой статьи будем рассматривать отношение между shared hit и общим количеством обработанных буферов. Существует ли «идеальное» значение, к которому стоит стремиться? Как мы увидим, универсального ответа нет.
Посчитаем его для нашего запроса:
hit_ratio = shared hit / (shared hit + shared read) = 13 / (13 + 857) = 1.5%
В нагрузке типа OLTP (онлайн-обработка транзакций) один и тот же небольшой набор строк запрашивается снова и снова — получить клиента по ID, найти заказ по номеру, проверить остатки товара. Рабочий набор данных составляет малую часть всей базы. Такие запросы затрагивают всего несколько страниц, и эти страницы остаются «горячими» в shared buffers, потому что к ним постоянно обращаются. Хорошо настроенная OLTP-система естественным образом приходит к высокому значению hit ratio — не потому, что кто-то задал целевое значение, а потому что паттерн доступа удерживает нужные данные в кеше.
Если смотреть на число без контекста, оно выглядит плохо. Если бы такой показатель был у большинства OLTP-запросов, можно было бы уверенно сказать — есть проблема. Но в данном случае запрос выполнялся на только что загруженных данных с «холодным» кешем — каждая страница таблицы orders считывалась впервые. Если запустить тот же запрос ещё раз, скорее всего, большая часть из этих 857 чтений превратится в попадания в кеш по мере «прогрева» shared buffers и кеша страниц ОС. В тестовой среде (где нет других нагрузок) вы, скорее всего, увидите почти 100%.
Важно не абсолютное значение, а hit ratio конкретного запроса во времени относительно его собственной базовой линии:
-
Отчётный запрос, сканирующий большой диапазон дат, может стабильно показывать 10–30% — это нормально, он работает с «холодными» данными.
-
Запрос, обслуживающий страницу логина, должен быть близок к 100%. Если он падает до 80%, значит что-то изменилось — возможно, выросла таблица, был пересоздан индекс или shared_buffers испытывает давление от новой нагрузки.
-
Запрос, который на прошлой неделе имел 95%, а теперь показывает 40%, требует расследования — независимо от того, кажется ли 40% «хорошим» или «плохим» в отрыве от контекста.
Это отношение — инструмент диагностики, а не оценка «насколько всё хорошо». Используйте его, чтобы находить регрессии, сравнивать результаты до и после оптимизации и понимать, куда на самом деле уходит время запроса. Низкое значение при большом времени выполнения указывает на то, что узкое место — это ввод-вывод. Высокое значение при большом времени выполнения говорит о том, что искать причину нужно в другом — возможно, в CPU, количестве обрабатываемых строк или неудачном плане.
Контекст важнее абсолютных значений. Сравнивайте похожие запросы во времени, а не с абстрактными эталонами.
Локальные буферы
Локальные буферы отслеживают операции ввода-вывода (I/O) для временных таблиц. В отличие от обычных таблиц, которые находятся в shared buffers, временные таблицы используют память на уровне отдельного серверного процесса — каждое подключение получает собственный локальный буферный пул, управляемый параметром temp_buffers.
CREATE TEMP TABLE temp_large_orders ASSELECT o.id, o.amount, o.status, o.created_at, c.name AS customer_nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.amount > 200;EXPLAIN (ANALYZE, BUFFERS)SELECT status, count(*), sum(amount)FROM temp_large_ordersGROUP BY status;
Первое, на что стоит обратить внимание: здесь вообще нет shared buffers, по крайней мере на этапе выполнения. Весь запрос работал только с локальными буферами, потому что временные таблицы недоступны другим серверным процессам.
QUERY PLAN------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1281.60..1284.10 rows=200 width=72) (actual time=24.659..24.661 rows=4.00 loops=1) Group Key: status Batches: 1 Memory Usage: 32kB Buffers: local hit=576 -> Seq Scan on temp_large_orders (cost=0.00..979.20 rows=40320 width=48) (actual time=0.009..5.965 rows=60731.00 loops=1) Buffers: local hit=576 Planning: Buffers: shared hit=36 read=5 Planning Time: 0.294 ms Execution Time: 24.708 ms(10 rows)
Из отдельных значений здесь могут встречаться local hit/read — по смыслу они аналогичны shared, только относятся к временным таблицам в локальном буферном пуле конкретного серверного процесса.
Ещё один вариант — local dirtied/written, отражающий изменения во временных таблицах. Dirtied означает, что запрос изменил страницы в локальном буферном пуле. Written означает, что «грязные» страницы пришлось сбросить на диск, чтобы освободить место для новых — используется тот же механизм вытеснения по круговому алгоритму, что и в shared buffers, но уже применительно к локальному буферному пулу. В отличие от shared buffers, записи во временные таблицы не порождают WAL и не участвуют в контрольных точках.
На практике local written встречается редко — PostgreSQL достаточно эффективно обрабатывает переполнение временных таблиц, так что вы вряд ли увидите этот показатель, если только temp_buffers не настроен слишком маленьким для вашей нагрузки на временные таблицы.
Временные буферы: когда work_mem недостаточно
Если локальные буферы не так часто рассматриваются как проблема и не так часто попадаются на глаза, то временные буферы позволяют отследить случаи, когда сортировки, хеш-операции и другие операции выходят за пределы текущего work_mem и начинают сбрасывать данные из памяти на диск.
SET work_mem = '256kB';EXPLAIN (ANALYZE, BUFFERS)SELECT o.id, o.amount, o.status, o.created_at, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idORDER BY o.amount DESC;
Здесь мы специально занизили work_mem, чтобы увидеть последствия.
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------- Sort (cost=38374.70..38874.70 rows=200000 width=36) (actual time=109.345..120.574 rows=200000.00 loops=1) Sort Key: o.amount DESC Sort Method: external merge Disk: 9736kB Buffers: shared hit=1738, temp read=3636 written=3722 -> Hash Join (cost=116.00..4353.56 rows=200000 width=36) (actual time=1.597..34.857 rows=200000.00 loops=1) Hash Cond: (o.customer_id = c.id) Buffers: shared hit=1738 -> Seq Scan on orders o (cost=0.00..3712.00 rows=200000 width=27) (actual time=0.016..6.973 rows=200000.00 loops=1) Buffers: shared hit=1712 -> Hash (cost=66.00..66.00 rows=4000 width=17) (actual time=1.568..1.569 rows=4000.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 235kB Buffers: shared hit=26 -> Seq Scan on customers c (cost=0.00..66.00 rows=4000 width=17) (actual time=0.012..0.629 rows=4000.00 loops=1) Buffers: shared hit=26 Planning: Buffers: shared hit=15 Planning Time: 1.184 ms Execution Time: 123.932 ms
Здесь вы видите temp read/written — количество страниц, прочитанных из временных файлов на диске и записанных в них. Это означает, что операция не поместилась в памяти.
Важно не запутаться в названиях
temp read/writtenв EXPLAIN никак не связано с параметромtemp_buffers.
temp_buffers= память для временных таблиц (CREATE TEMP TABLE)
temp read/written= сброс данных на диск при сортировках и хеш-операциях, которые не поместились в память и регулируются параметромwork_mem
Строка Sort Method: external merge Disk: 9736kB это подтверждает: сортировка 200 000 строк при work_mem, равном всего 256 КБ, вынудила PostgreSQL выгрузить около 9,7 МБ во временные файлы на диске. Значение temp written=3722 появилось на этапе сортировки, когда страницы сбрасывались наружу, а temp read=3636 — на этапе слияния, когда PostgreSQL считывал их обратно, чтобы получить итоговый отсортированный результат.
Обратите внимание и на другое: Hash Join и всё, что находится ниже него, показывают только shared hit=1738, без каких-либо временных буферов. Хеш-таблица для 4 000 клиентов спокойно уместилась в 235 КБ памяти. Сброс на диск произошёл только в узле Sort — статистика буферов всегда относит операции ввода-вывода (I/O) к тому узлу, который их вызвал.
Попробуйте увеличить work_mem до разумного значения — и сброс на диск исчезнет:
SET work_mem = '16MB';
После этого вы не должны увидеть никаких временных буферов. Сортировка завершится в памяти, время выполнения сократится, а единственные операции ввода-вывода будут связаны с чтением самих табличных данных.
Чтобы сократить использование временных файлов, можно:
-
Увеличить
work_mem(но осторожно: это параметр на уровень операции, а не всего запроса, поэтому сложный запрос с несколькими сортировками или хеш-соединениями выделяетwork_memдля каждой такой операции) -
Оптимизировать запрос так, чтобы до сортировки обрабатывалось меньше строк
-
И, пожалуй, самое важное — рассмотреть возможность добавления индексов, чтобы вообще избежать сортировки: индекс по
orders(amount DESC)полностью убрал бы узел Sort
Буферы планирования
До этого момента мы полностью игнорировали буферы планирования. Это относительно новая возможность, появившаяся в PostgreSQL 13, которая позволяет увидеть использование буферов на этапе планирования запроса отдельно от этапа выполнения:
Planning: Buffers: shared hit=36 read=5
Зачем планированию вообще нужны буферы? Планировщик читает системные каталоги (pg_class, pg_statistic, pg_index и т. д.), чтобы понять структуру таблиц и доступную статистику. Сложные запросы, затрагивающие множество таблиц, могут создавать заметную нагрузку на операции ввода-вывода (I/O) уже на этапе планирования.
Большое значение read на этапе планирования говорит о том, что либо системные каталоги не находятся в кеше (чаще всего при «холодном» старте), либо запрос затрагивает слишком много таблиц или колонок.
Если время планирования становится проблемой, стоит убедиться, что системные каталоги остаются «прогретыми» в кеше. В системах с большим количеством партиций (секций) накладные расходы на планирование могут быть значительными — это одна из причин, почему важно отсечение партиций.
Размытая граница между буферами планирования и выполнения
Во время написания статей у меня часто возникают сомнения, всё ли изложено корректно — с такой сложной системой, как PostgreSQL, постоянно приходится учиться. Недавно я лучше разобрался с буферами планирования, пересмотрев свои не до конца точные предположения.
На практике PostgreSQL не разрешает все метаданные на этапе планирования. Планировщик выполняет только минимально необходимую работу, чтобы выбрать оптимальный план, а часть обращений к каталогам откладывает до этапа выполнения. Например, при первом запуске узла Sort происходит обращение к функции сравнения из pg_amproc через get_opfamily_proc(). Это обращение затрагивает shared buffers и учитывается уже как буферы выполнения. При повторном запуске в рамках той же сессии информация уже находится в системном кеше, поэтому обращения к буферам не происходит и в статистике отражается меньше операций.
Собираем всё вместе
Вот пример вывода запроса, в котором проблемы видны сразу по всем категориям буферов:
Buffers: shared hit=50 read=15000 written=847 temp read=2500 written=2500 Planning: Buffers: shared hit=12 read=156 Planning Time: 45.678 ms Execution Time: 12345.678 ms
Если читать сверху вниз, картина такая: hit ratio крайне низкий (50 попаданий против 15 000 чтений), значит рабочий набор данных не находится в кеше. written=847 означает, что запрос вызвал синхронное вытеснение страниц — процесс фоновой записи не успевает справляться. Сброс во временные файлы указывает на операцию, вышедшую за пределы work_mem. Даже на этапе планирования понадобилось 156 чтений, что говорит о том, что системные каталоги были вытеснены из кеша.
Каждое из этих чисел указывает на конкретный рычаг настройки: shared_buffers, bgwriter_lru_maxpages, work_mem или оптимизацию запроса, чтобы он затрагивал меньше данных.
Смотрим шире, чем на отдельные запросы
Анализ отдельного запроса полезен, но ещё важнее закономерности во всей вашей нагрузке. pg_stat_statements показывает те же счётчики буферов, но уже в накопленном виде за время работы:
SELECT substring(query, 1, 60) AS query, calls, shared_blks_hit, shared_blks_read, round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct, temp_blks_writtenFROM pg_stat_statementsWHERE calls > 100ORDER BY shared_blks_read DESCLIMIT 10;
Этот запрос показывает, какие именно запросы создают больше всего чтений с диска в вашей системе — зачастую это полезнее, чем разбирать запросы по одному.
Подведем итоги
Статистика буферов превращает EXPLAIN из «вот план выполнения» в «вот куда на самом деле ушло время». За каждым числом стоит конкретная причина и конкретный способ исправления. Как только вы научитесь это читать, вы перестанете гадать и начнёте осознанно настраивать систему.
Если хочется лучше понимать, что происходит внутри запросов и за счёт чего они замедляются или ускоряются, можно пойти чуть дальше практики из статьи. Приглашаем на бесплатные уроки**, где будем разбирать работу с SQL и PostgreSQL на более прикладных примерах. **Уроки проводят практикующие эксперты, преподаватели курсов Otus.
-
21 апреля 20:00. «Функции в SQL: вычисления и переиспользование кода». Записаться
-
5 мая 20:00. «Postgres + JSON: реляционная мощь, документная гибкость». Записаться
Немного практики в тему — пройдите вступительный тест по PostgreSQL и узнаете, есть ли пробелы в знаниях.
ссылка на оригинал статьи https://habr.com/ru/articles/1024674/