Ускорение запросов в PostgreSQL: три рычага оптимизации и практический разбор

В предыдущих частях серии мы разобрали, как читать планы выполнения через EXPLAIN ANALYZE, и научились автоматически ловить медленные запросы с помощью pg_stat_statements, auto_explain и log_min_duration_statement. Теперь — следующий шаг: что делать с проблемами, которые вы нашли.

В этой части разбираем три рычага оптимизации: статистику планировщика, индексы и рефакторинг SQL-запросов. На демонстрационном примере покажем, как снизить стоимость запроса почти вдвое — без изменений в инфраструктуре.

Оборудование, конфигурация сервера и схема БД тоже влияют на производительность, но остаются за рамками статьи — здесь сосредоточимся на том, что можно улучшить на уровне запросов.

Диагностика через системные представления и обновление статистики

Из прошлого гайда мы узнали, что планировщик PostgreSQL опирается на статистические данные при выборе плана выполнения запроса. В большинстве случаев планировщик работает хорошо. За актуальность статистики отвечает фоновый процесс Autovacuum: он удаляет устаревшие записи и обновляет данные, на которые опирается планировщик. Однако в некоторых случаях выбранный план может быть далек от оптимального. Если статистика устарела или неполная, выбор плана может быть ошибочным, и запрос может выполняться долго. Поэтому отключать Autovacuum не рекомендуется — без него статистика обновляется реже, и качество планов может заметно снижаться.

Работа со статистикой помогает лучше понять логику планировщика. Что такое статистика в СУБД? Это данные, которые собирает PostgreSQL, такие как: количество строк в таблице, распределение значений, средняя длина строк, selectivity и другие. Эта информация необходима планировщику запросов для расчета стоимости запроса и выбора оптимального плана выполнения.

Selectivity — доля строк таблицы, которую вернёт условие фильтрации (значение от 0 до 1). Например, если из 1000 строк условие отбирает 100, то селективность = 0,1 (10%). Высокая селективность — малая доля строк, низкая — большая (показатель близок к 1). Подробнее — в прошлой статье.

Хорошая новость: эти данные не скрыты внутри системы. PostgreSQL предоставляет прямой доступ к ним через набор специальных системных представлений и каталогов. Например: pg_class, pg_stats, pg_stat_user_tables и другие представления группы pg_stat_. Изучив их, вы сможете самостоятельно оценить, насколько точно база данных «понимает» ваши данные, и вовремя заметить проблемы. Это далеко не полный список — больше сведений в документации PostgreSQL.

Статистика для планировщика (pg_class, pg_stats)

pg_class — системный каталог с метаинформацией о физической структуре объектов. 

Ключевые метрики:

• reltuples — оценочное количество строк в таблице, 

• relpages — оценочный размер таблицы в страницах, 

• relallvisible — количество страниц, полностью видимых (отмеченных в карте видимости).

Пример запроса с использованием pg_class:

SELECTrelname AS table_name,relkind AS type,reltuples AS estimated_rows,relpages AS estimated_pages,relallvisible AS visible_pagesFROM pg_classWHERE relname = 'yourtablename' AND relkind = 'r';

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

В нашем примере для таблицы test значение ‘r’ означает regular table (обычную таблицу). Для представлений или индексов обозначение будет другим. В таблице приблизительно 8 строк и одна страница размером 8 КБ (для стандартной сборки).

Если вы уверены, что в таблице есть данные, но значения reltuples и relpages равны 0, значит, статистика устарела. В этом случае стоит выполнить команды:

ANALYZE your_table;VACUUM;

Статистика обновляется не только вручную, но и автоматически: механизм autovacuum запускает команды при достижении соответствующих порогов изменения данных. Если autovacuum отключён или работает с ограничениями, значения могут долго оставаться неточными.

После обновления планировщик сможет точнее рассчитывать стоимость запросов (cost). Важно помнить: reltuples — это оценка на момент последнего ANALYZE или VACUUM. 

На что обратить внимание:

• Если reltuples велико (десятки-сотни тысяч строк и более), стоимость полного сканирования возрастает, и планировщик скорее выберет индекс (о них — в следующем разделе). Это сигнал проверить наличие индексов для часто используемых фильтров.

• Если значение relallvisible близко к relpages (то есть почти все страницы помечены как видимые), PostgreSQL может выполнять запросы, обращаясь только к индексу, не заглядывая в основную таблицу (heap), что значительно ускоряет чтение. Подробно о том, как это работает, мы поговорим позже, когда будем разбирать индексы и механизм Heap Fetch.

pg_stats — системное представление со статистикой распределения значений в столбцах. 

Ключевые метрики:

• доля NULL‑значений в столбце (null_frac);

• оценку количества уникальных значений (n_distinct);

• наиболее часто встречающиеся значения и их частоты (most_common_vals, most_common_freqs);

• гистограмма распределения значений (histogram_bounds).

Пример запроса с использованием pg_stats:

SELECTschemaname,tablename,attname AS column_name,null_frac,n_distinct,most_common_vals,most_common_freqsFROM pg_statsWHERE schemaname = 'public'    AND tablename = 'yourtablename';

Допустим, вы добавили или удалили много строк, либо в столбцах изменилось распределение данных — например, выросла доля NULL. pg_stats хранит снимок статистики, на основе которого планировщик выбирает план выполнения..

Посмотрите на значения в pg_stats. Если они не соответствуют тому, что сейчас реально лежит в таблице — например, в таблице появились NULL, а null_frac всё ещё равен 0, или количество уникальных значений изменилось, а n_distinct осталось прежним — это повод обновить статистику командой ANALYZE.

В нашем примере для таблицы customers видно, что в столбцах нет NULL‑значений, а n_distinct = -1 означает, что PostgreSQL считает значения в этих столбцах уникальными.

Статистика использования (pg_stat_user_tables)

pg_stat_user_tables собирает статистику на уровне таблиц: количество операций и показатели, по которым можно оценить актуальность статистики планировщика.

Ключевые метрики:

• n_live_tup — приблизительное количество «живых» (не удалённых) кортежей;

• last_analyze — время последнего запуска ANALYZE;

• n_tup_ins, n_tup_upd, n_tup_del — количество операций вставки, обновления и удаления с момента последнего сбора статистики.

Пример запроса с использованием pg_stat_user_tables:

SELECTrelname AS table_name,last_analyze,n_live_tupFROM pg_stat_user_tablesWHERE relname = 'yourtablename'     AND schemaname = 'public';

В примере видно: на момент последнего ANALYZE в таблице было ~8 активных строк. Если с этой даты прошло много времени или таблица активно менялась, статистику стоит обновить (ANALYZE).

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

Пример запроса с использованием pg_stat_user_tables:

SELECT schemaname, relname, idx_scan, seq_scanFROM pg_stat_user_tablesWHERE schemaname = 'public'ORDER BY seq_scan DESC;

В примере для таблицы customers idx_scan значительно превышает seq_scan — индексы работают эффективно. Для таблицы orders фиксируется только seq_scan, но это не всегда проблема: для небольших таблиц или запросов с низкой селективностью seq_scan бывает эффективнее. Если же план неоптимален, стоит проверить: нужен ли индекс, можно ли переписать запрос или достаточно обновить статистику (ANALYZE).

Следует учитывать, что счётчики seq_scan и idx_scan относятся к статистике использования таблиц. Они накапливаются с момента запуска сервера или последнего сброса статистики через pg_stat_reset, поэтому интерпретировать их нужно с учётом периода, за который они собраны. Они не сбрасываются при ANALYZE — эта команда обновляет статистику данных (pg_statistic), но не влияет на статистику использования таблиц.

Индексы

Если обновление статистики с помощью ANALYZE  не заставило планировщик выбрать эффективный план, значит, проблема глубже. Скорее всего, базе данных просто нечем воспользоваться для ускорения выборки. На этом этапе будем переходить от обслуживания текущей структуры к созданию новой — индексов.

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

• Без индекса: поиск информации подобен последовательному чтению каждой страницы книги (аналогично Seq Scan в PostgreSQL).

• С индексом: вы можете быстро найти нужные страницы, используя указатель (аналогично Index Scan в PostgreSQL).

Цена ускорения: Индексы не бесплатны.

1. Место на диске: Индекс — это дополнительные данные, которые занимают место (иногда сравнимое с размером самой таблицы).

2. Замедление записи: При каждом INSERT, UPDATE или DELETE база данных должна не только изменить таблицу, но и обновить все связанные индексы.

Именно поэтому создание индексов «на всякий случай» — плохая стратегия. Решение должно быть обосновано данными, полученными на этапе диагностики, через проверку статистики.

PostgreSQL поддерживает множество типов индексов, но для большинства задач достаточно знать два основных:

• B—Tree: Индекс по умолчанию. Идеально подходит для операций сравнения (=, >, <), сортировки (ORDER BY) и поиска по точному значению. Пример команды создания B—Tree индекса:

CREATE INDEX idx_email ON users (email);

GiN (Generalized Inverted Index):Специализированный индекс для составных значений. Необходим для эффективной работы с JSONB (поиск по ключам внутри JSON), полнотекстовым поиском (tsvector) и массивами. Так как попытка использовать B-Tree для поиска внутри JSON-документа приведет к тому же медленному Seq Scan. Пример команды создания GiN индекса:

CREATE INDEX idx_content ON docs USING GIN (content);

JSON (JavaScript Object Notation) — легкий текстовый формат для хранения и обмена данными. Состоит из пар «ключ: значение» и массивов.

Для B‑Tree также доступен модификатор INCLUDE, который позволяет создавать покрывающие индексы. В таком индексе часть колонок используется для поиска, а дополнительные колонки просто хранятся в индексе. Это важно, потому что влияет на то, как именно PostgreSQL выполняет индексное сканирование.

Обычное индексное сканирование (Index Scan) работает в два этапа:

1. Находит подходящие строки в индексе по условиям фильтрации (WHERE).

2. Если в запросе (SELECT) есть колонки, которых нет в индексе, PostgreSQL обращается к таблице (heap), чтобы дозагрузить эти данные. Такое обращение называется Heap Fetch.

Каждое такое обращение к таблице часто требует случайного чтения с диска (Random I/O), что значительно медленнее последовательного чтения индекса. Чем больше строк нужно обработать, тем ощутимее падает производительность.

Чтобы избежать лишних обращений к таблице, используется конструкция INCLUDE. Она позволяет добавить в индекс колонки, которые не участвуют в поиске (не нужны в условиях фильтрации), но требуются для вывода результатов (в ‘SELECT’). Благодаря этому планировщик может выполнить операцию Index Only Scan: все необходимые данные уже находятся внутри индекса, и дорогостоящее чтение из таблицы (Heap Fetch) становится не нужно.

Пример: Представим, что у нас создан обычный индекс на поле фильтрации:

CREATE INDEX ON users (status);Однако в запросе также обращаемся к другим полям таблицы:

Однако в запросе также обращаемся к другим полям таблицы:

SELECT name, email FROM users WHERE status = 'active';

Индекс по ‘status’ помогает найти нужные строки, но данные для ‘name’ и ’email’ в нём не хранятся. Базе данных приходится выполнять дополнительные обращения к таблице (Heap Fetch). В подобных ситуациях лучше использовать покрывающий индекс и включить другие поля через оператор INCLUDE:

CREATE INDEX ON users (status) INCLUDE (name, email);

Все нужные данные теперь хранятся в структуре индекса. Планировщик сможет выполнить Index Only Scan — получить информацию прямо из индекса, не обращаясь к таблице. Это критически ускоряет выборку на больших объемах данных.

Используйте INCLUDE для колонок, которые часто нужны в результате (SELECT), но не участвуют в поиске (WHERE). Это один из самых эффективных способов снизить нагрузку на подсистему ввода-вывода. 

Рефакторинг SQL-запроса

Даже идеально спроектированные индексы могут оказаться бесполезными, если SQL‑запрос написан так, что планировщик не может воспользоваться этими индексами. Иногда проблема не в отсутствии индекса, а в том, как сформулированы условия выборки. В этом разделе разберём две самые частые ошибки, которые «ломают» индексы.

Функции в условии WHERE

Если вы применяете функцию к колонке в запросе, PostgreSQL вынужден вычислить эту функцию для каждой строки таблицы, прежде чем сравнить результат. Это превращает эффективный поиск по индексу в полное сканирование таблицы.

Например:

SELECT id, name, email FROM usersWHERE LOWER(email) = 'test@example.com';

Решение: Перенести функцию на сторону сравнения (если возможно) или создать функциональный индекс:

Вариант 1. Переместить функцию на правую сторону (работает для точных совпадений):

SELECT id, name, email FROM usersWHERE email = LOWER('test@example.com');

Вариант 2. Создать функциональный индекс (универсальное решение):

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Неявное приведение типов: когда PostgreSQL вынужден игнорировать индекс

PostgreSQL известен своей гибкостью в работе с типами данных, но эта гибкость может дорого стоить производительности. Если тип данных в запросе не совпадает с типом колонки в базе, база данных попытается привести их к общему знаменателю. Приоритеты определяются системными каталогами и контекстом выражения. Например, колонка phone имеет тип VARCHAR и соответствующий индекс, но мы сравниваем её с числом (забыли кавычки):

SELECT id, name FROM usersWHERE phone = 79991234567;

PostgreSQL анализирует литерал 79991234567 и пытается определить его тип. Он не заключён в кавычки, значит, это числовой тип (numeric/bigint). Поскольку типы varchar и, например, bigint несовместимы, PostgreSQL приводит колонку: CAST(phone AS bigint). Такое выражение не может использовать индекс по ‘phone’, и запрос выполняется через последовательное сканирование (seq scan).

Решение: передавайте значение в типе колонки —  например, строку в кавычках или параметр для varchar.

Отдельного внимания заслуживает ORM‑ловушка. Бывает, что проблема кроется не в SQL, который вы написали вручную, а в том, как настроена модель данных. Если в модели приложения поле объявлено как long, BigInteger или другой числовой тип, а в базе колонка — varchar, драйвер передаёт параметр с типом BIGINT.

Запрос от ORM: WHERE phone = $1Тип параметра $1: 79991234567 (тип: BIGINT передан драйвером)Ожидание базы: WHERE phone = '79991234567' (тип varchar)

PostgreSQL фиксирует тип параметра, переданный драйвером, и согласно алгоритму разрешения типов при сравнении varchar = bigint выбирает приведение колонки к типу параметра CAST(phone AS bigint). Такое приведение выполняется для каждой строки и приводит к лишним вычислениям. Обычный B-tree индекс по phone перестаёт работать, так как он построен по значениям колонки, а не по результату функции. Увидеть этот момент возможно через EXPLAIN ANALYZE. В плане запроса это было бы видно так:

Filter: (phone = ($1)::bigint) 

Вместо ожидаемого:

Index Cond: (phone = $1)

Решение: синхронизируйте типы в модели приложения и схеме БД. Если изменить модель нельзя, приводите параметр явно в запросе.

Помимо двух вышеописанных, существует ряд других распространенных антипаттернов, негативно влияющих на производительность базы данных:

• Арифметические операции над колонками (например: WHERE salary * 1.1 > 1000) приводят к тому, что индекс по salary не используется.

• Неоптимальное использование LIKE (поиск по шаблону %value% вместо value%). Лишает СУБД возможности использовать индекс.

• Конструкции OR между разными колонками (часто лучше заменить на UNION).

• Избыточные JOIN или подключение таблиц, данные из которых не используются. Утяжеление плана выполнения.

• Фильтрация в HAVING вместо WHERE (когда это возможно).

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

1. EXPLAIN ANALYZE: Главный инструмент. Анализируйте план выполнения: если вместо Index Scan вы видите Seq Scan (полное сканирование таблицы), проверьте условия фильтрации (WHERE). Возможно, функция в WHERE ломает индекс.

2. Плагины для IDE: Инструменты вроде SQLFluff, SonarLint или встроенные инспекции в DataGrip/IntelliJ IDEA могут подсвечивать синтаксические антипаттерны (например, SELECT или потенциальные SQL-инъекции).

*SQL-инъекции — это тип атаки на веб-приложения, при которой злоумышленник внедряет вредоносный SQL-код в запросы к базе данных, чтобы получить несанкционированный доступ к данным или изменить их.

Теория разобрана — переходим к практике. Ускорим конкретный запрос шаг за шагом: от диагностики до рефакторинга.

Пробуем оптимизировать запрос 

Все примеры ниже — учебные. В реальности на скорость влияют также оборудование, настройки СУБД, актуальность статистики и другие факторы, но здесь мы сосредоточимся на структуре запроса.

Эксперименты проведём на тестовой базе из двух таблиц: customers (100 000 записей) и orders (1 000 000 записей) со стандартным набором полей для бизнес-систем.

Представим, что pg_stat_statements выявил аналитический запрос, занимающий верхние строчки по времени выполнения и нагрузке. Запрос возвращает топ-10 покупателей за июнь 2025 года: имя, количество заказов и сумму покупок. Сам запрос выглядит так:

SELECT    c.name AS customer_name,    COUNT(o.id) AS total_orders,     COALESCE(SUM(o.total_amount), 0) AS total_spent    FROM customers cJOIN orders o ON c.id = o.customer_idwhere EXTRACT(month from o."date")=6 AND EXTRACT(year from o."date") = 2025GROUP BY c.nameORDER BY total_spent DESCLIMIT 10;

В результате выполнения запрос возвращает топ-10 покупателей за июнь 2025 года.

Теперь с помощью EXPLAIN ANALYZE посмотрим на показатели данного запроса.

Общая стоимость запроса находится в первой строчке, второе значение — cost 21140,82. Сразу заметна «тяжёлая» операция seq scan on orders с «cost» 17224,33. Вспомним, что seq scan – это последовательное чтение таблицы, самый базовый алгоритм чтения данных. Отметим довольно высокую фильтрацию — Rows Removed by Filter: 491852. Планировщик отбросил 491 852 строки после применения условий — при том что в таблице всего 1 000 000 строк.. Но в данном случае это ожидаемо, так как фильтрация происходит полным сканированием таблицы (seq scan). Время на планирование составило 0,240 мс, а время на выполнение 214,958 мс. Пока наш запрос не выглядит быстрым и эффективным. Чтобы исправить ситуацию, будем двигаться от простых шагов к более затратным: сначала улучшим сам запрос, а уже потом, при необходимости, будем менять структуру данных. Начнём с рефакторинга SQL-запроса. Это самый безопасный шаг оптимизации: он не требует изменений в схеме БД, не создаёт рисков для продакшена и часто даёт результат за несколько минут.

Решение А: заменяем EXTRACT на диапазон дат (умеренное улучшение) 

В запросе используется функция EXTRACT в условии WHERE. Как мы обсуждали выше, функция в WHERE заставляет PostgreSQL вычислять её для каждой строки, что блокирует использование индекса. Попробуем заменить функцию на явное указание диапазона дат. Наш измененный запрос будет выглядеть так:

SELECTc.name AS customer_name,COUNT(*) AS total_orders,COALESCE(SUM(o.total_amount), 0) AS total_spentFROM customers cJOIN orders o ON o.customer_id = c.idWHERE o.date >= DATE '2025-06-01'  AND o.date < DATE '2025-07-01'GROUP BY c.nameORDER BY total_spent DESCLIMIT 10;

Выполним для этого запроса EXPLAIN ANALYZE.

Стоимость запроса немного снизилась до 19275,90. Время на планирование составило 0,216 мс, а время на выполнение — 157,538 мс. Видим изменения в лучшую сторону по сравнению с исходным запросом. Подтверждается предположение, что сравнение диапазонов дешевле для планировщика, чем вызов функции EXTRACT для каждой строки. Продолжим оптимизацию.

Решение Б: предварительно фильтруем большую таблицу в подзапросе (минимальный эффект — оптимизатор делает это сам)

Если ещё раз внимательно посмотрим на размеры таблиц, то в нашем случае таблица orders значительно больше таблицы customers. Существует распространённая техника оптимизации: если большую таблицу предварительно отфильтровать в подзапросе, а затем соединить, это может ускорить выполнение. Хотя оптимизатор PostgreSQL достаточно умен, чтобы самостоятельно перестраивать запросы, явное указание приоритета фильтрации через подзапрос может помочь ему понять, что нам нужны только данные за июнь перед тем, как соединять их с таблицей клиентов. Теоретически это должно уменьшить объём данных для операции соединения (JOIN) и снизить нагрузку на память. Давайте проверим. Наш измененный запрос будет выглядеть так:

SELECTc.name AS customer_name,COUNT(*) AS total_orders,SUM(o.total_amount) AS total_spentFROM customers cJOIN (SELECT customer_id, total_amountFROM ordersWHERE date >= DATE '2025-06-01'  AND date < DATE '2025-07-01') o ON o.customer_id = c.idGROUP BY c.nameORDER BY total_spent DESCLIMIT 10;

Выполним для этого запроса EXPLAIN ANALYZE.

​​Общая стоимость запроса осталась прежней — 19275,90. Время выполнения составило 162,695 мс. Изменения незначительны, и это может быть в пределах погрешности. Почему так? План выполнения остался идентичным, поэтому изначальная гипотеза не подтвердилась. Оптимизатор PostgreSQL автоматически объединил подзапрос с основным запросом, применив фильтр напрямую к таблице. Такой подход действительно может давать выигрыш, но только в ситуациях, когда оптимизатор не может безопасно перестроить запрос сам — например, при очень сложных условиях фильтрации, нестандартных функциях или ограничениях старых версий СУБД. Двигаемся дальше.

Решение В: добавляем составной индекс (значительное сокращение времени выполнения)

Рефакторинг себя исчерпал — план почти не изменился. Значит, базе нечем ускорить выборку. Пора создавать индекс. В запросе есть тяжёлая операция — Seq Scan on orders, которую нужно заменить на более эффективный метод доступа. Поэтому, учитывая специфику нашего запроса (фильтрация по диапазону дат и соединение по customer_id), создадим составной индекс:

CREATE INDEX idx_orders_date_customer ON orders(date, customer_id);

Разберём логику создания индекса. Исходя из принципа работы B—дерева в PostgreSQL, индекс работает по правилу левого префикса. Это значит, что база данных может эффективно использовать индекс, только если условия в запросе соответствуют последовательности колонок в индексе, начиная с первой.

Если первым столбцом индекса будет date, PostgreSQL быстро найдет начало диапазона и просканирует только нужные строки. Поэтому в нашем случае первым столбцом должна идти дата. Также нет смысла создавать отдельный индекс на колонку date, если у нас уже есть составной индекс, начинающийся с этой колонки. Индекс (date, customer_id) полностью покрывает потребности фильтрации по дате. Создание же дополнительного индекса только для date станет лишней тратой места на диске и приведет к замедлению вставки и обновления записей.

Посмотрим на отчет EXPLAIN ANALYZE для запроса из Решения А (так как подзапрос (Решение Б) не дал преимуществ, вернемся к более ‘чистому’ коду).

Общая стоимость снизилась до 13 692,76. Время на планирование составило 0,278 мс, а время на выполнение — 92,771 мс. Изменения существенные по сравнению с исходным запросом. Планировщик выбрал Bitmap Index Scan вместо Seq Scan, что позволило избежать чтения лишних страниц диска.

Итоги оптимизации

После серии экспериментов можно сделать несколько важных наблюдений о поведении PostgreSQL и эффективности применённых подходов.

Во‑первых, рефакторинг запроса оказался рабочим инструментом. Замена функции EXTRACT на явный диапазон дат (Решение А) дала умеренное, но ощутимое улучшение и показала, что даже небольшие изменения в формулировке запроса могут положительно влиять на план выполнения.

Во‑вторых, попытка предварительно отфильтровать большую таблицу в подзапросе (Решение Б) продемонстрировала, что планировщик PostgreSQL действительно достаточно «умён», чтобы самостоятельно перестраивать выражения и выбирать эквивалентные, но более оптимальные формы. В результате эффект оказался минимальным и практически не повлиял на время выполнения.

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

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

Результаты EXPLAIN ANALYZE могут отличаться от запуска к запуску, поэтому для объективной оценки проводите несколько измерений (3–5 раз подряд; ориентируйтесь на медиану, добавьте BUFFERS, чтобы видеть работу кэша и отличить тёплый/холодный запуск).

В целом практика работы с EXPLAIN ANALYZE показала, что оптимизация запроса — это итерационный процесс. Иногда достаточно переписать выражение, иногда — понять, что планировщик уже делает всё возможное, а иногда — добавить недостающий индекс. Регулярный анализ планов выполнения помогает быстрее находить узкие места и принимать обоснованные решения.

Практические рекомендации по оптимизации работы с PostgreSQL

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

Уровень запросов

• Минимизируйте распространённые антипаттерны в SQL‑запросах (SELECT *, лишние JOIN, арифметика в условиях, неоптимальный LIKE и др.). Такие конструкции создают лишнюю нагрузку и мешают эффективному использованию индексов.

• Настройте автоматический мониторинг тяжёлых запросов (pg_stat_statements, auto_explain, log_min_duration_statement) и анализируйте их через EXPLAIN ANALYZE.

• Пробуйте рефакторинг запроса. В нашем примере замена EXTRACT на диапазон дат снизила стоимость выполнения без единого индекса.

Индексы и обслуживание БД

• Используйте индексы там, где это возможно. В нашем кейсе один составной индекс изменил метод доступа и сократил время выполнения более чем вдвое.

• Не отключайте стандартные службы Postgres, например, Autovacuum. Это сделает статистику неактуальной, и планировщик не сможет подобрать оптимальный план выполнения запроса.

Архитектура и инфраструктура

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

• Не размещайте на одном сервере совместно с БД другие высоконагруженные приложения, например, Django, Redis, Nginx. Это создаёт конкуренцию за CPU, память и диск. База данных должна работать на подходящем оборудовании, соответствующем уровню нагрузки.

• Следите за состоянием дисков и учитывайте тип хранилища. Разные носители имеют свои особенности: например, SSD дают больший прирост производительности для таблиц, чем для индексов. Со временем любые накопители изнашиваются: HDD теряют скорость из‑за механического износа, а SSD — из‑за ограниченного ресурса ячеек. Регулярный мониторинг помогает заранее заметить деградацию и избежать проблем с производительностью.

Процессы и экспертиза

• В сложных ситуациях привлекайте DBA-инженеров. Изучайте дополнительные материалы по работе с СУБД — если время выполнения запроса критично, то время — деньги.

Выводы. Что делать дальше?

Мы прошли путь от теории к практике: научились находить медленные запросы, читать планы выполнения и ускорять SQL через рефакторинг и индексы. Завершаем цикл сводом рекомендаций.

Эта серия статей (1, 2) — лишь отправная точка в теме оптимизации PostgreSQL. Теперь, столкнувшись с медленным запросом, вы сможете провести диагностику, найти узкое место и принять обоснованное решение — переписать запрос, обновить статистику или добавить индекс.

И ещё один важный вопрос: а стоит ли вообще тратить время на оптимизацию? Если запрос работает медленно, но не влияет на критичную бизнес-логику, иногда проще обновить оборудование или масштабировать инфраструктуру.

За рамками статьи остались: тюнинг конфигурации PostgreSQL, модификация структуры БД, управление пулом соединений, антипаттерны в работе с запросами и другие темы. Какие темы разобрать подробнее? Пишите в комментариях — выберем самую популярную для следующей статьи.

Для углубления в тему — полезные ссылки:
— Документация PostgreSQL
— Оптимизация запросов в PostgreSQL : Домбровская Г. Р., Новиков Борис
— Материалы и книги с ресурса postgrespro.ru
— explain.dalibo.com и explain.depesz.com для визуализации плана запросов
— База знаний firstvds.ru

Автор текста Макаренков Вячеслав

---

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

-15% на заказ любого VDS (кроме тарифа Прогрев) — HABRFIRSTVDS