CREATE STATISTICS
CREATE STATISTICS — определить расширенную статистику
Синтаксис
CREATE STATISTICS [ IF NOT EXISTS ] имя_статистики
ON ( выражение )
FROM имя_таблицы
CREATE STATISTICS [ IF NOT EXISTS ] имя_статистики
[ ( вид_статистики [, ... ] ) ]
ON { имя_столбца | ( выражение ) }, { имя_столбца | ( выражение ) } [, ...]
FROM имя_таблицы
Описание
Команда CREATE STATISTICS создаст новый расширенный объект статистики,
отслеживающий данные о заданной таблице, сторонней таблице или материализованном
представлении. Объект статистики будет создан в текущей базе данных и будет
принадлежать пользователю, выполнившему команду.
Команда CREATE STATISTICS имеет две основные формы. Первая форма позволяет
собрать одномерную статистику по одному выражению, предоставляя преимущества,
схожие с индексом по выражению, но без затрат на обслуживание индекса. Эта форма
не позволяет указывать вид статистики, поскольку различные виды относятся только
к многомерным статистикам. Вторая форма этой команды позволяет собрать многомерные
статистики по нескольким столбцам и/или выражениям, при необходимости указав,
какой вид статистики нужно включить. Кроме того, эта форма автоматически запускает
сбор одномерных статистик по всем выражениям в списке.
Если задано имя схемы (например CREATE STATISTICS myschema.mystat ...), то
объект статистики создается в указанной схеме, в противном случае — в текущей.
Имя объекта статистики должно отличаться от имени любого другого объекта
статистики в той же схеме.
Параметры
IF NOT EXISTS
Не выдавать ошибку, если объект статистики с тем же именем уже существует. В этом случае выдается только замечание. Обратите внимание, что здесь рассматривается только имя объекта статистики, а не подробности его определения.
имя_статистики
Имя создаваемого объекта статистики (может быть дополнено схемой).
вид_статистики
Вид многомерной статистики, который будет вычисляться в этом объекте статистики. В настоящее время поддерживаются следующие виды: ndistinct, который активирует статистику количества уникальных значений, dependencies, который активирует статистику функциональных зависимостей, и mcv, который активирует списки наиболее распространенных значений. Если это предложение опущено, в объект статистики включаются все поддерживаемые виды статистики. Одномерная статистика по выражениям собирается автоматически, если определение статистики включает любые сложные выражения, а не просто ссылки на столбцы. Дополнительную информацию см. в подразделе Расширенная статистика и разделе Примеры многомерной статистики.
имя_столбца
Имя столбца таблицы, который будет покрыт вычисляемой статистикой. Это указание допустимо только при построении многомерных статистик. Должно быть задано не менее двух имен столбцов или выражений; их порядок значения не имеет.
выражение
Выражение, которое будет покрыто вычисляемой статистикой. Это можно использовать для построения одномерной статистики по одному выражению или как часть списка из нескольких имен столбцов и/или выражений для построения многомерных статистик. В последнем случае для каждого выражения в списке автоматически строятся отдельные одномерные статистики.
имя_таблицы
Имя таблицы, содержащей столбец (или столбцы), по которому вычисляются статистические
данные (может быть дополнено схемой). Объяснение особенностей обработки иерархии
наследования и партиций см. на справочной странице команды ANALYZE.
Примечания
Чтобы создать считывающий таблицу объект статистики, нужно быть владельцем этой таблицы. Однако после создания объекта статистики его владелец определяется независимо от нижележащих таблиц.
Статистики выражений рассчитываются по отдельным выражениям, и этим схожи с созданием индекса по выражению за исключением того, что они не требуют расходов на обслуживание индекса. Статистики выражений строятся автоматически для каждого выражения в определении объекта статистики.
Примеры
Создание таблицы t1 с двумя функционально зависимыми столбцами, т. е. знания значения в первом столбце достаточно для определения значения в другом столбце. Затем по этим столбцам строится статистика функциональной зависимости:
CREATE TABLE t1 (
a int,
b int
);
INSERT INTO t1 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
ANALYZE t1;
-- количество совпадающих строк будет существенно занижено:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
-- теперь оценка количества строк стала точнее:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
Без статистики функциональной зависимости планировщик предположил бы, что эти два условия WHERE независимы, и перемножил бы их избирательности вместе, получив при этом слишком маленькую оценку количества строк. С такой статистикой планировщик признает, что условия WHERE являются избыточными, и не занижает количество строк.
Создание таблицы t2 с двумя идеально коррелирующими столбцами (содержащими идентичные данные) и списком MCV по этим столбцам:
CREATE TABLE t2 (
a int,
b int
);
INSERT INTO t2 SELECT mod(i,100), mod(i,100)
FROM generate_series(1,1000000) s(i);
CREATE STATISTICS s2 (mcv) ON a, b FROM t2;
ANALYZE t2;
-- рабочая комбинация (входит в MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
-- нерабочая комбинация (не входит в MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
Список MCV предоставляет планировщику более подробную информацию о конкретных значениях, которые обычно отображаются в таблице, а также верхнюю границу избирательности комбинаций значений, которые не отображаются в таблице, что позволяет ему генерировать лучшие оценки в обоих случаях.
Создание таблицы t3 с единственным столбцом метки времени и выполнение запросов с использованием выражений по этому столбцу. Без расширенной статистики планировщик не имеет информации о распределении данных для этих выражений и использует оценки по умолчанию. Также планировщик не понимает, что значение даты, усеченной до месяцев, полностью определяется значением даты, усеченной до дней. Затем по этим двум выражениям строится статистика выражений и ndistinct:
CREATE TABLE t3 (
a timestamp
);
INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
'2020-12-31'::timestamp,
'1 minute'::interval) s(i);
ANALYZE t3;
-- количество совпадающих строк будет существенно занижено:
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
AND '2020-06-30'::timestamp;
EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
FROM t3 GROUP BY 1, 2;
-- построить статистику ndistinct по этой паре выражений (статистика по
-- отдельным выражениям строится автоматически)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
ANALYZE t3;
-- теперь оценка количества строк стала точнее:
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('month', a) = '2020-01-01'::timestamp;
EXPLAIN ANALYZE SELECT * FROM t3
WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
AND '2020-06-30'::timestamp;
EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
FROM t3 GROUP BY 1, 2;
Без статистики выражений и ndistinct планировщик не имеет информации о количестве уникальных значений для этих выражений и должен полагаться на оценки по умолчанию. Предполагается, что условия равенства и вхождения в диапазон имеют избирательность 0,5%, а количество уникальных значений в выражении такое же, как и в столбце (т. е. предполагаются уникальные значения). Это приводит к значительному занижению оценки количества строк в первых двух запросах. Более того, у планировщика нет информации об отношении между выражениями, поэтому он предполагает, что два условия WHERE и GROUP BY независимы, и перемножает их избирательности, получая сильно завышенную оценку количества групп в запросе с агрегатом. Это еще больше усугубляется отсутствием точной статистики по выражениям, вынуждающей планировщик использовать для выражения оценку ndistinct по умолчанию, полученную из статистики ndistinct для столбца. Но при наличии такой статистики планировщик понимает, что условия коррелируют, и выдает гораздо более точные оценки.
Совместимость
В стандарте SQL нет команды CREATE STATISTICS.