Volver a las notas

Consultas lentas en PostgreSQL: encontrarlas, leer EXPLAIN y crear el índice

16 de julio de 2026·
postgresqlperformancesqlindices

Nota revisada y reescrita el 18 de octubre de 2026: la versión original tenía errores y datos sin fuente.

Antes de crear índices a ciegas, merece la pena saber qué consulta es la lenta y por qué. PostgreSQL trae las dos herramientas para eso: pg_stat_statements y EXPLAIN.

1. Qué consulta duele

pg_stat_statements agrupa las consultas por forma (sin los valores concretos) y acumula tiempos. Hay que cargarla en postgresql.conf y reiniciar:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS media_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ordena por tiempo total, no por media: una consulta de 20 ms que se ejecuta 50.000 veces al día pesa más que una de 2 segundos que corre una vez.

2. Por qué es lenta

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE ejecuta la consulta y muestra tiempos reales. Con un UPDATE o un DELETE, hazlo dentro de BEGIN; ... ROLLBACK;. Lo que hay que mirar:

  • Seq Scan sobre una tabla grande con un filtro que descarta casi todo (Rows Removed by Filter enorme): falta un índice.
  • rows= estimadas frente a actual rows muy distintas: el planificador trabaja con estadísticas viejas. ANALYZE orders; y vuelve a mirar.
  • Sort con external merge Disk: la ordenación no cabe en work_mem y se va a disco.
  • Buffers: shared read alto: está leyendo de disco, no de caché.

3. El índice que encaja

Para la consulta de arriba, un índice compuesto con las columnas de igualdad primero y la de orden al final permite filtrar y devolver ya ordenado, sin Sort:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

CONCURRENTLY evita bloquear las escrituras mientras se construye, a cambio de tardar más. No puede ir dentro de una transacción, y si falla deja un índice INVALID que hay que borrar y volver a crear.

Otros dos tipos que resuelven casos frecuentes:

-- Parcial: si casi siempre filtras por pedidos pendientes, indexa solo esos
CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

-- De expresión: búsquedas sin distinguir mayúsculas
CREATE INDEX CONCURRENTLY customers_email_lower_idx
  ON customers (lower(email));
-- solo se usa si la consulta escribe exactamente lower(email) = ...

4. Comprobar y limpiar

Vuelve a lanzar el EXPLAIN (ANALYZE, BUFFERS) y confirma que aparece Index Scan o Index Only Scan y que el tiempo baja. Los índices no son gratis: ocupan disco y hacen más lento cada INSERT y UPDATE. Para ver los que nunca se usan:

SELECT relname AS tabla, indexrelname AS indice, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan cuenta desde el último reinicio de estadísticas. Antes de borrar nada, asegúrate de que el contador lleva tiempo suficiente acumulando, y de que el índice no respalda una restricción UNIQUE o una clave primaria.

Fuentes