Respuesta a la pregunta
PostgreSQL mantiene histogramas por columna en pg_statistic y asume independencia estadística entre columnas al estimar la selectividad de filtros de múltiples columnas. Cuando filtras por columnas altamente correlacionadas, como city y state_code o make y model en una base de datos de vehículos, el planificador multiplica las selectividades individuales, subestimando drásticamente el conteo real de filas. Este error de cardinalidad a menudo hace que el optimizador prefiera uniones de Nested Loop sobre uniones de Hash o Merge, resultando en un rendimiento catastrófico en tablas grandes.
Para resolver esto, puedes crear un objeto de estadísticas extendidas usando CREATE STATISTICS, que construye datos de correlación multivariados. Específicamente, el tipo dependencies rastrea dependencias funcionales entre columnas, permitiendo al planificador reconocer que filtrar por state_code = 'CA' ya restringe city a valores de California, evitando el error de multiplicación.
-- Crear estadísticas extendidas para columnas correlacionadas CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- Poblar las estadísticas ANALYZE vehicles;
Situación de la vida real
Una plataforma de logística tuvo problemas con un dashboard de seguimiento de envíos que unía una tabla de shipments de 50 millones de filas contra customers. La consulta filtraba por origin_state y origin_city, donde el 95% de las filas para 'Springfield' como ciudad estaban en realidad en 'IL', pero el planificador asumió que solo el 2% de los envíos cumplían ambos predicados de manera independiente. Estimó 500 filas y eligió una unión de Nested Loop, iterando a través de millones de registros de clientes y agotándose después de 90 segundos.
Una solución considerada fue deshabilitar completamente las uniones de Nested Loop a través de SET enable_nestloop = off en la sesión. Esto forzó una Hash Join y se completó en 3 segundos para esta consulta específica, pero conllevaba graves riesgos: los cambios de configuración global se propagan a través de los grupos de conexiones, lo que podría hacer que otros planes legítimos de Nested Loop que funcionen bien en pequeñas tablas con búsquedas indexadas retrocedieran. Además, este workaround requería cambios en el código a nivel de aplicación para establecer el parámetro antes de consultar.
Otra opción implicó crear un índice compuesto en (origin_city, origin_state). Si bien esto mejoró la selección del índice, no resolvió la subestimación de cardinalidad; el planificador todavía pensaba que pocas filas surgirían del escaneo del índice y mantenía la estrategia de Nested Loop, simplemente ejecutándola más rápido a través del índice cubriente. Además, el amplio índice compuesto consumía 4GB de espacio adicional en disco y ralentizaba las operaciones de escritura en la tabla de shipments de alta velocidad.
El equipo finalmente implementó estadísticas extendidas ejecutando CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, seguido de ANALYZE. Este enfoque no requirió reescribir consultas y añadió una sobrecarga de almacenamiento insignificante. Después de la implementación, el planificador estimó correctamente 45,000 filas y eligió una Hash Join, reduciendo la latencia de la consulta a 400 milisegundos mientras preservaba planes óptimos para cargas de trabajo no relacionadas.
Lo que a menudo pasan por alto los candidatos
¿Cómo refresca el comando ANALYZE las estadísticas extendidas, y por qué podría parecer que un objeto estadístico no se utiliza inmediatamente después de su creación?
ANALYZE calcula estadísticas extendidas solo cuando se invoca explícitamente en la tabla objetivo o cuando el proceso de autovacuum procesa la tabla después de la existencia del objeto de estadísticas. Muchos candidatos asumen que CREATE STATISTICS afecta instantáneamente la planificación, pero las tablas del catálogo pg_statistic_ext y pg_statistic_ext_data permanecen vacías hasta el siguiente ciclo de análisis. En consecuencia, el planificador continúa usando histogramas de una sola columna y suposiciones de independencia hasta que ANALYZE shipments; pobla los datos multivariados. Puedes verificar el uso comprobando la vista pg_stats_ext para valores no nulos de dependencies o ndistinct.
¿Cuál es la diferencia funcional entre dependencies y ndistinct en CREATE STATISTICS, y qué patrones de consulta se benefician de cada uno?
Dependencies capturan relaciones funcionales donde una columna determina otra (por ejemplo, zip_code determina city), corrigiendo directamente las estimaciones de selectividad de la cláusula WHERE. Ndistinct calcula el número exacto de combinaciones distintas para grupos de columnas, lo que mejora las estimaciones de GROUP BY y DISTINCT en lugar de la selectividad de filtro. Los candidatos a menudo confunden estos, creando dependencies cuando su consulta lenta contiene un GROUP BY en columnas correlacionadas, o viceversa. Para obtener resultados óptimos, especifica ambos tipos: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
¿Por qué podrían fallar las estadísticas extendidas en ayudar con consultas que usan condiciones de OR entre columnas correlacionadas?
Las estadísticas extendidas actualmente solo asisten con cláusulas de AND donde ocurre la multiplicación de selectividad. Cuando filtras con OR (por ejemplo, city = 'Springfield' OR state = 'IL'), PostgreSQL calcula la selectividad usando la fórmula P(A) + P(B) - P(A ∩ B), y no puede aplicar coeficientes de dependencia al término de intersección porque las estadísticas rastrean la selectividad conjunta para conjunciones, no disyunciones. Los candidatos frecuentemente pasan por alto esta limitación e intentan usar CREATE STATISTICS para corregir errores de cardinalidad basados en OR, lo que requiere reescritura de consultas (por ejemplo, dividiendo en ramas de UNION ALL) o índices parciales en su lugar.