Un índice no se elige solamente por el tipo de columna. PostgreSQL puede utilizarlo cuando la consulta emplea operadores compatibles con la operator class del índice.

Una columna jsonb, por ejemplo, puede admitir ciertas comparaciones mediante B-tree, pero una búsqueda de contención con @> normalmente necesita GIN. Un rango puede compararse por igualdad mediante B-tree, mientras que el solapamiento && se resuelve naturalmente con GiST.

La regla de decisión es:

consulta real → operador → opclass → índice candidato → medición

Matriz de elección

B-tree: escalares, rangos y orden

B-tree es la opción habitual para identificadores, fechas, estados, joins, restricciones únicas y resultados ordenados.

En índices multicolumna, las columnas iniciales son especialmente importantes. Un patrón habitual es igualdad en las primeras columnas y después rango u orden. PostgreSQL 18 puede aplicar skip scan en algunos casos; PostgreSQL 16 y 17 no disponen de esa optimización. Índices multicolumna en PostgreSQL 18.

Para esta consulta:

SELECT id, status
FROM public.products
WHERE tenant_id = 42
  AND created_at >= '2026-08-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

El candidato es:

CREATE INDEX products_tenant_created_idx
ON public.products (tenant_id, created_at DESC);

La igualdad sobre tenant_id reduce primero el conjunto; created_at aplica el rango y proporciona el orden solicitado. El orden inverso respondería a otro patrón de consultas.

GIN: arrays, JSONB y texto

GIN es un índice invertido: una fila puede generar múltiples entradas. Esto resulta útil para arrays, JSONB, texto completo y trigramas, pero aumenta el costo de escritura.

Para consultar arrays:

CREATE INDEX products_tags_gin_idx
ON public.products USING gin (tags);

SELECT id
FROM public.products
WHERE tags && ARRAY['postgresql', 'database'];

Para JSONB con contención y existencia de claves:

CREATE INDEX products_attributes_gin_idx
ON public.products USING gin (attributes);

SELECT id
FROM public.products
WHERE attributes ? 'color'
  AND attributes @> '{"stock": true}'::jsonb;

La opclass predeterminada jsonb_ops admite @>, ?, los operadores de existencia combinada y JSONPath.

Si las consultas utilizan únicamente @>, @? y @@, evalúa como alternativa:

CREATE INDEX products_attributes_path_gin_idx
ON public.products USING gin (attributes jsonb_path_ops);

jsonb_path_ops no admite ?, ?| ni ?&. Puede ser más compacto para los operadores soportados, pero no debe crearse automáticamente junto a jsonb_ops. Índices JSONB.

GIN utiliza normalmente fastupdate, que acumula entradas pendientes. Una lista grande puede afectar búsquedas y producir limpiezas puntualmente costosas; mide autovacuum, WAL y latencia de escritura.

GiST: rangos, solapamientos y distancia

Para una tabla de reservas con during tstzrange:

CREATE INDEX bookings_during_gist_idx
ON public.bookings USING gist (during);

SELECT id, resource_id, during
FROM public.bookings
WHERE during && tstzrange(
  '2026-08-31 12:00:00+00',
  '2026-08-31 13:00:00+00',
  '[)'
);

Si además debes impedir reservas solapadas para un mismo recurso:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE public.bookings
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (
  resource_id WITH =,
  during WITH &&
);

Esta constraint modifica la semántica de escritura y fallará si ya existen solapamientos. Requiere una evaluación de bloqueo independiente. Rangos y GiST.

Con pg_trgm, GiST también puede resolver nearest-neighbor:

SELECT name
FROM public.products
ORDER BY name <-> 'postgres'
LIMIT 10;

Esta capacidad depende de gist_trgm_ops; no pertenece automáticamente a cualquier índice GiST.

Partir de una consulta real

Si pg_stat_statements ya está habilitado:

SELECT queryid, calls, total_exec_time, mean_exec_time,
       shared_blks_hit, shared_blks_read,
       left(query, 300) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Si no está disponible, utiliza APM o logs. Habilitar la extensión puede requerir configuración y reinicio; no debe ser un efecto secundario del procedimiento.

Registra también stats_reset, porque los contadores no son comparables si se reiniciaron:

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Medir antes de crear

Empieza con EXPLAIN, que no ejecuta la consulta:

EXPLAIN (COSTS, VERBOSE)
SELECT id, status
FROM public.products
WHERE tenant_id = 42
  AND created_at >= '2026-08-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

En una copia o con un SELECT acotado:

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, status
FROM public.products
WHERE tenant_id = 42
  AND created_at >= '2026-08-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN ANALYZE ejecuta el SQL. Conserva parámetros y carga comparables para la medición posterior.

Crear el índice en producción

Los CREATE INDEX anteriores son definiciones para laboratorio. Una construcción normal bloquea INSERT, UPDATE y DELETE. Cuando no puedes bloquear escrituras:

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY products_tenant_created_idx
ON public.products (tenant_id, created_at DESC);

RESET lock_timeout;

CREATE INDEX CONCURRENTLY:

• no se ejecuta dentro de BEGIN/COMMIT;
• realiza más trabajo y escaneos;
• puede esperar transacciones antiguas;
• consume CPU, I/O y WAL;
• solo permite una construcción concurrente por tabla;
• puede dejar un índice inválido si falla.

No utilices IF NOT EXISTS para ocultar conflictos: comprueba el nombre, no la equivalencia de la definición. CREATE INDEX.

Comprobar progreso y validez

SELECT pid, relid::regclass AS table_name,
       index_relid::regclass AS index_name,
       command, phase,
       lockers_total, lockers_done,
       blocks_total, blocks_done,
       tuples_total, tuples_done
FROM pg_stat_progress_create_index;

Después:

SELECT indexrelid::regclass AS index_name,
       indisready, indisvalid, indislive
FROM pg_index
WHERE indexrelid =
  to_regclass('public.products_tenant_created_idx');

Sin filas significa que el índice no llegó a registrarse. Si existe, los tres estados deben ser verdaderos. Un índice inválido puede seguir añadiendo costo de escritura aunque no sirva para consultas.

Comparar antes y después

Repite la misma consulta y observa:

• plan y tipo de scan;
• latencia y tiempo total;
• buffers y filas leídas;
• estimaciones frente a filas reales;
• tamaño del índice;
• CPU, I/O, WAL y lag de réplica;
• rendimiento de INSERT, UPDATE y DELETE.

La aparición de Index Scan no es una meta obligatoria. Si la consulta devuelve gran parte de la tabla, Seq Scan puede ser la decisión correcta.

Mantener o retirar

Comprueba primero si el índice respalda una constraint:

SELECT conname, contype, conrelid::regclass
FROM pg_constraint
WHERE conindid =
  'public.products_tenant_created_idx'::regclass;

Si no respalda una constraint, conserva su definición y retíralo de forma controlada:

SELECT pg_get_indexdef(
  'public.products_tenant_created_idx'::regclass
);

SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY public.products_tenant_created_idx;
RESET lock_timeout;

DROP INDEX CONCURRENTLY no se ejecuta dentro de una transacción y no admite CASCADE.

Problemas frecuentes

• PostgreSQL mantiene Seq Scan: el índice puede no ser selectivo o el operador puede no pertenecer a la opclass.
• El índice multicolumna no ayuda: revisa el orden de las columnas.
• GIN degrada escrituras: mide entradas por fila, pending list, WAL y autovacuum.
• jsonb_path_ops no sirve para ?: utiliza jsonb_ops.
• GiST muestra Recheck Cond: algunas opclasses son aproximadas.
• CONCURRENTLY dejó un índice inválido: diagnostica y retíralo o reconstruye.
• idx_scan = 0: no basta para eliminar; revisa stats_reset, constraints y consultas infrecuentes.
  
  
  

Cloud Serversby Donweb

Todo el poder de la nube a tus proyectos y aplicaciones.
Alojamiento ultra rápido, escalable y con alta disponibilidad.

• Performance que te sorprenderá
• Rápida escalabilidad y sin limitaciones
• Arquitectura de alta disponibilidad
• Soporte experto y ejecutivos de cuenta
• Pagos en tu moneda y facturación local

Descubre la mejor solución de Cloud Hosting