PostgreSQL
Índices: familias, diseño y trade-offs
Diseño de índices B-tree, Hash, GIN, GiST, BRIN y SP-GiST según operadores, selectividad, ordenamiento, escritura y tamaño.
- Última actualización
- Actualizada
- Nivel
- Profundización
PostgreSQL
Diseño de índices B-tree, Hash, GIN, GiST, BRIN y SP-GiST según operadores, selectividad, ordenamiento, escritura y tamaño.
Un índice no “acelera una tabla”. Acelera operadores y patrones de acceso concretos a cambio de almacenamiento, WAL, mantenimiento y mayor coste en cada escritura.
Una tabla heap no está ordenada por primary key. Para encontrar filas, PostgreSQL puede leer la tabla o consultar una estructura adicional:
consulta
↓
planner estima cuántas filas necesita
├─ sequential scan
└─ access method + índice
↓
candidatos
↓
verificar visibilidad y filtrosEl índice guarda claves y referencias hacia tuples. No reemplaza la tabla ni conoce por sí solo todas las reglas de visibilidad de MVCC.
Si una tabla tiene cien millones de orders y necesitas veinte de una branch concreta, leer todas las páginas sería costoso. Un índice puede localizar un rango pequeño.
Pero si la consulta necesita el 70 % de la tabla, saltar entre índice y heap puede costar más que un scan secuencial. La existencia del índice no obliga al planner a usarlo.
Cada índice introduce:
Por eso “crear un índice por columna” degrada sistemas con muchas escrituras.
El access method define la estructura general; la operator class conecta tipos y operadores con esa estructura.
B-tree + tipo/opclass
→ igualdad, orden y rangos compatibles
GIN + opclass JSONB
→ pertenencia de claves/valoresNo todo índice soporta cualquier operador. Antes de crear uno, identifica el predicado real de la query.
Es el default y la familia más general:
CREATE INDEX orders_branch_created_idx
ON orders(branch_id, created_at DESC, id DESC);Sirve a patrones como:
WHERE branch_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;B-tree soporta normalmente:
IS NULL / IS NOT NULL en contextos compatibles.Índice:
(branch_id, created_at, id)Organización conceptual:
branch 1
created_at...
branch 2
created_at...Puede buscar eficientemente una branch y después rango/orden temporal. No equivale a (created_at, branch_id), que organiza primero por tiempo global.
Regla aproximada para B-tree compuesto:
Verifica con EXPLAIN; las capacidades modernas como skip scan dependen del patrón y versión, y no convierten el orden en irrelevante.
CREATE INDEX orders_created_desc_idx
ON orders(created_at DESC, id DESC);Puede evitar sort para:
ORDER BY created_at DESC, id DESC
LIMIT 20;B-tree puede recorrerse en ambas direcciones, pero combinaciones de ASC/DESC y NULLS importan en índices multicolumna.
CREATE UNIQUE INDEX products_business_sku_uidx
ON products(business_id, sku);No es solo rendimiento. Protege unicidad bajo concurrencia.
Cuando sea posible, una UNIQUE constraint comunica mejor la regla y crea el índice subyacente. Un unique partial/expression index cubre reglas que una constraint tradicional no expresa.
CREATE INDEX orders_pending_created_idx
ON orders(branch_id, created_at DESC, id DESC)
WHERE status = 'pending';Beneficios:
La query debe permitir demostrar el predicado. Un parámetro genérico:
WHERE status = $1puede no implicar en planificación que siempre sea pending, especialmente con generic plans.
CREATE UNIQUE INDEX users_active_email_uidx
ON users(lower(email))
WHERE deleted_at IS NULL;Útil cuando el workload consulta exactamente esa expresión.
Condiciones:
CREATE INDEX orders_customer_created_idx
ON orders(customer_id, created_at DESC)
INCLUDE (status, total_amount);Columnas key:
Columnas INCLUDE:
Trade-offs:
No significa “nunca lee la tabla”. PostgreSQL necesita comprobar visibilidad.
Si la visibility map marca una page all-visible, puede responder desde el índice. Si no, realiza heap fetch.
Señal en plan:
Index Only Scan
Heap Fetches: 12000Muchos heap fetches pueden indicar writes recientes o vacuum/visibility insuficiente.
Especializados en igualdad. Son WAL-logged y crash-safe en versiones modernas, pero B-tree suele ser preferible por versatilidad.
Considera hash únicamente tras comparar tamaño, workload de igualdad y operación; no por asumir que “hash siempre es O(1)”.
Generalized Inverted Index almacena múltiples claves por fila.
Casos:
tsvector de full-text search.Ejemplo:
CREATE INDEX events_payload_gin
ON events USING gin(payload jsonb_path_ops);Trade-offs:
Framework para datos con relaciones espaciales o de solapamiento:
Ejemplo:
CREATE INDEX reservations_period_gist
ON reservations USING gist(reserved_during);Permite operadores como overlap && para ranges.
Space-Partitioned GiST representa estructuras como tries, quadtrees o particiones no balanceadas según operator class.
Puede aportar en:
Es una herramienta especializada; el tipo y operador determinan si aplica.
Block Range Index resume rangos de páginas físicas:
CREATE INDEX events_occurred_brin
ON events USING brin(occurred_at);Funciona bien cuando:
Ventajas:
Costes:
pages_per_range cambia tamaño/precisión.Un índice es valioso cuando reduce suficientemente el conjunto o evita un sort costoso.
Columna status con tres valores:
WHERE status = 'paid'Si paid representa 95 %, un índice simple puede perder frente a sequential scan.
Si pending representa 0.1 %, un partial index puede ser excelente.
PostgreSQL puede combinar índices:
Bitmap Index Scan A
Bitmap Index Scan B
→ BitmapAnd / BitmapOr
→ Bitmap Heap ScanÚtil para varios filtros con muchas coincidencias. Agrupa visitas por página, reduciendo saltos aleatorios.
Un bitmap puede volverse lossy cuando falta memoria y requiere más rechecks.
La FK necesita una key única apropiada en el parent, pero no crea índice automático en el child.
Índice child aporta cuando:
Ejemplo:
CREATE INDEX order_items_order_id_idx
ON order_items(order_id);No indexes mecánicamente una tabla diminuta; analiza lifecycle.
CREATE INDEX CONCURRENTLY orders_status_idx
ON orders(status);Reduce bloqueo de escrituras al hacer varias fases y esperar transacciones.
Características:
INVALID al fallar.Después de un fallo, inspecciona y elimina/reintenta conscientemente.
Reduce ciertos bloqueos al eliminar un índice, con restricciones como no usarlo dentro de transaction block y limitaciones sobre índices que respaldan constraints.
Nunca elimines un índice de constraint sin comprender la dependencia.
Se usa para:
REINDEX CONCURRENTLY reduce indisponibilidad en versiones compatibles, pero requiere espacio adicional y trabajo.
No lo ejecutes como ritual nocturno.
Ejemplo:
(a)
(a, b)El segundo puede cubrir búsquedas por a, pero no siempre hace redundante al primero:
Decide con workload, no solo prefijos.
pg_stat_user_indexes.idx_scan = 0 no demuestra inutilidad:
Combina periodo largo, tamaño, queries y dependencias.
Updates/deletes dejan entradas que vacuum limpia; splits y patrones aleatorios pueden dejar baja densidad.
Diagnóstico puede usar:
pgstattuple si está disponible.No confundas espacio libre reutilizable con bloat dañino.
Consulta:
SELECT id, status, created_at, total_amount
FROM orders
WHERE tenant_id = $1
AND branch_id = $2
AND status = 'pending'
AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 50;Índice candidato:
CREATE INDEX orders_pending_page_idx
ON orders(tenant_id, branch_id, created_at DESC, id DESC)
INCLUDE (status, total_amount)
WHERE status = 'pending';Decisiones:
Sequential scan puede ser siempre mejor; el índice añade coste.
Index scan puede hacer I/O aleatorio excesivo.
Planner puede estimar mal; extended statistics ayudan.
Necesita ANALYZE y pages quizá no all-visible.
Índices textuales pueden necesitar reindex.
Puede exceder límites o inflar mucho el índice.
Cada partition tiene índices propios; el índice “global” no existe como en otros motores. Mantenimiento y unicidad tienen requisitos.
Amplifica escrituras y no cubre combinaciones reales.
Crea estructura que no alinea filtros/orden.
El planner compara coste según selectividad.
Solo aporta si existen operadores y queries compatibles.
Infla el índice.
Oculta estimaciones o diseño incorrectos.
Pierde índices raros o de integridad.
(branch_id, created_at) no equivale a (created_at, branch_id)?Query planner y executor explica cómo PostgreSQL estima cardinalidad y elige entre estas estructuras.