Описание #

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

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

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

Параметры

Примечания

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

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

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

Примеры #

Создайте таблицу 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 с одним полем 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;

-- оценки количества совпадающих строк будут существенно занижены:
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 для столбца. Однако с новой статистикой планировщик понимает, что условия связаны, и даёт гораздо более точные оценки.

Совместимость

Команда CREATE STATISTICS отсутствует в стандарте SQL.

См. также