CREATE STATISTICS#
CREATE STATISTICS
CREATE STATISTICS — определение расширенной статистики
Синтаксис
CREATE STATISTICS [ IF NOT EXISTS ]statistics_nameON (expression) FROMtable_nameCREATE STATISTICS [ IF NOT EXISTS ]statistics_name[ (statistics_kind[, ... ] ) ] ON {column_name| (expression) }, {column_name| (expression) } [, ...] FROMtable_name
Описание
Создание команды CREATE STATISTICS создаст новый объект расширенной статистики, отслеживающий данные о указанной таблице, внешней таблице или материализованном представлении. Объект статистики будет создан в текущей базе данных и будет принадлежать пользователю, выполнившему команду.
Команда CREATE STATISTICS имеет две основные формы. Первая форма позволяет собирать одномерную статистику для одного выражения, обеспечивая преимущества, аналогичные индексу выражения, без издержек на обслуживание индекса. В этой форме нельзя указать вид статистики, так как различные виды статистики относятся только к многомерной статистике. Вторая форма команды позволяет собирать многомерную статистику для нескольких столбцов и/или выражений, с возможностью указания включаемых видов статистики. В этой форме также автоматически собирается одномерная статистика для любых выражений, включенных в список.
Если указано имя схемы (например, CREATE STATISTICS
myschema.mystat ...), то объект статистики создается в
указанной схеме. В противном случае он создается в текущей схеме.
Имя объекта статистики должно отличаться от имени любого другого
объекта статистики в той же схеме.
Параметры
IF NOT EXISTSНе генерировать ошибку, если объект статистики с таким же именем уже существует. В этом случае будет выдано уведомление. Обратите внимание, что здесь учитывается только имя объекта статистики, а не детали его определения.
statistics_nameИмя (опционально с указанием схемы) объекта статистики, который будет создан.
statistics_kindВид многомерной статистики, который должен быть вычислен в этом объекте статистики. В настоящее время поддерживаются следующие виды:
ndistinct, который включает статистику n-уникальных значений,dependencies, который включает статистику функциональных зависимостей, иmcv, который включает список наиболее часто встречающихся значений. Если эта часть опущена, в объект статистики включаются все поддерживаемые виды статистики. Унивариантная статистика выражений создается автоматически, если определение статистики включает сложные выражения, а не только простые ссылки на столбцы. Дополнительную информацию см. в разделах Раздел 14.2.2 и Раздел 72.2.column_nameИмя столбца таблицы, для которого будут созданы вычисляемые статистические данные. Это разрешено только при построении многомерной статистики. Необходимо указать как минимум два имени столбцов или выражения, и их порядок не имеет значения.
expressionВыражение, для которого будут созданы вычисляемые статистические данные. Это может использоваться для создания одномерной статистики для одного выражения или в качестве части списка из нескольких имен столбцов и/или выражений для создания многомерной статистики. В последнем случае, для каждого выражения в списке автоматически создаются отдельные одномерные статистические данные.
table_nameИмя (опционально с указанием схемы) таблицы, содержащей столбцы, для которых вычисляются статистические данные; см. ANALYZE для объяснения обработки наследования и разделения.
Примечания
Вы должны быть владельцем таблицы, чтобы создать объект статистики и прочитать его. Однако, после создания, владение объектом статистики не зависит от базовой таблицы(таблиц).
Статистика выражений относится к каждому выражению и аналогична созданию индекса на выражении, за исключением того, что она избегает издержек на обслуживание индекса. Статистика выражений автоматически создается для каждого выражения в определении объекта статистики.
Расширенная статистика в настоящее время не используется планировщиком для оценки выборки при выполнении объединения таблиц. Это ограничение, вероятно, будет устранено в будущей версии Tantor SE-1C.
Примеры
Создайте таблицу 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;
-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
-- now the row count estimate is more accurate:
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;
-- valid combination (found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);
-- invalid combination (not found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
Список MCV предоставляет планировщику более подробную информацию о конкретных значениях, которые часто встречаются в таблице, а также верхнюю границу селективности комбинаций значений, которые не встречаются в таблице, позволяя ему генерировать более точные оценки в обоих случаях.
Создайте таблицу t3 с одним столбцом типа timestamp и выполняйте запросы, используя выражения на этом столбце. Без расширенной статистики планировщик не имеет информации о распределении данных для выражений и использует значения по умолчанию. Планировщик также не понимает, что значение даты, усеченной до месяца, полностью определяется значением даты, усеченной до дня. Затем строятся статистики для этих двух выражений: выражения и 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;
-- the number of matching rows will be drastically underestimated:
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;
-- build ndistinct statistics on the pair of expressions (per-expression
-- statistics are built automatically)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;
ANALYZE t3;
-- now the row count estimates are more accurate:
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.