Статистика
Расширенная статистика
16
Авторские права
© Postgres Professional, 2019–2024
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Функциональная зависимость
Наиболее частые комбинации значений
Число уникальных комбинаций значений
Статистика по выражениям
3
Расширенная статистика
Включает
многовариантную статистику (по нескольким столбцам)
статистику по выражениям
Объект базы данных, создается вручную
хранится в pg_statistic_ext и pg_statistic_ext_data
представления pg_stats_ext и pg_stats_ext_exprs
После создания статистика собирается автоматически
Базовой статистики, которая собирается автоматически, может не
хватать для точных оценок кардинальности и селективности.
PostgreSQL позволяет администратору вручную определить, какая
дополнительная — расширенная — статистика требуется. Можно
собрать статистику, охватывающую не один столбец, а сразу несколько
(многовариантная статистика), или статистику по произвольному
выражению.
Заметьте, что базовая статистика собирается для таблиц и их столбцов,
но не для индексов (за исключением индексов по выражениям).
Поэтому и индекс, построенный по нескольким столбцам, не приводит
сам по себе к появлению многовариантной статистики.
Расширенная статистика создается командой CREATE STATISTICS.
После того, как объект создан, соответствующая статистика будет
собираться автоматически в фоновом режиме или командой ANALYZE.
Собранная информация хранится в таблицах pg_statistic_ext и
pg_statistic_ext_data; доступная пользователю статистика отображается
в представлениях pg_stats_ext и pg_stats_ext_exprs.
4
Зависимые столбцы
Функциональная зависимость (dependencies)
значение одного столбца определяет значение другого столбца
статистика улучшает оценку селективности условий
Казань
Самара
420000
443000
city index
Нижний Новгород 603000
Великий Новгород 173000
Существует несколько видов многовариантной статистики (то есть
статистики по нескольким столбцам таблицы), которые можно указать
при создании объекта расширенной статистики.
Функциональная зависимость между столбцами показывает,
насколько данные в одном столбце определяются значением другого
столбца.
В примере на слайде почтовый индекс однозначно определяет город,
поэтому селективность условия city = 'Самара' and index = '443000'
определяется селективностью предиката index = '443000'. Такие
предикаты, селективность которых нельзя рассчитывать отдельно друг
от друга, называются коррелированными.
6
Частые комбинации
Наиболее частые комбинации значений (mcv)
как pg_stats.most_common_vals/freqs, но для нескольких столбцов
статистика улучшает оценку селективности условий
Казань Волга
city river
Нижний Новгород Волга
Великий Новгород Волхов
Самара Волга
Нижний Новгород Ока
Список наиболее частых комбинаций значений позволяет сохранить
несколько комбинаций значений и их частоту.
На слайде показаны возможные пары город — река. Очевидно, что
предикаты со столбцами city и river коррелированы, но, в отличие от
предыдущего примера, ни один из этих столбцов не определяет другой
(между городами и реками отношение «многие ко многим»). В этом
случае статистика по функциональным зависимостям не поможет
улучшить оценки.
8
Уникальные комбинации
Число уникальных комбинаций значений (ndistinct)
как pg_stats.ndistinct, но для нескольких столбцов
статистика улучшает оценку кардинальности для группировки
Казань
Самара
Респ. Татарстан
Самарская обл.
city region
Нижний Новгород Нижегородская обл.
Великий Новгород Новгородская обл.
420000
443000
index
603000
173000
Число уникальных комбинаций значений позволяет улучшить оценку
кардинальности при группировке по нескольким столбцам.
В примере на слайде число возможных комбинаций всех полей,
очевидно, нельзя определить, просто перемножив количество
уникальных значений для каждого столбца по отдельности.
10
Статистика по выражению
Расширенная статистика по выражению
как если бы в таблице был определен генерируемый столбец
статистика улучшает оценку селективности условий с выражениями
Казань
Самара
420000
443000
city index
Нижний Новгород 603000
Великий Новгород 173000
420000, г. Казань
443000, г. Самара
address
603000, г. Нижний Новгород
173000, г. Великий Новгород
Расширенная статистика по выражению позволяет собрать всю
базовую статистику, которая была бы собрана, если бы в таблице был
определен столбец, вычисляемый по этому выражению.
Если в предикате слева или справа от оператора стоит не имя столбца,
а выражение, планировщик использует фиксированную оценку
селективности. С помощью статистики по выражению это можно
исправить.
Заметим, что выражения также можно использовать вместо имен
столбцов в многовариантной статистике любого типа.
12
Итоги
Расширенная статистика помогает в сложных случаях
Создается вручную, поддерживается автоматически
Может увеличить затраты на анализ и планирование
13
Практика
1. Используя команды из демонстрации, создайте статистики
типа dependencies, mcv и ndistinct для таблицы flights.
Измерьте время выполнения команды ANALYZE. Удалите
расширенные статистики, снова измерьте время выполнения
команды ANALYZE и сравните с предыдущим результатом.
2. Напишите запрос, выбирающий все перелеты в бизнес-
классе стоимостью более 100 тыс. рублей. Поможет ли
расширенная статистика улучшить оценку кардинальности
результата? Если да, то какой тип статистики лучше
использовать?
1. Выполняйте измерение времени несколько раз и усредняйте
результаты, чтобы сгладить неравномерности.
