Optimización de consultas en PostgreSQL: índices, EXPLAIN ANALYZE y tácticas prácticas

Este artículo va directo al grano: cómo identificar consultas lentas en PostgreSQL, interpretar el plan con EXPLAIN ANALYZE, y aplicar índices y cambios prácticos que realmente mejoran el rendimiento. Si trabajas con tablas grandes y consultas críticas, estos patrones te harán ahorrar tiempo de ejecución y CPU.

1) Diagnóstico rápido: encontrar la consulta problemática

Activa y examina las consultas reales. Si usas pg_stat_statements puedes ver latencias y frecuencias:

-- habilitar en postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- luego en SQL
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Para casos puntuales, ejecuta la consulta con EXPLAIN (ANALYZE, BUFFERS) para obtener tiempos reales y uso de IO:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'paid';

2) Entender el plan: términos clave

• Seq Scan: lee toda la tabla - malo en tablas grandes si la selectividad es alta.
• Index Scan: usa un índice - ideal para selecciones con buena selectividad.
• Bitmap Heap Scan: combina múltiples index scans o aglutina resultados antes de leer la tabla.
• Buffers: te dice cuántos bloques se leyeron de cache y disco; útil para cálculo de IO real.

3) Ejemplo: tabla y consulta problemática

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  total numeric(12,2),
  created_at timestamptz NOT NULL
);

-- consulta típica
SELECT id, total FROM orders
WHERE customer_id = 12345
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Si ejecutas esto y ves un Seq Scan, el siguiente paso es añadir índices relevantes.

4) Índices prácticos y cuándo usarlos

Regla general: crea el índice que coincida con tu WHERE/ORDER BY y que tenga alta selectividad. Algunas opciones:

• Índice simple para búsquedas por customer_id:

CREATE INDEX idx_orders_customer ON orders(customer_id);

• Índice compuesto con ORDER BY: si la consulta filtra por customer_id y ordena por created_at, pon created_at como segunda columna en ese orden:

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

Con ese índice PostgreSQL suele evitar un sort adicional y hace un index scan eficiente que satisface el ORDER BY y el LIMIT.

• Índice parcial: si solo consultas registros con status = 'paid' muy a menudo, un índice parcial reduce tamaño y costo:

CREATE INDEX idx_orders_customer_paid ON orders(customer_id, created_at DESC)
WHERE status = 'paid';

• Índice de expresión para búsquedas con funciones (ej. lower):

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- consulta debe usar lower(email) para aprovecharlo
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

• INCLUDE / covering index (Postgres 11+): evita el fetch de la fila si el índice contiene las columnas necesarias:

CREATE INDEX idx_orders_cover ON orders(customer_id, created_at DESC) INCLUDE (total, id);
-- ahora la consulta puede ser resuelta solo desde el índice

5) Casos donde el índice no se usa (y cómo actuar)

• Si la columna tiene baja selectividad (boolean, tiny enum), el planner puede preferir Seq Scan.
• Si la condición usa una función y no hay índice de expresión correspondiente.
• Si la estadística está desactualizada; ejecuta ANALYZE o VACUUM ANALYZE.
• Si el orden de columnas en un índice multicolumna no coincide con la forma de filtrar/ordenar.

-- actualizar estadísticas
VACUUM ANALYZE orders;
-- forzar temporalmente al planner (solo para pruebas)
SET enable_seqscan = OFF; 
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = ON;

Advertencia: no dejes enable_seqscan deshabilitado en producción.

6) Mantenimiento y tuning

• VACUUM / AUTOVACUUM: crucial para evitar bloat en tablas/índices.
• REINDEX / pg_repack: si el índice está fragmentado o grande por bloat.
• FILLFACTOR: para tablas con muchas actualizaciones, disminuir fillfactor reduce page split

ALTER TABLE orders SET (fillfactor = 70);
-- o al crear índice
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id) WITH (fillfactor = 70);

7) Índices especiales

• GIN para jsonb, tsvector, arrays: excelente para búsquedas de texto/contención.
• BRIN para tablas muy grandes donde los datos están naturalmente ordenados (logs, series temporales).

-- ejemplo JSONB
CREATE INDEX idx_docs_data ON docs USING gin (data jsonb_path_ops);
-- BRIN para created_at en tablas append-only
CREATE INDEX idx_big_log_created ON big_log USING brin (created_at);

8) Medición: antes y después

Siempre mide con EXPLAIN (ANALYZE, BUFFERS) antes y después. Un ejemplo típico de mejora:

-- antes: Seq Scan: total 15000 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- después de crear índice parcial: Index Scan: total 120 ms
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

9) Reglas prácticas rápidas

• Prioriza índices que reduzcan coste de lectura y coincidan con WHERE+ORDER BY+LIMIT.
• No indexar todo: cada índice penaliza INSERT/UPDATE/DELETE.
• Usa índices parciales para filtros frecuentes y de alta selectividad.
• Para LIKE 'prefix%': usa btree; para '%infix%': usa trigram + GIN/GiST.
• Mide siempre en un entorno representativo con datos reales.

Siguiente paso avanzado: incorpora pruebas de carga (pgbench o scripts específicos) y automatiza la captura de planes con pg_stat_statements y snapshots de EXPLAIN (ANALYZE). Si trabajas en producción, crea un pipeline que compare planes históricos y te alerte de cambios de estrategia del planner — suelen indicar estadísticas desactualizadas o cambios en patrón de datos. Además, ten cuidado con la proliferación de índices en tablas con alta escritura; a veces la optimización real es rediseñar la consulta o denormalizar selectivamente.