PostgreSQL desde cero (IV): índices, EXPLAIN y rendimiento

Cuarta entrega de la serie PostgreSQL desde cero a pro. Tiempo de lectura estimado: 13 minutos.

Tienes un esquema decente (II) y sabes escribir consultas potentes (III). En algún momento, algo irá lento. Este post es sobre cómo averiguar por qué y qué hacer al respecto.

Tres ideas guían todo lo que sigue:

1. Mide antes de optimizar. EXPLAIN ANALYZE es tu única fuente de verdad.
2. Los índices aceleran lecturas y ralentizan escrituras. No los añadas por reflejo.
3. PostgreSQL es autogestionado pero no mágico. ANALYZE y VACUUM son las dos palabras más importantes.

Tipos de índices

PostgreSQL ofrece varios tipos de índice. El 95% de las veces el que quieres es B-tree. Los demás resuelven casos específicos.

B-tree

Por defecto. Soporta igualdad, rangos y ordenación. Sirve para prácticamente todo.

CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX posts_published_at_idx ON posts(published_at DESC);

Índices compuestos

El orden de las columnas importa:

CREATE INDEX posts_author_published_idx ON posts(author_id, published_at DESC);

Este índice ayuda a:

• WHERE author_id = ? (prefijo)
• WHERE author_id = ? AND published_at > ? (prefijo + rango)
• WHERE author_id = ? ORDER BY published_at DESC (sin sort extra)

No ayuda a WHERE published_at > ? por sí solo: el prefijo es author_id.

Regla práctica: igualdad antes que rango. Columnas que filtras por igualdad primero, las de rango al final.

Índices parciales

Solo indexan las filas que cumplen una condición:

CREATE INDEX posts_published_idx
  ON posts(published_at DESC)
  WHERE status = 'published';

Resultado: índice mucho más pequeño, consultas sobre posts publicados mucho más rápidas. Si el 90% son drafts, un índice parcial puede ser 10× más pequeño y proporcionalmente más rápido.

Índices por expresión

Permiten indexar el resultado de una función:

CREATE INDEX users_email_lower_idx ON users(lower(email));

-- Esta consulta usa el índice
SELECT * FROM users WHERE lower(email) = 'a@b.com';

Si tu WHERE aplica una función, necesitas un índice por esa función. Sin él, el índice normal no se usa.

GIN

Para tipos compuestos: arrays, JSONB, full text search, tsvector.

-- Array
CREATE INDEX posts_tags_gin ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

-- JSONB
CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);
SELECT * FROM events WHERE data @> '{"plan":"free"}';

-- Full text search
CREATE INDEX posts_search_gin ON posts USING GIN (search);
SELECT * FROM posts WHERE search @@ plainto_tsquery('postgresql');

GiST

Para tipos geométricos, rangos y búsquedas aproximadas. La extensión btree_gist permite mezclarlo con igualdades (lo vimos en la entrega II con las exclusion constraints).

BRIN

Índice "block range". Muy pequeño, ideal para tablas enormes con datos físicamente correlacionados con la columna indexada (típicamente tablas append-only por fecha).

CREATE INDEX events_created_at_brin ON events USING BRIN (created_at);

BRIN es el patrón para tablas de logs que no caben en RAM: el índice ocupa KBs en lugar de GBs. La contrapartida es que es menos selectivo.

Hash

Antes de PostgreSQL 10 no se recomendaba. Hoy es WAL-loggeado y puede usarse para igualdad estricta de valores grandes. En la práctica, B-tree sigue siendo la elección por defecto salvo casos muy específicos.

Covering indexes

Desde PostgreSQL 11, un índice puede "incluir" columnas adicionales que no son claves pero están disponibles en el índice, permitiendo index-only scans:

CREATE INDEX posts_slug_covering
  ON posts(slug) INCLUDE (title, published_at);

Si la consulta pide solo slug, title y published_at, PostgreSQL puede responder sin tocar la tabla.

EXPLAIN

EXPLAIN muestra el plan de ejecución sin ejecutar la consulta:

EXPLAIN SELECT * FROM posts WHERE author_id = 42;

Salida típica:

Index Scan using posts_author_id_idx on posts  (cost=0.29..8.31 rows=1 width=...)
  Index Cond: (author_id = 42)

• cost=X..Y: coste estimado. No son milisegundos; son "unidades de página" del planner.
• rows=N: filas estimadas.
• width=B: bytes por fila estimados.

Si solo tienes EXPLAIN, estás viendo estimaciones. Para medir de verdad, EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts WHERE author_id = 42;

• ANALYZE: ejecuta la consulta de verdad.
• BUFFERS: añade información sobre páginas leídas (hit/read/dirtied).

Información clave que aparece con ANALYZE:

• actual time=X..Y rows=N loops=M: tiempo real y número de filas por iteración.
• Planning Time y Execution Time: totales al final.

Banderas rojas en un plan

• Seq Scan sobre tabla grande con filtro selectivo: falta un índice adecuado.
• rows estimadas muy distintas de las reales: estadísticas desactualizadas, haz ANALYZE.
• Hash join con Batches: N alto: no cabe en work_mem, se está usando disco.
• Filter vs Index Cond: un Filter se aplica después del scan; un Index Cond lo usa para saltar. Siempre prefieres el segundo.
• Nested Loop sobre muchas filas: a veces el planner se equivoca; revisa estadísticas y costes.

Visualizadores

Los planes se vuelven ilegibles rápido. Herramientas útiles:

• explain.depesz.com — pegas el plan y lo colorea por coste.
• explain.dalibo.com — visualización de árbol con detalles.
• pev2 — versión moderna de lo anterior.

Para un plan que te está volviendo loco, pegarlo ahí suele dar el "ajá" en segundos.

ANALYZE y estadísticas

PostgreSQL elige planes basándose en estadísticas sobre la distribución de los datos: cardinalidad, correlación física, histogramas, most common values (MCV).

Esas estadísticas las mantiene ANALYZE (y el autovacuum, que las actualiza automáticamente). Si cargas muchos datos de golpe, lanza ANALYZE a mano:

ANALYZE posts;
ANALYZE;   -- toda la base de datos

Para columnas con correlaciones o distribuciones difíciles, puedes crear estadísticas extendidas:

CREATE STATISTICS posts_author_status (dependencies, ndistinct)
  ON author_id, status FROM posts;
ANALYZE posts;

Esto le enseña al planner que author_id y status no son independientes, que es lo que suele romper las estimaciones con AND de columnas correlacionadas.

VACUUM y autovacuum

PostgreSQL usa MVCC: cada UPDATE o DELETE deja la fila vieja como "muerta" hasta que VACUUM la limpia. Si el autovacuum no da abasto, la tabla se infla y las consultas se degradan.

Síntomas de bloat:

• Tabla mucho más grande que la suma de sus filas.
• EXPLAIN ANALYZE muestra muchas más páginas leídas de las esperadas.
• El autovacuum_worker sale disparado en logs.

Diagnóstico rápido:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Si ves tablas con más del 20% de tuplas muertas y last_autovacuum antiguo, hay trabajo.

Ajustes típicos para tablas con mucha escritura:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

El autovacuum se dispara cuando el porcentaje de tuplas muertas supera scale_factor * n_live_tup + vacuum_threshold. Bajar el scale_factor en tablas calientes evita el bloat.

VACUUM FULL recompacta pero bloquea la tabla entera. En producción, casi nunca. Alternativas: pg_repack, pg_squeeze.

Parámetros de servidor que más se notan

Estos son los parámetros de postgresql.conf que marcan la diferencia real:

• shared_buffers: memoria de caché compartida. Regla de pulgar: 25% de la RAM total.
• effective_cache_size: lo que el planner cree que está disponible entre shared_buffers y el page cache del kernel. 50–75% de la RAM.
• work_mem: memoria por operación de ordenación/hash. Empieza por 16–64 MB, súbelo con cautela (se multiplica por conexiones × operaciones por consulta).
• maintenance_work_mem: para VACUUM, CREATE INDEX, REINDEX. 256 MB – 2 GB.
• max_connections: bajo, mejor. Usa un pool (PgBouncer) antes que 1000 conexiones.
• random_page_cost: 1.1 en SSD (el default 4 asume HDD).
• effective_io_concurrency: 200 en SSD, 1 en HDD.
• wal_compression: on si tienes CPU sobrada (ahorra WAL).
• checkpoint_timeout y max_wal_size: afectan a la carga de I/O. Ajústalos si ves "checkpoints occurring too frequently" en logs.

Y en el servicio de sistema, casi siempre:

• huge_pages = try y configurar vm.nr_hugepages en el SO para tablas grandes.
• vm.swappiness = 10.

Herramientas de diagnóstico continuo

pg_stat_statements

Extensión (viene con PostgreSQL) que guarda estadísticas de cada consulta normalizada: llamadas totales, tiempo medio, filas.

CREATE EXTENSION pg_stat_statements;

-- Top 20 consultas por tiempo total
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Es la manera más rápida de encontrar la consulta que lo está petando.

Tablas de sistema más útiles

-- Consultas activas ahora mismo
SELECT pid, state, wait_event, age(clock_timestamp(), query_start) AS dur,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY dur DESC;

-- Bloqueos
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Uso de índices
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;   -- los menos usados

Índices con idx_scan = 0 durante meses son candidatos a eliminar: ocupan espacio y ralentizan escrituras para nada.

Connection pooling

Nunca dejes que una app abra conexiones directas a PostgreSQL en producción. Pon PgBouncer (o similar) delante. Modos:

• session: connection pool clásico.
• transaction: reutiliza conexión por transacción. Mucho más eficiente pero rompe ciertas features de sesión.
• statement: reutiliza por consulta. Solo para patrones muy específicos.

El impacto es enorme: 5000 conexiones lógicas de aplicación pueden respaldarse con 20 conexiones reales a PostgreSQL.

Una receta de depuración

Cuando una consulta va lenta, el orden que sigo:

1. EXPLAIN ANALYZE (BUFFERS) y mirar si las estimaciones cuadran con la realidad.
2. Si no cuadran: ANALYZE, y si sigue sin cuadrar, estadísticas extendidas.
3. Buscar Seq Scan sobre tabla grande → índice que falta.
4. Buscar Filter que se aplica tras el scan → índice por expresión o parcial.
5. Buscar Sort en memoria grande → work_mem insuficiente o ORDER BY reemplazable por índice.
6. Buscar joins con rows muy sobreestimadas → reformular o forzar join type.
7. Si todo está bien y sigue siendo lento por volumen: agregados precalculados (materialized views, resúmenes incrementales).

Si los agregados precalculados empiezan a doler, quizá sea momento de un motor analítico al lado. Lo vimos en la serie ClickHouse, especialmente el post comparativo.

Por dónde seguir

• V: replicación, backups y producción — último tramo: operar PostgreSQL con garantías.

Aterrizando tarde:

• I: instalación, psql y primeros pasos
• II: tipos, restricciones y relaciones
• III: CTEs, window functions y consultas avanzadas