Cómo PostgreSQL estima tus consultas (y por qué a veces se equivoca)

Toda consulta empieza con un plan. Toda consulta lenta probablemente empieza con uno malo. Y más a menudo de lo que crees, las estadísticas son las culpables.

Pero ¿cómo funciona realmente? PostgreSQL no ejecuta la consulta para averiguarlo — estima el coste. Lee datos precalculados de pg_class y pg_statistic y hace los cálculos para encontrar la ruta más barata hacia tus datos.

En el escenario ideal, los números que lee son precisos y obtienes el plan que esperas. Pero cuando están desactualizados, la situación se descontrola. El planificador estima 500 filas, planifica un nested loop, y se encuentra con 25,000. Lo que parecía un plan óptimo se convierte en una falla en cascada.

¿Cómo se vuelven obsoletas las estadísticas?

Puede ser una carga masiva de datos, una migración de esquema, un crecimiento más rápido de lo esperado, o simplemente que VACUUM no da abasto. Sea cual sea la causa, el resultado es el mismo: el planificador está volando a ciegas, eligiendo rutas basadas en una realidad que ya no existe.

Vamos a profundizar en los dos catálogos de los que depende el planificador, entender qué obtiene realmente ANALYZE de una tabla de 30,000 filas, y ver cómo esos números determinan si tu consulta toma milisegundos o minutos.

Qué lee el planificador

Cada decisión que toma el planificador se basa en dos fuentes:

• Metadatos a nivel de tabla desde pg_class
• Metadatos a nivel de columna desde pg_statistic

pg_class — estadísticas a nivel de relación

En realidad rastrea todas las relaciones: no solo tablas e índices, sino también particiones, tablas TOAST, secuencias, tipos compuestos y vistas.

Cada tabla, índice y materialized view tiene una fila en pg_class. Antes de mirar las estadísticas a nivel de columna, el planificador lee tres valores clave:

SELECT relname, relpages, reltuples, relallvisible
FROM pg_class
WHERE relname = 'orders';

relname | relpages | reltuples | relallvisible
---------+----------+-----------+---------------
orders  |      856 |    100000 |           856

El planificador ve 100,000 filas distribuidas en 856 páginas. Cada estimación de coste empieza desde esos dos números. relpages determina el coste del escaneo secuencial — cada página es una unidad de trabajo de E/S configurada vía seq_page_cost. reltuples controla las estimaciones para joins, agregaciones y básicamente todo lo demás.

El valor reltuples es solo una estimación, no un recuento en vivo. Se actualiza mediante ANALYZE (y autovacuum), no por INSERTs o DELETEs individuales. Entre ejecuciones de ANALYZE, PostgreSQL escala reltuples proporcionalmente cuando cambia el valor de relpages — si la tabla crece un 10% en páginas, el planificador asume también un 10% más de filas.

Esto funciona razonablemente bien para crecimiento normal, pero se rompe con el bloat. Si las tuplas muertas están inflando el número de páginas usadas sin añadir filas reales, el planificador sobreestima el tamaño de la tabla.

pg_statistic (via pg_stats) — estadísticas a nivel de columna

Saber el tamaño de una tabla es solo la mitad de la imagen. Para estimar cuántas filas podrían coincidir, el planificador necesita entender los datos dentro de cada columna. PostgreSQL mantiene estadísticas en el catálogo pg_statistic, aunque en la práctica usarás la vista pg_stats que presenta los datos de forma amigable.

Los valores más interesantes que expone son:

SELECT attname, null_frac, avg_width, n_distinct,
       most_common_vals, most_common_freqs, histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

attname      | status
null_frac    | 0
avg_width    | 8
n_distinct   | 4
most_common_vals | {pending,shipped,delivered,cancelled}
most_common_freqs | {0.25396666,0.25,0.24973333,0.2463}
histogram_bounds |
correlation  | 0.2524199

A partir de esto, el planificador sabe que hay exactamente 4 valores distintos, más o menos distribuidos equitativamente, sin ningún valor NULL. Cuando escribes un predicado WHERE status = 'pending', estimará que ~25% de las filas coincidirán. Todo ello sin ejecutar una consulta, simplemente leyendo la fila del catálogo.

Selectividad en acción

Ahora que cubrimos qué datos tiene disponibles el planificador, veamos cómo los usa para estimar cuántas filas leerá una parte de una consulta. Este “adivino” se llama Selectividad, y se define como un número flotante entre 0 y 1.

La fórmula es bastante simple:

Filas Estimadas = Total de Filas × Selectividad

Pero la forma en que se calcula la Selectividad depende enteramente del operador que uses (=, <, > o LIKE).

Igualdad (Most Common Values)

La igualdad es lo más fácil de empezar. Cuando usas WHERE status = 'shipped', el planificador primero revisa la lista most_common_vals (MCV). Si hay coincidencia, la selectividad es la misma que la proporcionada por most_common_freqs.

Si el valor no está en la lista, el planificador asume que el valor es parte de la población “restante”. Resta todas las frecuencias MCV de 1.0 y divide el residuo entre el número de otros valores distintos.

Búsqueda por rango (el histograma)

La vida sería fácil si solo buscáramos valores exactos. La mayor parte del tiempo necesitamos búsqueda por rango, por ejemplo WHERE amount > 400.

Los MCVs son inútiles en este caso, ya que podría haber miles o millones de constantes únicas. Aquí es donde entra histogram_bounds. PostgreSQL divide los valores de la columna en un número de buckets, donde cada bucket contiene el mismo número de filas (no de valores).

La Selectividad en este caso se determina por cuántos buckets cubre tu consulta. Si tenemos límites (100, 200, 300, 400, 500, 600), el planificador establecerá que cubre 2 buckets completos. Como hay 5 buckets en total, la Selectividad será 0.4 (2/5).

La debilidad mayor del histograma es la interpolación lineal. Si tus datos tienen picos masivos en la distribución, el planificador siempre asumirá una distribución perfecta.

Búsqueda y coincidencia de patrones

Este es probablemente el territorio más traicionero para el planificador. Para patrones de coincidencia de subcadenas como WHERE note LIKE '%middle%', no hay histograma ni lista de valores en la que confiar. El planificador debe recurrir a “constantes mágicas” hardcodeadas en el código fuente de PostgreSQL.

El valor por defecto para patrones genéricos es 0.5% del total de filas, definido como #define DEFAULT_MATCH_SEL 0.005.

Una situación ligeramente mejor ocurre para coincidencias con prefijo como WHERE note LIKE 'boringSQL%', donde PostgreSQL puede recurrir a condiciones de rango y usar los límites del histograma. Aunque es una diferencia sutil, marca la diferencia entre el día y la noche.

Correlación y coste de escaneo de índice

¿Recuerdas correlation de pg_stats? Indica qué tan closely el orden físico de las filas en disco coincide con el orden lógico de los valores de la columna. Valores cercanos a 1.0 significan alta correlación; valores cercanos a 0 significan que los datos están distribuidos aleatoriamente.

Esto importa porque determina si un escaneo de índice vale la pena. El planificador asume que una lectura aleatoria de página cuesta 4× más que una secuencial (random_page_cost = 4.0 vs seq_page_cost = 1.0). Cuando la correlación es alta, las filas a las que apunta un índice están físicamente adyacentes. El planificador espera E/S secuencial y costea el escaneo barato. Cuando la correlación es baja, cada búsqueda probablemente golpea una página diferente, y el planificador costea cada una de esas lecturas a la tasa aleatoria superior.

Esa diferencia por sí sola puede ser suficiente para hacer que un escaneo secuencial sea más barato que un escaneo de índice.

¿Qué pasa si no hay estadísticas?

Hasta ahora hemos tocado por qué las estadísticas actualizadas son un debe. Pero ¿qué pasa si no hay estadísticas en absoluto? Por ejemplo, para una tabla nueva o columna nueva cuando ANALYZE aún no se ha ejecutado.

En esos casos, PostgreSQL recurre a valores por defecto hardcodeados:

Nada que un rápido ANALYZE no pueda arreglar, ¿correcto? O tal vez no.

Donde no van las estadísticas

Aunque este artículo se centra en estadísticas y obtenerlas correctamente, hay situaciones donde no habrá estadísticas disponibles (nunca o de forma predecible):

• CTEs y subconsultas cuando no están inline/materializadas no tienen estadísticas
• Tablas temporales no son tocadas por autovacuum, así que no hay ANALYZE automático
• Tablas foráneas no garantizan que las estadísticas se propaguen
• Y, para gran sorpresa, expresiones computadas en WHERE como WHERE amount * 1.1 > 500 o lower(email) = 'hola@ejemplo.com'; a menos que crees un índice de expresión o estadísticas extendidas

Y por cierto, ¿sabías que TRUNCATE es una forma rápida de deshacerse de los datos, pero deja las estadísticas atrás?

Cómo funciona ANALYZE

Como ya mencionamos varias veces, ANALYZE es el único mecanismo que pobla pg_class y pg_statistic con datos frescos. Entender qué muestrea, qué computa y qué se pierde es clave para entender por qué las estadísticas a veces están equivocadas.

El mecanismo de muestreo

ANALYZE en realidad no lee la tabla completa. Muestrea lo que se considera un tamaño mínimo de muestra estadísticamente justificado (definido como 300 para el algoritmo de muestreo de reservorio). Para PostgreSQL, eso significa 300 × default_statistics_target filas.

SHOW default_statistics_target;

default_statistics_target
---------------------------
100

Con el target por defecto de 100, son 30,000 filas. Para nuestra tabla orders de 100,000 filas, ANALYZE lee alrededor del 30% de los datos. Para una tabla de 50 millones de filas, lee el 0.06%.

El muestreo es de dos etapas. Primero, ANALYZE selecciona un conjunto aleatorio de páginas. Luego lee todas las filas vivas de esas páginas. Esto da una sección transversal representativa sin leer cada página.

Computar estadísticas

Una vez que ANALYZE tiene sus 30,000 filas de muestra (considerando los valores por defecto), procesa cada columna independientemente. El pipeline para una sola columna se ve más o menos así:

Primero, cuenta NULLs y calcula null_frac y avg_width — las estadísticas más baratas de computar. Luego ordena los valores no nulos y construye la lista MCV contando duplicados. Los valores que aparecen con suficiente frecuencia pasan el corte; el resto se pasa al constructor del histograma, que los divide en buckets de igual población. Finalmente, porque los valores ya están ordenados, ANALYZE compara el orden de clasificación lógico contra las posiciones físicas de las tuplas (de qué página vino cada fila) para calcular la correlation.

El detalle clave aquí es que MCVs e histogramas no se construyen desde el mismo pool. Los valores que aterrizan en most_common_vals se excluyen de histogram_bounds. Es por eso que a veces verás una columna con MCVs pero sin histograma, o un histograma pero sin MCVs. Representan diferentes rebanadas de los mismos datos.

Controlando la calidad de las estadísticas

El target por defecto de 100 funciona bien para la mayoría de columnas. Significa hasta 100 MCVs, 101 límites de histograma, y una muestra de 30,000 filas. Incrementarlo ayuda cuando:

• Una columna tiene muchos valores distintos y los top 100 no cubren suficiente de la distribución
• Consultas de rango en datos sesgados producen malas estimaciones porque los buckets del histograma son demasiado gruesos
• Las estimaciones de join están equivocadas porque n_distinct es inexacto

El coste escala linealmente. Configurarlo a 1000 significa 300,000 filas muestreadas, hasta 1000 MCVs, más almacenamiento de catálogo, y planificación más lenta por arrays más grandes para buscar. El máximo es 10,000.

No tienes que subirlo globalmente. Para una sola columna problemática:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Ahora status obtiene hasta 500 MCVs y 501 límites de histograma, mientras cada otra columna se queda en 100.

Estadísticas extendidas

Las estadísticas estándar tratan cada columna independientemente. Esto significa que el planificador no puede saber que city = 'Edinburgh' y country = 'UK' están correlacionados. Multiplica sus selectivities independientemente, potencialmente subestimando por órdenes de magnitud.

Las estadísticas extendidas solucionan esto para combinaciones específicas de columnas:

CREATE STATISTICS orders_status_date (dependencies, ndistinct, mcv)
ON status, created_at FROM orders;
ANALYZE orders;

Esto le dice a ANALYZE que compute dependencias funcionales, recuentos distintos combinados, y MCVs combinados entre las columnas. El planificador puede entonces usarlos para evitar la asunción de independencia para consultas filtrando en ambas columnas.

Los tres tipos de estadísticas extendidas sirven propósitos diferentes:

• dependencies capturan dependencias funcionales entre columnas. Ayuda cuando conocer el valor de una columna determina o estrecha otra (ej: zip_code determina mayormente city)
• ndistinct rastrea el número de combinaciones de valores distintos a través de columnas. Ayuda con GROUP BY en múltiples columnas donde el planificador multiplicaría recuentos distintos independientemente
• mcv construye una lista de valores más comunes combinados para tuplas de columnas. La opción más poderosa pero más costosa. Ayuda con condiciones WHERE multi-columna en valores correlacionados

Diagnosticando malas estimaciones

Cuando una consulta es lenta, la primera pregunta siempre debería ser: ¿estimó correctamente el planificador? Compara la estimación con la realidad usando EXPLAIN ANALYZE.

Una estimación desviada por un puñado de filas significa que las estadísticas están bien. Pero cuando ves estimaciones desviadas por 10× o más, es donde la planificación sale mal. Un nested loop que parece barato para 100 filas se convierte en un desastre a 10,000.

Las estadísticas te dicen qué creyó el planificador, y comparar eso con la realidad te dice qué hacer a continuación. Ejecuta ANALYZE, considera ajustar el target de estadísticas para una columna específica, o crea estadísticas extendidas si múltiples columnas están involucradas.

El planificador es tan bueno como lo que lee del catálogo. Cuando las estimaciones salen mal, no culpes al planificador. Revisa los datos con los que está trabajando.