Cuando alguien dice "la base de datos está lenta", casi siempre el problema real es otro: faltan índices, o los que hay no sirven para la query que se está ejecutando. No hace falta ser DBA para diagnosticarlo. Con EXPLAIN ANALYZE y un poco de criterio para elegir el tipo de índice correcto, la mayoría de queries lentas en PostgreSQL se arreglan en minutos.

Primero, entiende qué está haciendo tu query

EXPLAIN ANALYZE ejecuta la query de verdad y te dice exactamente en qué se le fue el tiempo. La primera vez que lo ves, la salida intimida un poco (nodos, costes, números por todas partes), pero la idea central es simple: si ves Seq Scan, PostgreSQL está leyendo la tabla entera fila por fila. Si ves Index Scan, está usando un índice para ir directo a lo que necesita. El objetivo casi siempre es pasar de lo primero a lo segundo.

EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE user_id = 123 AND created_at > '2026-01-01';

Si esa query devuelve algo como "Seq Scan on transactions (cost=... rows=150000000)", ya sabes dónde está el problema: se están recorriendo 150 millones de filas para encontrar las de un solo usuario. Herramientas como DBeaver visualizan este plan como un árbol y son bastante más cómodas que leerlo en la terminal.

Qué tipo de índice usar

PostgreSQL no tiene un único tipo de índice, y elegir el correcto importa:

• B-tree es el índice por defecto y cubre la gran mayoría de casos: igualdad, rangos, ordenamiento. Si dudas, empieza aquí.
• Hash solo sirve para igualdad exacta (WHERE id = 123). Es algo más compacto que B-tree pero pierde la capacidad de resolver rangos, así que su caso de uso es bastante estrecho.
• GIN es el que necesitas para buscar dentro de JSON, arrays o texto completo. Tarda más en construirse, pero las búsquedas son muy rápidas.
• BRIN está pensado para tablas enormes con datos ordenados de forma natural, como series temporales. En vez de indexar fila por fila, indexa rangos de bloques, así que su tamaño en disco es una fracción del de un B-tree equivalente — a cambio de que solo funciona bien si los datos realmente están ordenados por esa columna.

Un patrón que se repite mucho

Es habitual encontrarse con tablas de decenas o cientos de millones de filas donde una query "de siempre" tarda varios segundos, y la causa resulta ser la misma: no hay ningún índice que cubra el filtro que se usa a diario. Añadir uno compuesto, del tipo:

CREATE INDEX idx_transactions_user_date
ON transactions(user_id, created_at DESC);

...suele bajar ese tipo de query de varios segundos a un puñado de milisegundos, sin tocar ni una línea de código de la aplicación. No es magia: es que antes se estaba escaneando la tabla entera y ahora no.

Errores habituales al indexar

Creas el índice y sigue usando Seq Scan. A veces PostgreSQL no confía todavía en las estadísticas de la tabla. Ejecuta ANALYZE nombre_tabla; y vuelve a mirar el plan.

Demasiados índices. Cada índice adicional ralentiza los INSERT y UPDATE. Antes de crear uno nuevo, comprueba si de verdad hay una query que lo necesite, y de vez en cuando revisa cuáles no se están usando:

SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Aplicar funciones sobre la columna indexada. WHERE LOWER(email) = 'user@example.com' no puede aprovechar un índice normal en email, porque el valor que compara ya no es el original. La solución es un índice de expresión:

CREATE INDEX idx_email_lower ON users(LOWER(email));

Indexar columnas de baja cardinalidad. Si una columna solo tiene un puñado de valores posibles (por ejemplo, un status con tres estados), un índice ahí normalmente no ayuda: PostgreSQL prefiere seguir escaneando la tabla.

Herramientas para diagnosticar

DBeaver Community (gratis, open source) es probablemente la opción más cómoda para visualizar planes de ejecución sin pelearte con la terminal.

DataGrip de JetBrains es de pago, pero sugiere índices automáticamente cuando detecta un Seq Scan en una query que ejecutas a menudo.

pgAdmin 4 es gratis, corre en el navegador y aunque su análisis de planes es más básico, cumple perfectamente para el día a día.

En resumen

Optimizar PostgreSQL no exige ser experto en estructuras de datos. El flujo de trabajo es siempre el mismo: ejecuta EXPLAIN ANALYZE en la query lenta, identifica el Seq Scan, crea el índice que corresponda, y vuelve a comprobar que ahora aparece Index Scan. La próxima vez que alguien diga que "PostgreSQL es lento", vale la pena preguntar si ha revisado los índices antes de asumir que hace falta reescribir la aplicación.

Fuentes:

• Documentación oficial de PostgreSQL — CREATE INDEX
• Use The Index, Luke!