14.2. Статистика, используемая планировщиком#
14.2. Статистика, используемая планировщиком #
14.2.1. Статистика для одного столбца #
Как мы видели в предыдущем разделе, планировщик запросов должен оценить количество строк, извлекаемых запросом, чтобы принимать хорошие решения по планам запросов. В этом разделе представлен краткий обзор статистики, которую система использует для этих оценок.
Один из компонентов статистики - это общее количество записей в каждой таблице и индексе, а также количество дисковых блоков, занимаемых каждой таблицей и индексом. Эта информация хранится в таблице pg_class
, в столбцах reltuples
и relpages
. Можно посмотреть на это с помощью запросов, подобных этому:
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%'; relname | relkind | reltuples | relpages ----------------------+---------+-----------+---------- tenk1 | r | 10000 | 358 tenk1_hundred | i | 10000 | 30 tenk1_thous_tenthous | i | 10000 | 30 tenk1_unique1 | i | 10000 | 30 tenk1_unique2 | i | 10000 | 30 (5 rows)
Здесь мы видим, что tenk1
содержит 10000 строк, как и его индексы, но индексы (как неудивительно) значительно меньше таблицы.
В целях эффективности, reltuples
и relpages
не обновляются немедленно, и поэтому они обычно содержат несколько устаревшие значения. Они обновляются с помощью команд VACUUM
, ANALYZE
и нескольких команд DDL, таких как CREATE INDEX
. Операция VACUUM
или ANALYZE
, которая не сканирует всю таблицу (что обычно бывает), инкрементально обновляет счетчик reltuples
на основе части таблицы, которую она сканировала, что приводит к приближенному значению. В любом случае, планировщик масштабирует значения, которые он находит в pg_class
, чтобы соответствовать текущему физическому размеру таблицы, тем самым получая более точное приближение.
Большинство запросов извлекает только долю строк из таблицы, из-за предложений WHERE
, которые ограничивают строки для исследования. Планировщик должен сделать оценку селективности предложений WHERE
, то есть доли строк, соответствующих каждому условию в предложении WHERE
. Информация, используемая для этой задачи, хранится в системном каталоге pg_statistic
. Записи в pg_statistic
обновляются командами ANALYZE
и VACUUM ANALYZE
, и даже после свежего обновления они всегда приближенные.
Вместо прямого просмотра pg_statistic
лучше обратиться к его представлению pg_stats
при ручном анализе статистики. pg_stats
разработан для более удобного чтения. Кроме того, pg_stats
доступен для чтения всем, в то время как pg_statistic
доступен только для суперпользователя. (Это предотвращает получение непривилегированными пользователями информации о содержимом таблиц других пользователей из статистики. Представление pg_stats
ограничено отображением только строк о таблицах, которые текущий пользователь может прочитать). Например, можно выполнить:
SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename = 'road'; attname | inherited | n_distinct | most_common_vals ---------+-----------+------------+------------------------------------ name | f | -0.363388 | I- 580 Ramp+ | | | I- 880 Ramp+ | | | Sp Railroad + | | | I- 580 + | | | I- 680 Ramp name | t | -0.284859 | I- 880 Ramp+ | | | I- 580 Ramp+ | | | I- 680 Ramp+ | | | I- 580 + | | | State Hwy 13 Ramp (2 rows)
Обратите внимание, что для одной и той же колонки отображаются две строки: одна соответствует полной иерархии наследования, начиная с таблицы road
(inherited
=t
), а другая включает только саму таблицу road
(inherited
=f
).
Количество информации, хранящейся в структуре pg_statistic
при выполнении команды ANALYZE
, в частности, максимальное количество записей в массивах most_common_vals и histogram_bounds для каждого столбца, может быть установлено отдельно для каждого столбца с помощью команды ALTER TABLE SET STATISTICS
или глобально путем установки переменной конфигурации default_statistics_target. В настоящее время предельное значение по умолчанию составляет 100 записей. Увеличение этого значения может позволить делать более точные оценки планировщика, особенно для столбцов с неравномерным распределением данных, за счет использования большего объема памяти в структуре pg_statistic
и незначительного увеличения времени вычисления оценок. С другой стороны, для столбцов с простым распределением данных может быть достаточно и меньшего значения предела.
Дополнительные сведения о использовании статистики планировщиком можно найти в Глава 73.
14.2.2. Расширенная статистика #
Часто можно наблюдать медленные запросы с плохими планами выполнения из-за того, что несколько столбцов, используемых в предложениях запроса, коррелируют между собой. Планировщик обычно предполагает, что несколько условий независимы друг от друга, что не выполняется, когда значения столбцов коррелируют. Обычные статистические данные, из-за своей природы, не могут учитывать взаимосвязь между столбцами. Однако Tantor BE имеет возможность вычислять многомерную статистику, которая может учитывать такую информацию.
Поскольку количество возможных комбинаций столбцов очень велико, вычисление многомерной статистики автоматически является непрактичным. Вместо этого, могут быть созданы объекты расширенной статистики, чаще называемые просто объектами статистики, чтобы указать серверу получить статистику по интересующим наборам столбцов.
Все объекты статистики создаются с помощью команды CREATE STATISTICS
.
Создание такого объекта просто создает запись в каталоге, выражающую интерес к статистике. Фактическое сбор данных выполняется с помощью команды ANALYZE
(либо вручную, либо автоматически в фоновом режиме).
Собранные значения можно изучить в каталоге pg_statistic_ext_data
.
ANALYZE
вычисляет расширенную статистику на основе той же выборки строк таблицы, которую используется для вычисления обычной статистики по одному столбцу. Поскольку размер выборки увеличивается при увеличении цели статистики для таблицы или любого из ее столбцов (как описано в предыдущем разделе), более высокая цель статистики обычно приводит к более точной расширенной статистике, а также к большему времени, затрачиваемому на ее вычисление.
Следующие подразделы описывают виды расширенной статистики, которые в настоящее время поддерживаются.
14.2.2.1. Функциональные зависимости #
Простейший вид расширенной статистики отслеживает функциональные зависимости, понятие, используемое в определениях нормальных форм баз данных. Мы говорим, что столбец b
функционально зависит от столбца a
, если знание значения a
достаточно для определения значения b
, то есть нет двух строк с одинаковым значением a
, но разными значениями b
. В полностью нормализованной базе данных функциональные зависимости должны существовать только на первичных ключах и суперключах. Однако на практике множество наборов данных не полностью нормализовано по разным причинам; намеренная денормализация в целях повышения производительности является распространенным примером. Даже в полностью нормализованной базе данных между некоторыми столбцами может существовать частичная корреляция, которая может быть выражена как частичная функциональная зависимость.
Существование функциональных зависимостей напрямую влияет на точность оценок в определенных запросах. Если запрос содержит условия как на независимые, так и на зависимые столбцы, условия на зависимые столбцы не уменьшают размер результата; но без знания о функциональной зависимости, планировщик запросов будет предполагать, что условия независимы, что приведет к недооценке размера результата.
Для информирования планировщика о функциональных зависимостях ANALYZE
может собирать измерения кросс-колоночной зависимости. Оценка степени зависимости между всеми наборами колонок была бы чрезвычайно дорогостоящей, поэтому сбор данных ограничен только теми группами колонок, которые встречаются вместе в объекте статистики, определенном с помощью опции dependencies
. Рекомендуется создавать статистику dependencies
только для сильно коррелированных групп колонок, чтобы избежать ненужных издержек как в ANALYZE
, так и в последующем планировании запросов.
Вот пример сбора статистики функциональной зависимости:
CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes; ANALYZE zipcodes; SELECT stxname, stxkeys, stxddependencies FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts'; stxname | stxkeys | stxddependencies ---------+---------+------------------------------------------ stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130} (1 row)
Здесь видно, что столбец 1 (почтовый индекс) полностью определяет столбец 5 (город), поэтому коэффициент равен 1.0, в то время как город определяет почтовый индекс только примерно на 42% времени, что означает, что существует много городов (58%), которые представлены более чем одним почтовым индексом.
Когда вычисляется селективность для запроса, включающего функционально зависимые столбцы, планировщик корректирует оценки селективности для каждого условия с использованием коэффициентов зависимости, чтобы не получить недооценку.
14.2.2.1.1. Ограничения функциональных зависимостей #
В настоящее время функциональные зависимости применяются только при рассмотрении простых условий равенства, сравнивающих столбцы с константными значениями, и в предложениях IN
с константными значениями. Они не используются для улучшения оценок для условий равенства, сравнивающих два столбца или сравнивающих столбец с выражением, а также для предложений диапазона, LIKE
или любого другого типа предложений.
При оценке с использованием функциональных зависимостей планировщик предполагает, что условия на задействованных столбцах совместимы и, следовательно, избыточны. Если они несовместимы, правильная оценка будет равна нулю строк, но такая возможность не учитывается. Например, для данного запроса:
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
планировщик будет игнорировать предположение city
, так как оно не изменяет селективность, что является правильным. Однако, он будет делать ту же самую предположение о
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
даже если на самом деле не будет ни одной строки, удовлетворяющей этому запросу. Однако, статистика функциональной зависимости не предоставляет достаточно информации для такого заключения.
Во многих практических ситуациях это предположение обычно выполняется; например, в приложении может быть графический интерфейс, который позволяет выбирать только совместимые значения города и почтового индекса для использования в запросе. Но если это не так, функциональные зависимости могут быть непригодным вариантом.
14.2.2.2. Многомерные N-уникальные счетчики #
В одностолбцовой статистике хранится количество уникальных значений в каждом столбце. Оценки количества уникальных значений при объединении более одного столбца (например, для GROUP BY a, b
) часто неверны, когда планировщик имеет только одностолбцовые статистические данные, что приводит к выбору неправильных планов.
Для улучшения таких оценок ANALYZE
может собирать n-уникальные
статистики для групп столбцов. Как и раньше, это непрактично делать
для каждой возможной группировки столбцов, поэтому данные собираются только для
тех групп столбцов, которые встречаются вместе в объекте статистики,
определенном с помощью параметра ndistinct
. Данные будут собираться
для каждой возможной комбинации двух или более столбцов из набора
перечисленных столбцов.
Продолжая предыдущий пример, количество уникальных значений n-distinct в таблице почтовых индексов может выглядеть следующим образом:
CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes; ANALYZE zipcodes; SELECT stxkeys AS k, stxdndistinct AS nd FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts2'; -[ RECORD 1 ]-------------------------------------------------------- k | 1 2 5 nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178} (1 row)
Это указывает на то, что есть три комбинации столбцов, которые имеют 33178 различных значений: почтовый индекс и штат; почтовый индекс и город; и почтовый индекс, город и штат (то, что они все равны, ожидаемо, учитывая, что почтовый индекс в одиночку уникален в этой таблице). С другой стороны, комбинация города и штата имеет только 27435 различных значений.
Следует создавать объекты статистики ndistinct
только для комбинаций столбцов, которые фактически используются для группировки, и для которых неправильная оценка количества групп приводит к плохим планам. В противном случае, циклы ANALYZE
просто тратятся.
14.2.2.3. Многомерные списки MCV #
Другой тип статистики, хранящейся для каждого столбца, - это списки наиболее часто встречающихся значений. Это позволяет получать очень точные оценки для отдельных столбцов, но может приводить к значительным неправильным оценкам для запросов с условиями на несколько столбцов.
Для улучшения таких оценок ANALYZE
может собирать списки MCV для комбинаций столбцов. Аналогично функциональным зависимостям и коэффициентам n-различных, это непрактично делать для каждой возможной группировки столбцов. Тем более в этом случае, поскольку список MCV (в отличие от функциональных зависимостей и коэффициентов n-различных) хранит общие значения столбцов. Поэтому данные собираются только для тех групп столбцов, которые встречаются вместе в объекте статистики, определенном с помощью опции mcv
.
Продолжая предыдущий пример, список MCV для таблицы почтовых индексов может выглядеть следующим образом (в отличие от более простых типов статистики, для проверки содержимого MCV требуется функция):
CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes; ANALYZE zipcodes; SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3'; index | values | nulls | frequency | base_frequency -------+------------------------+-------+-----------+---------------- 0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05 1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05 2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133 3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113 4 | {New York, NY} | {f,f} | 0.001967 | 0.000114 5 | {Atlanta, GA} | {f,f} | 0.001633 | 3.3e-05 6 | {Sacramento, CA} | {f,f} | 0.001433 | 7.8e-05 7 | {Miami, FL} | {f,f} | 0.0014 | 6e-05 8 | {Dallas, TX} | {f,f} | 0.001367 | 8.8e-05 9 | {Chicago, IL} | {f,f} | 0.001333 | 5.1e-05 ... (99 rows)
Это указывает на то, что наиболее распространенной комбинацией города и штата является Вашингтон в округе Колумбия, с фактической частотой (в выборке) около 0,35%. Базовая частота этой комбинации (рассчитанная на основе простых частот по столбцам) составляет всего 0,0027%, что приводит к недооценке на два порядка.
Следует создавать объекты статистики MCV только для комбинаций столбцов, которые фактически используются вместе в условиях, и для которых неправильная оценка количества групп приводит к плохим планам. В противном случае, ANALYZE
и циклы планирования просто тратятся.