Descrição

O comando CREATE STATISTICS cria um novo objeto de estatísticas estendidas rastreando dados sobre a tabela, tabela estrangeira, ou visão materializada, especificada. O objeto de estatísticas é criado no banco de dados corrente, e pertence ao usuário que executa o comando.

O comando CREATE STATISTICS tem duas formas básicas. A primeira forma permite que sejam coletadas estatísticas univariadas para uma única expressão, fornecendo benefícios semelhantes a um índice de expressão sem a sobrecarga da manutenção do índice. Esta forma não permite especificar o tipo de estatística, uma vez que os vários tipos de estatísticas referem-se apenas a estatísticas multivariadas. A segunda forma do comando permite serem coletadas estatísticas multivariadas em várias colunas e/ou expressões, opcionalmente especificando quais tipos de estatísticas incluir. Esta forma também fará com que sejam coletadas automaticamente estatísticas univariadas em quaisquer expressões incluídas na lista.

Se for fornecido o nome do esquema (por exemplo, CREATE STATISTICS meu_esquema.minha_estatística ...), o objeto de estatísticas será criado no esquema especificado. Caso contrário, será criado no esquema corrente. Caso seja fornecido, o nome do objeto de estatísticas deve ser diferente do nome de qualquer outro objeto de estatísticas no mesmo esquema.

Parâmetros

Notas

É necessário ser o dono da tabela para criar um objeto de estatísticas que a leia. No entanto, uma vez criado, o dono do objeto de estatísticas é independente da(s) tabela(s) subjacente(s).

As estatísticas de expressão são por-expressão, e são semelhantes à criação de um índice na expressão, exceto pelo fato de evitarem a sobrecarga da manutenção do índice. As estatísticas de expressão são construídas automaticamente para cada expressão na definição do objeto de estatísticas.

Atualmente as estatísticas estendidas não são utilizadas pelo planejador para estimativas de seletividade feitas em junções de tabelas. Esta limitação provavelmente será removida em uma versão futura do PostgreSQL.

Exemplos

Criar a tabela t1 com duas colunas funcionalmente dependentes, ou seja, o conhecimento de um valor na primeira coluna é suficiente para determinar o valor na outra coluna. Em seguida, são construídas estatísticas de dependência funcional nessas colunas:

CREATE TABLE t1 (
    a   int,
    b   int
);

INSERT INTO t1
    SELECT i/100, i/500
        FROM generate_series(1,1000000) s(i);

ANALYZE t1;

-- o número de linhas correspondentes será drasticamente subestimado:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;

ANALYZE t1;

-- agora a estimativa de contagem de linhas será mais precisa:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

Sem as estatísticas de dependência funcional, o planejador assumiria que as duas condições WHERE são independentes e multiplicaria suas seletividades para chegar a uma estimativa de contagem de linhas muito pequena. Com estas estatísticas, o planejador reconhece que as condições WHERE são redundantes e não subestima a contagem de linhas.

Criar a tabela t2 com duas colunas perfeitamente correlacionadas (contendo dados idênticos), e uma lista MCV nessas colunas:

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;

-- combinação válida (encontrada em MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);

-- combinação inválida (não encontrada no MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);

A lista MCV fornece ao planejador informações mais detalhadas sobre os valores específicos que comumente aparecem na tabela, bem como um limite superior nas seletividades de combinações de valores que não aparecem na tabela, permitindo gerar melhores estimativas nos dois casos.

Criar a tabela t3 com uma única coluna de carimbo de data/hora, e executar consultas usando expressões nesta coluna. Sem estatísticas estendidas, o planejador não tem informações sobre a distribuição dos dados para as expressões, e usa as estimativas padrão. O planejador também não percebe que o valor da data truncada para o mês é inteiramente determinado pelo valor da data truncada para o dia. Em seguida, são construídas estatísticas ndistinct e por-expressão com base nessas duas expressões:

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;

-- o número de linhas correspondentes será drasticamente subestimado:
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;

-- construir estatísticas ndistinct no par de expressões
-- (as estatísticas por-expressão são construídas automaticamente)
CREATE STATISTICS s3 (ndistinct)
    ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

-- agora as estimativas de contagem de linhas são mais precisas:
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;

Sem estatísticas sobre expressão e ndistinct, o planejador não tem informações sobre o número de valores distintos para as expressões, e precisa confiar nas estimativas padrão. Presume-se que as condições de igualdade e intervalo tenham 0,5% de seletividade, e que o número de valores distintos na expressão seja o mesmo da coluna (ou seja, único). Isto resulta em uma subestimativa significativa da contagem de linhas nas duas primeiras consultas. Além disso, o planejador não tem informações sobre a relação entre as expressões, portanto, assume que as duas condições WHERE e GROUP BY são independentes, e multiplica suas seletividades para chegar a uma superestimativa severa da contagem de grupos na consulta de agregação. Isto é agravado ainda mais pela falta de estatísticas precisas para as expressões, forçando o planejador a usar uma estimativa ndistinct padrão para a expressão derivada de ndistinct para a coluna. Com estas estatísticas, o planejador reconhece que as condições estão correlacionadas, chegando a estimativas muito mais precisas.

Conformidade

Não existe o comando CREATE STATISTICS no padrão SQL.

Veja também