Описание

CREATE STATISTICS создает новый объект расширенной статистики, собирающий данные об указанной таблице, сторонней таблице или материализованном представлении. Объект статистики создается в текущей базе данных; его владельцем становится пользователь, выполнивший команду.

Команда CREATE STATISTICS имеет две основные формы. Первая форма позволяет собирать одномерную статистику для отдельного выражения, что дает преимущества, схожие с использованием индекса по выражению, но без накладных расходов на обслуживание индекса. В этой форме нельзя указывать виды статистики, так как они относятся только к многомерной статистике. Вторая форма команды позволяет собирать многомерную статистику по нескольким столбцам и/или выражениям; при этом можно явно указать виды собираемой статистики. Использование этой формы также приведет к автоматическому сбору одномерной статистики по всем выражениям, включенным в список.

Если указано имя схемы (например, CREATE STATISTICS myschema.mystat ...) то объект статистики создается в заданной схеме. В противном случае он создается в текущей схеме. Указанное имя объекта статистики должно отличаться от имен всех других объектов статистики в этой же схеме.

Параметры

Примечания

Для создания объекта статистики по таблице необходимо быть её владельцем. Однако после создания владение объектом статистики становится независимым от владельца базовой таблицы (таблиц).

Статистика по выражениям строится для каждого выражения индивидуально; это аналогично созданию индекса по выражению, но без накладных расходов на обслуживание индекса. Статистика по выражениям строится автоматически для каждого выражения, указанного в определении объекта статистики.

В настоящее время расширенная статистика не используется планировщиком для оценки селективности при выполнении соединений таблиц. Это ограничение, вероятно, будет устранено в одной из будущих версий Digital Q.DataBase.

Примеры

Создание таблицы 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;

-- 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 с одним столбцом типа метка времени, и выполнять запросы, использующие выражения с этим столбцом. Без расширенной статистики планировщик не располагает информацией о распределении данных для этих выражений и использует оценки по умолчанию. Планировщик также не учитывает, что значение даты, усеченное до месяца, полностью определяется значением даты, усеченным до дня. Затем на базе этих двух выражений строятся статистики выражений и статистика 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 команда в стандарте SQL.

См. также