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

*Cuarta entrega de la serie **[PostgreSQL desde cero a pro](/search?tag=postgresql-desde-cero)**. Tiempo de lectura estimado: 13 minutos.*

Tienes un esquema decente ([II](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones)) y sabes escribir consultas potentes ([III](/post/postgresql-desde-cero-iii-ctes-window-functions-y-consultas-avanzadas)). 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.

```sql
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:

```sql
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:

```sql
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:

```sql
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.

```sql
-- 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](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones) 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).

```sql
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*:

```sql
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:

```sql
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`:

```sql
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](https://explain.depesz.com) — pegas el plan y lo colorea por coste.
- [explain.dalibo.com](https://explain.dalibo.com) — visualización de árbol con detalles.
- [pev2](https://dalibo.github.io/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:

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

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

```sql
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:

```sql
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:

```sql
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.

```sql
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

```sql
-- 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](/search?tag=clickhouse-desde-cero), especialmente el [post comparativo](/post/clickhouse-para-desarrolladores-que-vienen-de-postgresql).

## Por dónde seguir

- **[V: replicación, backups y producción](/post/postgresql-desde-cero-v-replicacion-backups-y-produccion)** — último tramo: operar PostgreSQL con garantías.

Aterrizando tarde:

- [I: instalación, psql y primeros pasos](/post/postgresql-desde-cero-i-instalacion-psql-y-primeros-pasos)
- [II: tipos, restricciones y relaciones](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones)
- [III: CTEs, window functions y consultas avanzadas](/post/postgresql-desde-cero-iii-ctes-window-functions-y-consultas-avanzadas)