Описание

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

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

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

Параметры

Примечания

Вы должны быть владельцем таблицы, чтобы создать объект статистики и прочитать его. Однако, после создания, владение объектом статистики не зависит от базовой таблицы(таблиц).

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

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

Примеры

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

См. также