Conceptos básicos de índices

9 ejemplos para empezar con los índices de PostgreSQL: 6 básicos y 3 intermedios.

Prerrequisitos

Tabla con miles de filas para obtener una salida EXPLAIN significativa.

# Desarrollo local con Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Ejemplos básicos

1. Crear índice B-tree

Método de acceso predeterminado para igualdad y rangos.

CREATE INDEX customers_email_idx ON app.customers (email);
EXPLAIN SELECT * FROM app.customers WHERE email = 'ada@example.com';

• El planificador puede elegir Index Scan o Bitmap Index Scan
• Las búsquedas únicas a menudo son Index Scan
• Verifique con EXPLAIN ANALYZE

2. Orden de índice compuesto

La columna principal debe coincidir con el predicado.

CREATE INDEX orders_account_created_idx ON app.orders (account_id, created_at DESC);

• Las consultas (account_id) usan el índice
• (created_at) solo puede que no
• Coincida con el orden de clasificación cuando sea posible

3. Coste de escritura del índice

Cada índice se actualiza al cambiar una fila.

INSERT INTO app.customers (email) VALUES ('new@example.com');
-- actualiza customers_email_idx además de la PK

• Más índices ralentizan las escrituras
• Elimine los índices no utilizados que se encuentran en pg_stat_user_indexes
• Mida idx_scan antes de eliminar

4. Índice único

Impone la unicidad y acelera la búsqueda.

CREATE UNIQUE INDEX customers_email_unique ON app.customers (lower(email));

• Los índices de expresión imponen la unicidad insensible a mayúsculas y minúsculas
• Los conflictos generan unique_violation
• Prefiera CONSTRAINT USING INDEX cuando tenga nombre

5. Listar índices en una tabla

Inspección del catálogo.

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'app' AND tablename = 'customers';

• indexdef muestra expresiones y WHERE
• Compare con los archivos de migración
• Tenga cuidado con los índices duplicados superpuestos

6. Cuándo gana Seq Scan

Tablas pequeñas o una fracción amplia de filas.

EXPLAIN ANALYZE SELECT * FROM app.customers;

• El planificador elige Seq Scan cuando es más barato
• Forzar índices es raro
• El crecimiento de los datos cambia la elección

Ejemplos intermedios

7. Construcción de índice concurrente

Evite bloqueos de escritura largos en producción.

CREATE INDEX CONCURRENTLY customers_created_idx
  ON app.customers (created_at);

• No se puede ejecutar dentro de un bloque de transacción
• Una construcción fallida puede dejar un índice INVÁLIDO
• REINDEX CONCURRENTLY para reconstrucciones

8. Columnas INCLUDE de cobertura

Proyecciones amigables para index-only.

CREATE INDEX orders_cover_idx ON app.orders (account_id) INCLUDE (total);

• Las columnas INCLUDE no están en la clave de búsqueda
• Ayuda a los escaneos index-only cuando el mapa de visibilidad está actualizado
• Consulte el artículo sobre escaneos index-only

9. Monitorizar el uso del índice

pg_stat_user_indexes.

SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan;

• idx_scan = 0 durante semanas sugiere un candidato para eliminar
• Restablezca las estadísticas solo deliberadamente
• Empareje con la revisión de la carga de trabajo de consulta

Versiones de la pila: Esta página se escribió para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.