Любой запрос начинается с плана, и медленный запрос обычно означает, что план оказался неудачным. Чаще всего виновата устаревшая статистика.

Но как все это работает на самом деле?

PostgreSQL не выполняет запрос, чтобы выяснить, насколько он дорог, — он оценивает его стоимость. Планировщик читает заранее собранные данные из pg_class и pg_statistic, а затем рассчитывает путь к данным с наименьшей стоимостью.

В идеальном случае эти данные точны, и вы получаете тот план, на который рассчитывали. Но если статистика устарела, все быстро идет наперекосяк. Планировщик оценивает результат в 500 строк, выбирает Nested Loop, а на деле получает 25 000. План, который казался оптимальным, запускает цепную реакцию проблем.

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

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

---

Пример схемы

Для демонстрации будем использовать ту же схему, что и в статье «Статистика буферов в выводе EXPLAIN».

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;

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
FROM generate_series(1, 100000);

ANALYZE customers;
ANALYZE orders;

Какие данные читает планировщик

Все решения планировщика основаны на двух источниках:

• метаданных на уровне таблицы из pg_class;

• статистике по столбцам из pg_statistic.

pg_class — статистика на уровне отношения

На самом деле, pg_class хранит сведения обо всех отношениях. Это не только таблицы и индексы, но также секции, TOAST‑таблицы, последовательности, составные типы и представления.

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

Для нашей тестовой таблицы это выглядит так:

SELECT relname, relpages, reltuples, relallvisible
FROM pg_class
WHERE relname = 'orders';

relname | relpages | reltuples | relallvisible
--------+----------+-----------+--------------
 orders |      856 |    100000 |          856

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

Планировщик видит 100 000 строк, распределенных по 856 страницам. С этих двух чисел начинается любая оценка стоимости. relpages влияет на стоимость последовательного сканирования: каждая страница считается одной единицей работы ввода‑вывода в соответствии с параметром seq_page_cost. reltuples используется при оценке соединений, агрегаций и почти всего остального.

Значение reltuples — лишь оценка, а не актуальный счетчик строк. Его обновляют ANALYZE и autovacuum, но отдельные INSERT или DELETE этого не делают. Между запусками ANALYZE PostgreSQL пропорционально масштабирует reltuples, если меняется relpages: если число страниц таблицы выросло на 10%, планировщик предполагает, что строк тоже стало на 10% больше.

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

Еще один упомянутый выше столбец важен для некоторых конкретных операций. relallvisible показывает планировщику, какую часть таблицы можно прочитать с помощью сканирования только по индексу (index‑only scan). Иными словами, Index Only Scan может вернуть результат, используя только индекс и не обращаясь к куче (heap) для проверки видимости версий строк.

pg_statistic (через pg_stats) — статистика по столбцам

Знать размер таблицы — лишь половина дела. Чтобы оценить, сколько строк может удовлетворить условию, планировщику нужно понимать распределение данных внутри каждого столбца. PostgreSQL хранит такую статистику в системном каталоге pg_statistic, с которым напрямую вам, скорее всего, работать никогда не придется. На практике обычно используют представление pg_stats, где те же данные представлены в более удобном виде.

Самые интересные значения в нем такие:

SELECT attname, null_frac, avg_width, n_distinct,
       most_common_vals, most_common_freqs, histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-[ RECORD 1 ]-----+--------------------------------------
attname           | status
null_frac         | 0
avg_width         | 8
n_distinct        | 4
most_common_vals  | {pending,shipped,delivered,cancelled}
most_common_freqs | {0.25396666,0.25,0.24973333,0.2463}
histogram_bounds  |
correlation       | 0.2524199

Из этих данных планировщик понимает, что в столбце ровно четыре различных значения, распределенных примерно поровну, а NULL нет вообще. Если написать условие WHERE status = 'pending', он оценит, что ему подойдут примерно 25% строк. И все это без выполнения самого запроса — достаточно прочитать одну строку системного каталога.

С колонкой note ситуация другая.

SELECT attname, null_frac, avg_width, n_distinct,
       array_length(most_common_vals, 1) AS mcv_count,
       array_length(histogram_bounds, 1) AS histogram_buckets
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'note';

-[ RECORD 1 ]-----+-------
attname           | note
null_frac         | 0.6982
avg_width         | 32
n_distinct        | 1
mcv_count         | 1
histogram_buckets |

В этом столбце около 70% значений равны NULL, а различное ненулевое значение всего одно. Поэтому для условия WHERE note IS NOT NULL планировщик оценит выборку примерно в 30% строк.

Селективность в действии

Теперь, когда мы разобрались, какие данные есть у планировщика, посмотрим, как он использует их, чтобы оценить, сколько строк прочитает та или иная часть запроса. Эта «оценка» называется селективностью. Она задается числом с плавающей точкой от 0 до 1.

Формула довольно простая:

Ожидаемое число строк = Общее число строк * Селективность

А вот способ расчета селективности полностью зависит от используемого оператора: =, <, > или LIKE.

Равенство и наиболее частые значения

Проще всего начать с равенства. Для условия WHERE status = 'shipped' планировщик сначала проверяет список most_common_vals — наиболее частые значения (MCV). Если нужное значение там есть, селективность берется из соответствующего элемента most_common_freqs.

Если значения в списке нет, планировщик считает, что оно относится к оставшейся части распределения. Он вычитает сумму частот всех MCV из 1.0, а остаток делит на число остальных различных значений.

(1.0 - (SELECT sum(s) FROM unnest(most_common_freqs) s))
 /
(n_distinct - array_length(most_common_vals, 1))

Поиск по диапазону и гистограмма

Все было бы просто, если бы нас интересовали только точные значения. Но гораздо чаще приходится работать с диапазонами. Например, с условием WHERE amount > 400.

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

Селективность в этом случае определяется тем, сколько интервалов охватывает условие запроса. Например, если границы гистограммы имеют вид (100, 200, 300, 400, 500, 600), планировщик определит, что условие охватывает два полных интервала. Всего интервалов пять, поэтому селективность составит 0.4 (2/5).

Откуда взялись 2 и 5? Массив histogram_bounds задает границы между интервалами. При границах (100, 200, 300, 400, 500, 600) получается пять интервалов:

(100-200), (200-300), (300-400), (400-500), (500-600)

Та же логика применяется и при проверке условия. amount > 400 охватывает интервалы (400-500) и (500-600), то есть два из пяти.

Главный недостаток гистограммы — линейная интерполяция. Если в распределении данных есть резкие пики, планировщик все равно будет исходить из предположения о равномерном распределении внутри интервала.

Чуть сложнее становится, когда искомое значение попадает внутрь интервала. Например, для WHERE amount > 350 планировщик находит интервал, содержащий 350 — в нашем примере это (300-400), — предполагает линейное распределение данных и вычисляет долю подходящих значений внутри него.

Поиск и сопоставление с шаблоном

Для планировщика это, пожалуй, один из самых сложных случаев. При поиске подстроки вроде WHERE note LIKE '%middle%' ему не на что опереться: ни гистограмма, ни список значений здесь не помогают. Поэтому приходится использовать жестко заданные в исходном коде PostgreSQL “магические” константы.

Для произвольного шаблона по умолчанию предполагается селективность в 0,5% от общего числа строк:

#define DEFAULT_MATCH_SEL  0.005

С префиксным поиском вроде WHERE note LIKE 'boringSQL%' ситуация немного лучше: PostgreSQL может свести его к условиям по диапазону и воспользоваться границами гистограммы. Разница кажется небольшой, но на практике она может быть огромной.

Корреляция и стоимость индексного сканирования

Помните correlation из pg_stats? Она показывает, насколько физический порядок строк на диске соответствует логическому порядку значений столбца. Значение, близкое к 1.0, означает высокую корреляцию, а близкое к нулю — что данные распределены по страницам практически случайным образом.

Если вспомнить, как данные устроены внутри 8-КБ страницы, становится понятно, почему здесь важна локальность строк. От нее зависит, насколько выгодным окажется индексное сканирование. Планировщик считает произвольное чтение страницы в четыре раза дороже последовательного: random_page_cost = 4.0 против seq_page_cost = 1.0.

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

n_distinct и оценка соединений

Значение n_distinct играет важную роль при соединениях. Это еще одна причина запускать ANALYZE после массовых изменений данных.

Представим упрощенную логику оценки соединения по равенству:

На практике PostgreSQL также учитывает null_frac — значения NULL не участвуют в соединении — и MCV с обеих сторон. Если MCV есть в обеих таблицах, PostgreSQL вычисляет скалярное произведение их частот.

estimated_rows = (rows_left × rows_right) / max(n_distinct_left, n_distinct_right)

Допустим, вы соединяете две таблицы, и в обеих ключ соединения содержит примерно по 2 000 различных значений. Устаревший n_distinct может привести к серьезной ошибке в оценке числа строк, из‑за чего планировщик вообще выберет неподходящую стратегию соединения.

Вот о какой куче (heap) идет речь

Имейте в виду: все описанное выше относится к тому, как планировщик оценивает число строк при обычном сканировании heap. Для индексов, селективности соединений и сложных типов вроде JSONB действуют свои правила оценки, обработки операторов и свои особенности. Но базовые принципы оценки остаются теми же.

Что будет, если статистики нет?

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

В таких случаях PostgreSQL использует жестко заданные значения по умолчанию.

Казалось бы, достаточно быстро запустить ANALYZE, и проблема решена. Но не всегда.

Где статистики не будет

Хотя эта статья посвящена статистике и тому, как поддерживать ее в актуальном состоянии, есть ситуации, когда статистика недоступна вообще или появляется непредсказуемо. Если вам кажется, что вас это не касается, стоит подумать еще раз.

• Для CTE и подзапросов, если они не встраиваются или материализуются, статистики нет. Подробнее о том, когда именно это происходит, см. в статье Good CTE, Bad CTE.

• Временные таблицы не обрабатываются autovacuum, поэтому автоматического ANALYZE для них нет.

• Для внешних таблиц нет гарантии, что статистика будет передана.

• И, что особенно неожиданно, статистики нет для вычисляемых выражений в WHERE, например WHERE amount * 1.1 > 500 или lower(email) = 'hello@example.com', если только вы не создали индекс по выражению или расширенную статистику.

И кстати, помните про TRUNCATE: это быстрый способ удалить данные, но статистику он оставляет после себя...

Как работает ANALYZE

Как мы уже не раз упоминали, ANALYZE — единственный механизм, который заполняет pg_class и pg_statistic актуальными данными. Чтобы понимать, почему статистика иногда оказывается неточной, важно разобраться, какие данные он берет в выборку, что вычисляет и что может пропустить.

Сам процесс состоит из шести отдельных этапов, которые показывает pg_stat_progress_analyze:

1. инициализация;

2. получение строк выборки;

3. получение строк выборки из унаследованных таблиц — дочерних или секционированных;

4. вычисление статистики: MCV, гистограмм, корреляции и так далее;

5. вычисление расширенной статистики — о ней ниже;

6. завершение работы и запись в pg_statistic.

Для наших целей достаточно разобрать выборку данных, вычисление статистики и запись в pg_statistic.

Механизм выборки

На самом деле ANALYZE не читает таблицу целиком. Он берет объем данных, который считается статистически достаточным минимумом: для алгоритма резервуарной выборки этот минимум задан как 300. В PostgreSQL это означает 300 × default_statistics_target строк.

SHOW default_statistics_target;

default_statistics_target
--------------------------
100
(1 row)

При значении по умолчанию 100 получается 30 000 строк. Для нашей таблицы orders на 100 000 строк ANALYZE читает примерно 30% данных. Для таблицы на 50 миллионов строк — всего 0,06%. Этот же параметр определяет максимальный размер списка MCV и гистограммы: до 100 элементов в каждом.

При default_statistics_target = 100 не удивляйтесь, если histogram_bounds содержит 101 значение. Чтобы задать 100 интервалов, нужна 101 граница.

Выборка проходит в два этапа. Сначала ANALYZE случайным образом выбирает набор страниц, а затем читает с них все актуальные строки. Так получается репрезентативная выборка без необходимости читать каждую страницу таблицы.

Вычисление статистики

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

Сначала подсчитываются значения NULL и вычисляются null_frac и avg_width — это самая дешевая в расчете статистика. Затем ненулевые значения сортируются, и по числу повторов строится список MCV. Значения, встречающиеся достаточно часто, попадают в этот список, а остальные передаются построителю гистограммы, который разбивает их на интервалы с одинаковым числом строк. Наконец, поскольку значения уже отсортированы, ANALYZE сопоставляет их логический порядок с физическим положением версий строк — то есть с тем, с каких страниц они были прочитаны, — и вычисляет correlation.

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

Запись в pg_statistic

После обработки всех столбцов ANALYZE записывает результаты в pg_statistic — по одной строке на каждый столбец. Если строка для этого столбца уже существует, она обновляется на месте. Это обычное обновление heap, поэтому старая версия строки становится мертвой. Для таблиц с большим числом столбцов или при частых запусках ANALYZE это может привести к раздуванию (bloat) самого pg_statistic.

После обновления pg_statistic команда ANALYZE также обновляет relpages, reltuples и relallvisible в pg_class. Эти значения пересчитываются по выборке, а не по результатам полного сканирования таблицы, поэтому они тоже остаются оценочными.

Управление качеством статистики

default_statistics_target

Значение по умолчанию 100 хорошо подходит для большинства столбцов. Оно означает до 100 MCV, 101 границу гистограммы и выборку из 30 000 строк. Увеличивать его имеет смысл, когда:

• в столбце много различных значений, и первые 100 не покрывают достаточную часть распределения;

• запросы по диапазону на неравномерно распределенных данных дают плохие оценки из‑за слишком крупных интервалов гистограммы;

• оценки соединений неточны из‑за неверного n_distinct.

Цена растет линейно. Значение 1000 означает выборку из 300 000 строк, до 1000 MCV, больше места в системном каталоге и более медленное планирование из‑за необходимости искать по более крупным массивам. Максимальное значение — 10 000.

При этом повышать параметр глобально необязательно. Для одного проблемного столбца можно сделать так:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders;

Теперь для status будет храниться до 500 MCV и 501 границы гистограммы, а для остальных столбцов останется значение 100.

Расширенная статистика

Обычная статистика рассматривает каждый столбец независимо. Поэтому планировщик не знает, что city = 'Edinburgh' и country = 'UK' коррелируют между собой. Он независимо перемножает их селективности, из‑за чего оценка может оказаться занижена на порядки.

Расширенная статистика позволяет учитывать такие зависимости для конкретных комбинаций столбцов:

CREATE STATISTICS orders_status_date (dependencies, ndistinct, mcv)
    ON status, created_at FROM orders;
ANALYZE orders;

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

Три типа расширенной статистики решают разные задачи:

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

• ndistinct хранит число различных комбинаций значений по нескольким столбцам. Это помогает при GROUP BY по нескольким столбцам, где иначе планировщик независимо перемножал бы оценки числа различных значений;

• mcv строит общий список наиболее частых комбинаций значений для нескольких столбцов. Это самый мощный, но и самый дорогой вариант. Он полезен для условий WHERE по нескольким коррелирующим столбцам.

Расширенную статистику можно создавать с любой комбинацией этих типов. Начинать обычно стоит с dependencies как самого дешевого варианта, а mcv добавлять, если оценки фильтрации по нескольким столбцам стабильно оказываются неверными.

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

Диагностика неверных оценок

Если запрос работает медленно, первый вопрос должен быть таким: правильно ли планировщик оценил число строк? Сравнить оценку с реальностью можно с помощью EXPLAIN ANALYZE.

Если ошибка составляет всего несколько строк, со статистикой, скорее всего, все в порядке. Но когда оценка расходится с реальностью в 10 раз и более, именно здесь начинаются проблемы с выбором плана. Nested Loop, который выглядит дешевым для 100 строк, может стать катастрофой на 10 000.

Статистика показывает, во что верил планировщик, а сравнение этой оценки с реальностью подсказывает, что делать дальше. Можно запустить ANALYZE, увеличить целевое значение статистики для конкретного столбца или создать расширенную статистику, если в условии участвует несколько столбцов.

Разобраться со статистикой планировщика — только одна из частей работы с PostgreSQL. Короткий вступительный тест поможет проверить знания по другим темам и найти пробелы, которые не всегда очевидны на практике.

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

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

Отдельно посмотреть на новые возможности PostgreSQL 18 и их практическое применение можно на бесплатных открытых уроках:

• 1 сентября в 20:00. «PostgreSQL 18: асинхронный I/O и io_uring на практике». Записаться

• 16 сентября в 20:00. «Темпоральные данные в PostgreSQL 18: история и версии без триггеров». Записаться

Полную подборку бесплатных уроков августа можно найти в дайджесте.