CREATE STATISTICS

примечание

Эта страница переведена при помощи нейросети GigaChat.

CREATE STATISTICS — создание расширенной статистики.

Синтаксис

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    ON ( expression )
    FROM table_name

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    [ ( statistics_kind [, ... ] ) ]
    ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
    FROM table_name

Описание

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

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

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

Параметры

IF NOT EXISTS

Подавляет ошибку, если объект статистики с указанным именем уже существует, вместо этого будет выдано уведомление. Обратите внимание, что учитывается только имя объекта статистики, а не детали его определения. Имя объекта статистики является обязательным, когда указывается IF NOT EXISTS.

statistics_name

Задает имя объекта статистики, при необходимости дополненное схемой, который должен быть создан. Если имя опущено, PostgreSQL выбирает подходящее имя на основе имени родительской таблицы и определенных имен столбцов и/или выражений.

statistics_kind

Задает тип многомерной статистики, который нужно рассчитать в данном объекте статистики. В настоящее время поддерживаются следующие типы:

• ndistinct — для расчета количества различных значений;
• dependencies — для функциональных зависимостей;
• mcv — для построения списков наиболее часто встречающихся значений.

Если этот параметр не указан, в объект статистики будут включены все поддерживаемые типы. Одномерная статистика для выражений создается автоматически, если в определении статистики присутствуют сложные выражения, а не только ссылки на простые столбцы. Дополнительную информацию смотрите в разделе «Примеры многомерной статистики»

column_name

Указывает имя столбца таблицы, по которому рассчитывается статистика. Допустимо только при построении многомерной статистики. Необходимо указать как минимум два столбца или выражения, порядок их указания не имеет значения.

expression

Задает выражение, по которому рассчитывается статистика. Может использоваться как для создания одномерной статистики (если указано одно выражение), так и как часть списка для многомерной статистики. В последнем случае для каждого выражения из списка создаются отдельные одномерные статистики.

table_name

Задает имя таблицы, при необходимости дополненное схемой, содержащей столбцы, по которым рассчитывается статистика. Подробнее об обработке наследования и партиционирования смотрите в описании команды ANALYZE.

Примечания

Необходимо быть владельцем таблицы, чтобы создать объект статистики на ее основе. Однако после создания объект статистики существует независимо от таблицы.

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

Расширенная статистика в настоящее время не используется планировщиком для оценки селективности при соединении таблиц. Это ограничение, вероятно, будет снято в будущих версиях PostgreSQL.

Примеры

Создайте таблицу 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.

Смотрите также

ALTER STATISTICS, DROP STATISTICS