Оптимизация запросов
Статистика
13
Авторские права
© Postgres Professional, 2019–2022
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Базовая статистика
Наиболее частые значения и гистограммы
Частные и общие планы выполнения
Расширенная и многовариантная статистика
Статистика по выражениям
Использование статистики для оценки кардинальности
и селективности
3
Базовая статистика
Размер таблицы
строки (pg_class.reltuples) и страницы (pg_class.relpages)
Собирается
операциями DDL
очисткой
анализом
Настройка
default_statistics_target = 100
Базовая статистика собирается на уровне всей таблицы и на уровне
отдельных столбцов.
К статистике таблицы относится информация о размере объекта
(reltuples, relpages в таблице pg_class). Поскольку такая статистика
крайне важна, она собирается не только при анализе (ANALYZE),
но и заполняется некоторыми DDL-операциями (CREATE INDEX,
CREATE TABLE AS SELECT), а затем уточняется при очистке.
Кроме того, планировщик масштабирует количество строк
в соответствии с отклонением реального размера файла данных
от значения relpages.
При анализе просматривается случайная выборка строк. Установлено,
что размер выборки, обеспечивающий хорошую точность оценок,
практически не зависит от размера таблицы. В качестве размера
выборки используется ориентир статистики, заданный параметром
default_statistics_target, умноженный на 300.
При этом следует понимать, что статистика не должна быть абсолютно
точной, чтобы планировщик мог выбрать приемлемый план; часто
достаточно попадания в порядок.
4
pg_statistic (pg_stats)
Базовая статистика
null_frac
pg_class.reltuples, relpages
n_distinct
значения
частота
количество
или доля
доля
Вся остальная статистика собирается отдельно для каждого столбца
при анализе таблицы. Обычно этим занимается автоанализ (его
настройка рассматривается в курсе DBA2).
Статистика на уровне столбцов хранится в таблице pg_statistic.
Но смотреть проще в представление pg_stats, которое показывает
информацию в более удобном виде.
Поле null_frac содержит долю строк с неопределенными значениями
в столбце (от 0 до 1).
Поле n_distinct хранит число уникальных значений в столбце. Если
значение n_distinct отрицательно, то модуль этого числа показывает
долю уникальных значений. Например, -1 означает, что все значения
уникальны (типичный случай для первичного ключа).
6
Наиболее частые значения
null_frac
[most_common_vals] ≤ default_statistics_target
[most_common_freqs]
значения
частота
pg_class.reltuples, relpages
Если бы все данные были всегда распределены равномерно, то есть
все значения встречались бы с одинаковой частотой, этой информации
было бы почти достаточно (нужен еще минимум и максимум).
Но в реальности неравномерные распределения встречаются очень
часто. Поэтому собирается еще и следующая информация:
- массив наиболее частых значений — поле most_common_vals;
- массив частот этих значений — поле most_common_freqs.
Частота из этих массивов непосредственно служит оценкой
селективности для поиска конкретного значения.
Все это прекрасно работает, пока число различных значений не очень
велико. Максимальный размер каждого из массивов ограничен
параметром default_statistics_target. Это значение можно
переопределять на уровне отдельного столбца; в этом случае размер
анализируемой выборки будет определяться по максимальному
значению для таблицы.
Тонкий момент представляют «большие» значения. Чтобы не
увеличивать размер pg_statistic и не нагружать планировщик
бесполезной работой, значения, превышающие 1 Кбайт, исключаются
из статистики и анализа. В самом деле, если в поле хранятся такие
большие значения, скорее всего, они уникальны и не имеют шансов
попасть в most_common_vals.
8
Гистограмма
null_frac
[histogram_bounds] ≤ default_statistics_target
значения
частота
Если число различных значений слишком велико, чтобы записать их
в массив, на помощь приходит гистограмма. Гистограмма состоит из
нескольких корзин, в которые помещаются значения. Количество корзин
ограничено тем же параметром default_statistics_target.
Ширина корзин выбирается так, чтобы в каждую попало примерно
одинаковое число значений (на рисунке это выражается в одинаковой
площади прямоугольников).
При таком построении достаточно хранить только массив крайних
значений каждой корзины — поле histogram_bounds. Частота одной
корзины равна 1/(число корзин).
Оценить селективность условия поле < значение можно как N/(общее
число корзин), где N — число корзин, лежащих слева от значения.
Оценку можно улучшить, добавив часть корзины, в которую попадает
само значение.
Если же надо оценить селективность условия поле = значение, то
гистограмма в этом не может помочь, и приходится довольствоваться
предположением о равномерном распределении и брать в качестве
оценки 1/n_distinct.
9
Комбинация методов
null_frac
[histogram_bounds]
значения
частота
[most_common_vals]
[most_common_freqs]
Но обычно два подхода объединяются: строится список наиболее
частых значений, а все остальные значения покрываются
гистограммой.
При этом гистограмма строится так, что в ней не учитываются
значения, попавшие в список. Это позволяет улучшить оценки.
11
Дополнительные поля
Упорядоченность (использовать ли битовую карту?)
pg_stats.correlation
(1 — по возрастанию, 0 — хаотично, –1 — по убыванию)
Видимость (использовать ли сканирование только индекса?)
pg_class.relallvisible
Средний размер значения в байтах (оценка памяти)
pg_stats.avg_width
Информация об элементах массивов, tsvector и т. п.
pg_stats.most_common_elems
pg_stats.most_common_elem_freqs
pg_stats.elem_count_histogram
Есть еще несколько значений статистики.
В поле correlation записывается показатель упорядоченности значений
на диске. Если значения хранятся строго по возрастанию, показатель
будет близок к единице; если по убыванию — к минус единице. Чем
более хаотично расположены данные на диске, тем ближе значение
показателя к нулю. Именно это поле использует оптимизатор, когда
выбирает между сканированием битовой карты и обычным индексным
сканированием.
Поле pg_class.relallvisible хранит количество страниц таблицы, которые
содержат только актуальные версии строк (эта информация
обновляется вместе с картой видимости). Если количество
недостаточно велико, планировщик может отказаться от сканирования
только индекса в пользу сканирования по битовой карте.
В поле avg_width сохраняется средний размер значений в данном
столбце в байтах для расчета необходимого для операции объема
памяти.
В полях most_common_elems, most_common_elem_freqs и
elem_count_histogram для таких составных типов, как массивы или
tsvector, хранится распределение не самих значений, а их элементов.
Это позволяет более точно планировать запросы с участием полей не
в первой нормальной форме.
12
Расширенная статистика
CREATE STATISTICS
объект базы данных, создается вручную
после создания статистика собирается автоматически
pg_statistic_ext и pg_statistic_ext_data; представление pg_stats_ext
Функциональные зависимости между столбцами
и списки наиболее частых комбинаций значений
улучшают оценку селективности условий
с коррелированными предикатами
Число уникальных комбинаций значений
улучшает оценку кардинальности для группировки
Начиная с PostgreSQL 10, можно создавать специальный объект для
расширенной статистики командой CREATE STATISTICS. После того,
как объект создан, соответствующая статистика будет собираться
автоматически.
Существует три вида многовариантной статистики (то есть
статистики по нескольким столбцам таблицы), которые можно указать
при создании объекта расширенной статистики.
Функциональные зависимости между столбцами. Такая статистика
показывает, насколько данные в одном столбце определяются
значением другого столбца. Она помогает улучшить оценку в случае
коррелированных предикатов.
Число уникальных комбинаций значений в столбцах. Такая
информация позволяет улучшить оценку кардинальности группировки
по нескольким столбцам.
Список наиболее частых комбинаций значений. Статистика
помогает улучшить оценку условий, в которых проверяются значения
нескольких столбцов.
При создании расширенной статистики можно указать любую
комбинацию статистик и столбцов.
Собранная информация хранится в таблицах pg_statistic_ext и
pg_statistic_ext_data; доступная пользователю статистика отображается
в представлении pg_stats_ext.
14
Итоги
Характеристики данных собираются в виде статистики
Статистика нужна для оценки кардинальности
Кардинальность используется для оценки стоимости
Стоимость позволяет выбрать оптимальный план
Основа успеха —
адекватная статистика и корректная кардинальность
15
Практика
1. Создайте индекс на таблице билетов (tickets)
по имени пассажира (passenger_name).
2. Какая статистика имеется для этой таблицы?
3. Объясните оценку кардинальности и выбор плана
выполнения следующих запросов:
а) выборка всех билетов,
б) выборка билетов на имя ALEKSANDR IVANOV,
в) выборка билетов на имя ANNA VASILEVA,
г) выборка билета с идентификатором 0005432000284.
