- 14.2.1. Estadísticas de una sola columna
- 14.2.2. Estadísticas extendidas
14.2.1. Estadísticas de una sola columna
Como vimos en la sección anterior, el planificador de consultas necesita estimar el número de filas recuperadas por una consulta para poder hacer buenas elecciones de planes de consulta. Esta sección proporciona un vistazo rápido a las estadísticas que utiliza el sistema para estas estimaciones.
Un componente de las estadísticas es el número total de entradas en cada tabla e índice, así como el número de bloques de disco ocupados por cada tabla e índice. Esta información se mantiene en la tabla. pg_class, en las columnas reltuples y relpages. Podemos mirarlo con consultas similares a esta:
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(5 rows)
Aquí podemos ver que tenk1 contiene 10000 filas, al igual que sus índices, pero los índices son (como era de esperar) mucho más pequeños que la tabla.
Por razones de eficiencia, reltuples y relpages no se actualizan sobre la marcha, por lo que suelen contener valores algo desactualizados. Son actualizados por VACUUM, ANALYZEy algunos comandos DDL como CREATE INDEX. A VACUUM o ANALYZE operación que no escanea toda la tabla (que es comúnmente el caso) actualizará incrementalmente la reltuples cuente sobre la base de la parte de la tabla que escaneó, lo que da como resultado un valor aproximado. En cualquier caso, el planificador escalará los valores que encuentre en pg_class para que coincida con el tamaño de la tabla física actual, obteniendo así una aproximación más cercana.
La mayoría de las consultas recuperan solo una fracción de las filas de una tabla, debido a WHERE cláusulas que restringen las filas a examinar. Por tanto, el planificador necesita hacer una estimación de la selectividad de WHERE cláusulas, es decir, la fracción de filas que coinciden con cada condición en el WHERE cláusula. La información utilizada para esta tarea se almacena en el pg_statistic catálogo del sistema. Entradas en pg_statistic son actualizados por el ANALYZE y VACUUM ANALYZE comandos, y son siempre aproximados incluso cuando se actualizan recientemente.
En lugar de mirar pg_statistic directamente, es mejor mirar su vista pg_stats al examinar las estadísticas manualmente. pg_stats está diseñado para ser más fácil de leer. Es más, pg_stats es legible para todos, mientras que pg_statistic solo es legible por un superusuario. (Esto evita que los usuarios sin privilegios aprendan algo sobre el contenido de las tablas de otras personas a partir de las estadísticas. pg_stats La vista está restringida para mostrar solo filas sobre tablas que el usuario actual puede leer). Por ejemplo, podríamos hacer:
SELECT attname, inherited, n_distinct,
array_to_string(most_common_vals, E'n') as most_common_vals
FROM pg_stats
WHERE tablename="road";
attname | inherited | n_distinct | most_common_vals
---------+-----------+------------+------------------------------------
name | f | -0.363388 | I- 580 Ramp+
| | | I- 880 Ramp+
| | | Sp Railroad +
| | | I- 580 +
| | | I- 680 Ramp
name | t | -0.284859 | I- 880 Ramp+
| | | I- 580 Ramp+
| | | I- 680 Ramp+
| | | I- 580 +
| | | State Hwy 13 Ramp
(2 rows)
Tenga en cuenta que se muestran dos filas para la misma columna, una correspondiente a la jerarquía de herencia completa comenzando en el road mesa (inherited=t), y otro que incluye solo el road la mesa en síinherited=f).
La cantidad de información almacenada en pg_statistic por ANALYZE, en particular el número máximo de entradas en el most_common_vals y histogram_bounds matrices para cada columna, se pueden configurar columna por columna utilizando el ALTER TABLE SET STATISTICS comando, o globalmente estableciendo la variable de configuración default_statistics_target. El límite predeterminado es actualmente de 100 entradas. El aumento del límite podría permitir que se realicen estimaciones de planificador más precisas, en particular para columnas con distribuciones de datos irregulares, al precio de consumir más espacio en pg_statistic y un poco más de tiempo para calcular las estimaciones. Por el contrario, un límite inferior podría ser suficiente para columnas con distribuciones de datos simples.
Se pueden encontrar más detalles sobre el uso de las estadísticas por parte del planificador en Capitulo 70.
14.2.2. Estadísticas extendidas
Es común ver consultas lentas que ejecutan planes de ejecución incorrectos porque varias columnas utilizadas en las cláusulas de consulta están correlacionadas. El planificador normalmente asume que múltiples condiciones son independientes entre sí, una suposición que no se cumple cuando los valores de las columnas están correlacionados. Las estadísticas regulares, debido a su naturaleza por columna individual, no pueden capturar ningún conocimiento sobre la correlación entre columnas. Sin embargo, PostgreSQL tiene la capacidad de calcular estadística multivariante, que puede capturar dicha información.
Debido a que el número de posibles combinaciones de columnas es muy grande, no es práctico calcular estadísticas multivariadas automáticamente. En lugar de, objetos de estadísticas extendidas, más a menudo llamado simplemente objetos estadísticos, se puede crear para indicar al servidor que obtenga estadísticas en conjuntos de columnas interesantes.
Los objetos de estadísticas se crean utilizando el comando CREAR ESTADÍSTICAS. La creación de tal objeto simplemente crea una entrada de catálogo que expresa interés en las estadísticas. La recopilación de datos real la realiza ANALYZE (ya sea un comando manual o un análisis automático en segundo plano). Los valores recopilados se pueden examinar en el pg_statistic_ext_data catalogar.
ANALYZE calcula estadísticas extendidas basadas en la misma muestra de filas de la tabla que toma para calcular las estadísticas regulares de una sola columna. Dado que el tamaño de la muestra aumenta al aumentar el objetivo de estadísticas para la tabla o cualquiera de sus columnas (como se describe en la sección anterior), un objetivo de estadísticas más grande normalmente dará como resultado estadísticas extendidas más precisas, así como más tiempo para calcularlas.
Las siguientes subsecciones describen los tipos de estadísticas extendidas que se admiten actualmente.
14.2.2.1. Dependencias funcionales
El tipo más simple de pistas de estadísticas extendidas dependencias funcionales, un concepto utilizado en las definiciones de las formas normales de bases de datos. Decimos esa columna b es funcionalmente dependiente de la columna a si el conocimiento del valor de a es suficiente para determinar el valor de b, es decir, no hay dos filas que tengan el mismo valor de a pero diferentes valores de b. En una base de datos completamente normalizada, las dependencias funcionales deben existir solo en claves primarias y superclaves. Sin embargo, en la práctica, muchos conjuntos de datos no están completamente normalizados por varias razones; La desnormalización intencional por motivos de rendimiento es un ejemplo común. Incluso en una base de datos completamente normalizada, puede haber una correlación parcial entre algunas columnas, que se puede expresar como dependencia funcional parcial.
La existencia de dependencias funcionales afecta directamente la precisión de las estimaciones en determinadas consultas. Si una consulta contiene condiciones en las columnas independientes y dependientes, las condiciones en las columnas dependientes no reducen más el tamaño del resultado; pero sin el conocimiento de la dependencia funcional, el planificador de consultas asumirá que las condiciones son independientes, resultando en subestimar el tamaño del resultado.
Para informar al planificador sobre las dependencias funcionales, ANALYZE puede recopilar medidas de dependencia entre columnas. Evaluar el grado de dependencia entre todos los conjuntos de columnas sería prohibitivamente costoso, por lo que la recopilación de datos se limita a los grupos de columnas que aparecen juntas en un objeto estadístico definido con el dependencies opción. Es recomendable crear dependencies estadísticas solo para grupos de columnas que están fuertemente correlacionados, para evitar gastos generales innecesarios en ambos ANALYZE y posterior planificación de consultas.
A continuación, se muestra un ejemplo de recopilación de estadísticas de dependencia funcional:
CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes; ANALYZE zipcodes; SELECT stxname, stxkeys, stxddependencies FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts'; stxname | stxkeys | stxddependencies ---------+---------+------------------------------------------ stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130} (1 row)
Aquí se puede ver que la columna 1 (código postal) determina completamente la columna 5 (ciudad) por lo que el coeficiente es 1.0, mientras que la ciudad solo determina el código postal alrededor del 42% del tiempo, lo que significa que hay muchas ciudades (58%) que son representado por más de un código postal.
Al calcular la selectividad para una consulta que involucra columnas funcionalmente dependientes, el planificador ajusta las estimaciones de selectividad por condición utilizando los coeficientes de dependencia para no producir una subestimación.
14.2.2.1.1. Limitaciones de las dependencias funcionales
Actualmente, las dependencias funcionales solo se aplican cuando se consideran condiciones de igualdad simples que comparan columnas con valores constantes, y IN cláusulas con valores constantes. No se utilizan para mejorar las estimaciones de condiciones de igualdad comparando dos columnas o comparando una columna con una expresión, ni para cláusulas de rango, LIKE o cualquier otro tipo de condición.
Al estimar con dependencias funcionales, el planificador asume que las condiciones en las columnas involucradas son compatibles y, por lo tanto, redundantes. Si son incompatibles, la estimación correcta sería cero filas, pero esa posibilidad no se considera. Por ejemplo, dada una consulta como
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
el planificador ignorará el city cláusula como no cambiar la selectividad, lo cual es correcto. Sin embargo, hará la misma suposición sobre
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
aunque realmente no habrá filas que satisfagan esta consulta. Sin embargo, las estadísticas de dependencia funcional no proporcionan suficiente información para concluir eso.
En muchas situaciones prácticas, esta suposición suele satisfacerse; por ejemplo, puede haber una GUI en la aplicación que solo permite seleccionar valores de ciudad y código postal compatibles para usar en una consulta. Pero si ese no es el caso, las dependencias funcionales pueden no ser una opción viable.
14.2.2.2. Recuentos N-Distintos multivariados
Las estadísticas de una sola columna almacenan el número de valores distintos en cada columna. Estimaciones del número de valores distintos al combinar más de una columna (por ejemplo, para GROUP BY a, b) se equivocan con frecuencia cuando el planificador solo tiene datos estadísticos de una sola columna, lo que hace que seleccione planes incorrectos.
Para mejorar tales estimaciones, ANALYZE puede recopilar n estadísticas distintas para grupos de columnas. Como antes, no es práctico hacer esto para cada posible agrupación de columnas, por lo que los datos se recopilan solo para aquellos grupos de columnas que aparecen juntas en un objeto de estadísticas definido con el ndistinct opción. Se recopilarán datos para cada posible combinación de dos o más columnas del conjunto de columnas enumeradas.
Continuando con el ejemplo anterior, los recuentos n distintos en una tabla de códigos postales podrían tener el siguiente aspecto:
CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes; ANALYZE zipcodes; SELECT stxkeys AS k, stxdndistinct AS nd FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts2'; -[ RECORD 1 ]-------------------------------------------------------- k | 1 2 5 nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178} (1 row)
Esto indica que hay tres combinaciones de columnas que tienen 33178 valores distintos: código postal y estado; Código postal y ciudad; y código postal, ciudad y estado (se espera que todos sean iguales dado que el código postal solo es único en esta tabla). Por otro lado, la combinación de ciudad y estado tiene solo 27435 valores distintos.
Es recomendable crear ndistinct Estadísticas de objetos solo en combinaciones de columnas que se utilizan realmente para la agrupación y para las que una estimación errónea del número de grupos genera malos planes. De lo contrario, el ANALYZE los ciclos simplemente se desperdician.
14.2.2.3. Listas de MCV multivariante
Otro tipo de estadísticas almacenadas para cada columna son las listas de valores más comunes. Esto permite estimaciones muy precisas para columnas individuales, pero puede resultar en estimaciones erróneas significativas para consultas con condiciones en varias columnas.
Para mejorar tales estimaciones, ANALYZE puede recopilar listas de MCV en combinaciones de columnas. De manera similar a las dependencias funcionales y los coeficientes n distintos, no es práctico hacer esto para cada posible agrupación de columnas. Más aún en este caso, ya que la lista MCV (a diferencia de las dependencias funcionales y los coeficientes n distintos) almacena los valores de columna comunes. Por lo tanto, los datos se recopilan solo para aquellos grupos de columnas que aparecen juntas en un objeto de estadísticas definido con el mcv opción.
Continuo En el ejemplo anterior, la lista de MCV para una tabla de códigos postales podría tener el siguiente aspecto (a diferencia de los tipos de estadísticas más simples, se requiere una función para la inspección del contenido de MCV):
CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes; ANALYZE zipcodes; SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3'; index | values | nulls | frequency | base_frequency -------+------------------------+-------+-----------+---------------- 0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05 1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05 2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133 3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113 4 | {New York, NY} | {f,f} | 0.001967 | 0.000114 5 | {Atlanta, GA} | {f,f} | 0.001633 | 3.3e-05 6 | {Sacramento, CA} | {f,f} | 0.001433 | 7.8e-05 7 | {Miami, FL} | {f,f} | 0.0014 | 6e-05 8 | {Dallas, TX} | {f,f} | 0.001367 | 8.8e-05 9 | {Chicago, IL} | {f,f} | 0.001333 | 5.1e-05 ... (99 rows)
Esto indica que la combinación más común de ciudad y estado es Washington en DC, con una frecuencia real (en la muestra) de aproximadamente 0.35%. La frecuencia base de la combinación (calculada a partir de las frecuencias simples por columna) es solo del 0,0027%, lo que resulta en subestimaciones de dos órdenes de magnitud.
Es aconsejable crear objetos de estadísticas MCV solo en combinaciones de columnas que realmente se usan juntas en condiciones, y para las cuales una estimación errónea del número de grupos da como resultado planes incorrectos. De lo contrario, el ANALYZE y los ciclos de planificación simplemente se desperdician.
|
Anterior |
Hasta | próximo |
14.1. Utilizando EXPLAIN |
Hogar | 14.3. Controlar el planificador con explícito JOIN Cláusulas |