Descripción

CREATE STATISTICS creará un nuevo objeto de estadísticas extendidas que rastreará datos sobre la tabla, tabla foránea o vista materializada especificada. El objeto de estadísticas se creará en la base de datos actual y será propiedad del usuario que ejecute el comando.

El comando CREATE STATISTICS tiene dos formas básicas. La primera forma permite recopilar estadísticas univariadas para una sola expresión, proporcionando beneficios similares a los de un índice de expresión sin la sobrecarga del mantenimiento de índices. Esta forma no permite especificar el tipo de estadísticas, ya que los diversos tipos de estadísticas se refieren únicamente a estadísticas multivariadas. La segunda forma del comando permite recopilar estadísticas multivariadas sobre múltiples columnas y/o expresiones, especificando opcionalmente qué tipos de estadísticas incluir. Esta forma también hará que se recopilen automáticamente estadísticas univariadas sobre cualquier expresión incluida en la lista.

Si se proporciona un nombre de esquema (por ejemplo, CREATE STATISTICS miesquema.misest ...), el objeto de estadísticas se crea en el esquema especificado. De lo contrario, se crea en el esquema actual. Si se proporciona, el nombre del objeto de estadísticas debe ser distinto del nombre de cualquier otro objeto de estadísticas en el mismo esquema.

Parámetros

Notas

Debes ser el propietario de una tabla para crear un objeto de estadísticas que la lea. Sin embargo, una vez creado, la propiedad del objeto de estadísticas es independiente de la(s) tabla(s) subyacente(s).

Las estadísticas de expresión son por expresión y son similares a crear un índice en la expresión, excepto que evitan la sobrecarga del mantenimiento del índice. Las estadísticas de expresión se construyen automáticamente para cada expresión en la definición del objeto de estadísticas.

El planificador no utiliza actualmente las estadísticas extendidas para las estimaciones de selectividad realizadas para las uniones (joins) de tablas. Esta limitación probablemente se eliminará en una versión futura de PostgreSQL.

Ejemplos

Crea la tabla t1 con dos columnas funcionalmente dependientes, es decir, el conocimiento de un valor en la primera columna es suficiente para determinar el valor en la otra columna. Luego se construyen estadísticas de dependencia funcional en esas columnas:

CREATE TABLE t1 (
    a   int,
    b   int
);

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

ANALYZE t1;

-- el número de filas coincidentes se subestimará drásticamente:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

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

ANALYZE t1;

-- ahora la estimación del recuento de filas es más precisa:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

Sin estadísticas de dependencia funcional, el planificador asumiría que las dos condiciones WHERE son independientes y multiplicaría sus selectividades para llegar a una estimación de recuento de filas demasiado pequeña. Con tales estadísticas, el planificador reconoce que las condiciones WHERE son redundantes y no subestima el recuento de filas.

Crea la tabla t2 con dos columnas perfectamente correlacionadas (que contienen datos idénticos) y una lista MCV en esas columnas:

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;

-- combinación válida (encontrada en MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);

-- combinación no válida (no encontrada en MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);

La lista MCV le da al planificador información más detallada sobre los valores específicos que aparecen comúnmente en la tabla, así como un límite superior en las selectividades de las combinaciones de valores que no aparecen en la tabla, lo que le permite generar mejores estimaciones en ambos casos.

Crea la tabla t3 con una sola columna de marca temporal (timestamp) y ejecuta consultas utilizando expresiones sobre esa columna. Sin estadísticas extendidas, el planificador no tiene información sobre la distribución de datos para las expresiones y utiliza estimaciones por omisión. El planificador tampoco se da cuenta de que el valor de la fecha truncada al mes está completamente determinado por el valor de la fecha truncada al día. Luego se construyen estadísticas de expresión y de n-distintos (ndistinct) sobre esas dos expresiones:

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;

-- el número de filas coincidentes se subestimará drásticamente:
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;

-- construye estadísticas ndistinct en el par de expresiones (las estadísticas
-- por expresión se construyen automáticamente)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

-- ahora las estimaciones de recuento de filas son más 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;

Sin estadísticas de expresión y de n-distintos (ndistinct), el planificador no tiene información sobre el número de valores distintos para las expresiones y tiene que confiar en estimaciones por omisión. Se asume que las condiciones de igualdad y rango tienen una selectividad del 0.5%, y se asume que el número de valores distintos en la expresión es el mismo que para la columna (es decir, único). Esto da como resultado una subestimación significativa del recuento de filas en las dos primeras consultas. Además, el planificador no tiene información sobre la relación entre las expresiones, por lo que asume que las dos condiciones WHERE y GROUP BY son independientes, y multiplica sus selectividades para llegar a una sobreestimación severa del recuento de grupos en la consulta de agregación. Esto se ve agravado por la falta de estadísticas precisas para las expresiones, lo que obliga al planificador a usar una estimación de ndistinct predeterminada para la expresión derivada de ndistinct para la columna. Con tales estadísticas, el planificador reconoce que las condiciones están correlacionadas y llega a estimaciones mucho más precisas.

Compatibilidad

No existe el comando CREATE STATISTICS en el estándar SQL.

Consulte también