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

Утро началось не с кофе, а с визита Лены:

— Макс, ну почему мой запрос опять «тупит»? Я посмотрела планы: планировщик думает, что фильтр вернёт 10 строк, а по факту вытаскивает 12 тысяч! ANALYZE делаю прям перед выполнением… Как это возможно?

Макс кивнул, изучая EXPLAIN. В плане черным по белому: Estimated rows = 10, Actual rows = 12000. А вслед за этим — очередной Nested Loop на миллион итераций. Лена явно расстроена.

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

— Это как? — насторожилась Лена.

— Например, представь таблицу с полями country и city. В реальности город определяет страну, но без расширенной статистики оценка условий может перемножить независимые селективности:

WHERE country = 'Россия'
  AND city = 'Тула'

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

— Кажется поняла, но для чего указывать city и country, если эти справочники связаны между собой?

— Это просто пример для наглядности. На практике связи бывают не такие очевидные. Например, статус и дата операции, код валюты и тип операции, БИК и корр.счет, индекс, код города и код улицы и т.д.

— Теперь поняла. Значит планировщик Недооценивает число строк в таких случаях?

— Да, иногда и переоценивает. И если фильтров несколько, и они явно связаны — ошибка прогноза только растёт и влияет на выбор плана: тот же Nested Loop вместо Hash Join, потому что Postgres думает, что подвыборки микроскопические.

Лена задумчиво посмотрела на экран:

— И что делать? Я думала, кроме ANALYZE тут ничего не поможет.

— Вот тут появляется твой билет в закрытый клуб знания, — улыбнулся Макс. — Есть расширенная статистика для колонок, которые коррелируют. Функционал появился еще в Postgres 10 версии. Делается так:

CREATE STATISTICS stats_country_city (dependencies) ON country, city FROM customers;
ANALYZE customers;

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

— А надо что-то делать после этого?

— Только снова запустить ANALYZE — тогда статистика обновится и планировщик сможет применять новую информацию.

Что происходит после этого?

Лена создала статистику на свои условия, запустила повторный ANALYZE и пересмотрела EXPLAIN:

• Estimated rows теперь почти совпадает с Actual rows!

• Планировщик выбирает Hash Join вместо Nested Loop.

• Запрос вместо десятков секунд работает за миллисекунды!

— Вот это магия! — Лена просияла. — А какие схемы ещё можно анализировать расширенной статистикой?

— Есть ещё опции:

ndistinct — уникальные сочетания значений в комбинации полей

mcv (most common values) — наиболее часто встречающиеся шаблоны значений

dependencies — функциональные зависимости между столбцами

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

CREATE STATISTICS stats_month
ON (date_trunc('month', dt))
FROM transactions;

ANALYZE transactions;

Статистика по выражениям доступна в современных версиях PostgreSQL. Для одного выражения не нужно указывать ndistinct: PostgreSQL соберёт обычную статистику по результату выражения. Как и любой объект статистики, её стоит создавать под подтверждённую ошибку оценки, а не на каждую возможную комбинацию.

Что запомнить

• Обычная ANALYZE знает только о каждой колонке в отдельности.

• Для связанных фильтров (несколько условий в WHERE) планировщик часто ошибается.

• Расширенная статистика по колонкам (CREATE STATISTICS ... (dependencies)) помогает планировщику увидеть реальную картину — и выбирать более быстрые планы.

• Не забывайте после создания статистики делать ANALYZE!

Лена закрыла ноутбук, явно воодушевлённая:

— Теперь буду искать не только дыры в индексах, но и анализировать связи между столбцами!

Макс кивнул:

— Кто владеет статистикой — управляет планами. EXPLAIN‘ся на здоровье!