«Но ведь запрос был быстрым на dev и staging!»

Какие признаки говорят о том, что запрос работает быстро локально, но в production потребует отдельного внимания к производительности?

Есть две точки, после которых простой запрос начинает заметно замедляться:

Сортировка перестаёт помещаться в память.
Сканирование таблицы начинает читать данные с диска.

Примеры ниже показаны на небольших стендах с ограниченными ресурсами. В крупных системах картина будет такой же, только с гораздо большим количеством строк.
Простой запрос:

SELECT user_id, event_type, created_at
FROM events
WHERE user_id = 1
ORDER BY created_at DESC

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

(Небольшая оговорка: если для пользователя нет ни одной строки, PostgreSQL может решить вообще не сканировать таблицу, используя статистику по таблице и колонкам.)

Фаза 1. Всё помещается в память

Таблица содержит 10 000 строк.
Для user_id = 1 найдено 5 000 событий.
work_mem = 1MB.

Что видно ((фото1):

Sort Method: quicksort Memory: 427kB
Все 5 000 строк были отсортированы прямо в RAM.
Rows Removed by Filter: 5000
PostgreSQL всё равно прочитал остальные 5 000 строк и отбросил их.
Buffers: shared hit=74
Все страницы уже находились в памяти (shared_buffers).

Фаза 2. Сортировка начинает писать на диск

Теперь в таблице 200 000 строк.
У пользователя уже 100 000 событий.
work_mem всё ещё равен 1 MB.

Что изменилось (2):

Sort Method: external merge Disk: 3352kB
Сортировка больше не помещается в память и начинает использовать временные файлы.
temp read=836 written=843
PostgreSQL записал 843 временные страницы на диск и затем прочитал их обратно во время merge-фазы.
Rows Removed by Filter: 100000
Ещё 100 тысяч строк были прочитаны только для того, чтобы потом их выбросить.

Фаза 3. Таблица перестаёт помещаться в буферный кеш

Всего уже 600 000 строк.
Из них 100 000 принадлежат нужному пользователю, остальные 500 000 — другим пользователям.
PostgreSQL всё равно вынужден читать их.
Размер таблицы становится больше shared_buffers.

(3 фото)

Планировщик запустил 2 параллельных воркера.
Сканирование и сортировка были распределены между 3 процессами, поэтому каждый сортировал примерно по 33 000 строк вместо 100 000.

Но основные проблемы остались:

shared read=3049
Таблица перестала помещаться в буферный кеш. Более 3 000 страниц пришлось читать с диска.
Rows Removed by Filter: 166667 × 3
Было прочитано и выброшено около 500 тысяч строк.
Все три сортировки всё равно использовали external merge
Параллелизм уменьшил объём работы на каждый процесс, но не убрал запись на диск.

Решение зависит от конкретного приложения.
Самый очевидный вариант:

CREATE INDEX idx_events_user_created_at
ON events (user_id, created_at DESC);

Такой индекс позволяет резко сократить объём сортировки и избежать полного сканирования таблицы.
Но индекс далеко не единственный вариант.
В зависимости от нагрузки могут помочь:
- кэширование на уровне приложения;
- materialized views;
- партиционирование таблиц;
- изменение модели хранения данных.

Главный вывод простой:

Если в EXPLAIN ANALYZE появляются:
external merge
temp read / temp written
большие значения Rows Removed by Filter
shared read

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

👉 @SQLPortal