Статистика
Базовая статистика
16
Авторские права
© Postgres Professional, 2019–2024
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
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). Поскольку такая статистика
крайне важна, она обновляется некоторыми 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
Сервер хранит еще несколько показателей статистики.
В поле pg_stats.correlation записывается показатель физической
упорядоченности значений столбца. Если значения хранятся строго по
возрастанию, показатель будет близок к единице; если по убыванию —
к минус единице. Чем более хаотично расположены данные на диске,
тем ближе значение показателя к нулю. Именно это поле использует
оптимизатор, когда выбирает между сканированием битовой карты и
обычным индексным сканированием.
Поле pg_class.relallvisible хранит количество страниц таблицы, которые
содержат только актуальные версии строк (эта информация
обновляется вместе с картой видимости). Если количество
недостаточно велико, планировщик может отказаться от сканирования
только индекса в пользу сканирования по битовой карте.
В поле pg_stats.avg_width сохраняется средний размер значений
в данном столбце в байтах для расчета необходимого для операции
объема памяти.
12
Элементы составных полей
Наиболее частые элементы
pg_stats.most_common_elems
pg_stats.most_common_elem_freqs
Гистограмма количества элементов
pg_stats.elem_count_histogram
Для таких составных типов, как массивы или tsvector, в pg_stats
хранится распределение не только самих значений, но и их элементов:
●
most_common_elems и most_common_elem_freqs содержат наиболее
частые элементы и их частоты;
●
elem_count_histogram содержит гистограмму количества элементов
в значении (например, в случае массива — гистограмму длин
массивов).
Это позволяет более точно планировать запросы с участием полей
не в первой нормальной форме. В частности, эта информация важна
для классов операторов индексного метода GIN, поскольку позволяет
отделить частые значения (то есть встречающиеся во многих
документах, условие с низкой селективностью) от редких (условие
с высокой селективностью).
14
Частные и общие планы
Частные планы
может быть полезен при неравномерном распределении,
но запрос планируется заново при каждом вызове
Общий план
оптимален для равномерного распределения
кешируется в случае подготовленных операторов
Seq Scan on tasks
Filter: status = 'done'
Index Scan on tasks
Index Cond: status = 'todo'
Index Scan on tasks
Index Cond: id = $1
строится
с учетом значений
параметров
строится
без учета значений
параметров
При простом протоколе запросов (вспомните тему «Планирование
и выполнение») каждый запрос планируется заново с учетом значений
параметров.
Расширенный протокол позволяет подготавливать операторы, причем
операторы могут иметь параметры. Подготовка всегда включает разбор
и переписывание запроса, и дерево разбора сохраняется в локальной
памяти обслуживающего процесса.
При выполнении подготовленного оператора есть варианты. Запрос
может, как обычно, планироваться каждый раз заново с учетом
значений параметров. Такие планы называются частными (custom).
Это имеет смысл при неравномерном распределении, ведь для разных
значений оптимальные планы могут отличаться. Например, для
высокоселективных условий может лучше работать индексный доступ,
а если значение встречается часто — последовательный.
Но запрос можно спланировать и без учета параметров. Это позволяет
сохранить не только дерево разбора, но и сам план, и не тратить время
на повторное планирование. Такой план называется общим (generic).
Общий план отлично работает в случае равномерного распределения,
когда селективность условия не зависит от конкретного значения.
Но в случае неравномерного распределения общий план может хорошо
работать для одних значений и плохо — для других.
Разберемся, как планировщик решает, какой вариант использовать.
16
Итоги
Характеристики данных собираются в виде статистики
Статистика нужна для оценки кардинальности
Кардинальность используется для оценки стоимости
Стоимость позволяет выбрать оптимальный план
Основа успеха —
адекватная статистика и корректная кардинальность
17
Практика
1. Создайте индекс на таблице билетов (tickets)
по имени пассажира (passenger_name).
2. Какая статистика имеется для этой таблицы?
3. Объясните оценку кардинальности и выбор плана
выполнения следующих запросов:
а) выборка всех билетов,
б) выборка билетов на имя ALEKSANDR IVANOV,
в) выборка билетов на имя ANNA VASILEVA,
г) выборка билета с идентификатором 0005432000284.
