В статье о буферах в 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 ' || i
FROM 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.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE 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 AS
SELECT o.id, o.amount, o.status, o.created_at, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 200;

EXPLAIN (ANALYZE, BUFFERS)
SELECT status, count(*), sum(amount)
FROM temp_large_orders
GROUP 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.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER 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_written
FROM pg_stat_statements
WHERE calls > 100
ORDER BY shared_blks_read DESC
LIMIT 10;

Этот запрос показывает, какие именно запросы создают больше всего чтений с диска в вашей системе — зачастую это полезнее, чем разбирать запросы по одному.

Подведем итоги

Статистика буферов превращает EXPLAIN из «вот план выполнения» в «вот куда на самом деле ушло время». За каждым числом стоит конкретная причина и конкретный способ исправления. Как только вы научитесь это читать, вы перестанете гадать и начнёте осознанно настраивать систему.

Если хочется лучше понимать, что происходит внутри запросов и за счёт чего они замедляются или ускоряются, можно пойти чуть дальше практики из статьи. Приглашаем на бесплатные уроки**, где будем разбирать работу с SQL и PostgreSQL на более прикладных примерах. ^{**Уроки проводят практикующие эксперты, преподаватели курсов Otus.}

• 21 апреля 20:00. «Функции в SQL: вычисления и переиспользование кода». Записаться

• 5 мая 20:00. «Postgres + JSON: реляционная мощь, документная гибкость». Записаться

Немного практики в тему — пройдите вступительный тест по PostgreSQL и узнаете, есть ли пробелы в знаниях.