Índices en PostgreSQL: cuándo mejoran el rendimiento y cuándo no

Una consulta busca los veinte pedidos de un cliente, pero PostgreSQL examina doscientas mil filas para encontrarlos. Crear un índice puede reducir ese trabajo. Ahora cambia la pregunta: necesitas casi todos los pedidos de la tabla. Recorrer un índice y después visitar esas filas puede costar más que leer la tabla directamente.

Un índice ofrece otra forma de acceder a los datos; no garantiza que esa forma sea la más barata. Para decidir si aporta valor hay que mirar la consulta, la distribución de los datos y el plan de ejecución. También hay que pagar su almacenamiento y mantenerlo cuando cambian las filas.

Vamos a construir un laboratorio con PostgreSQL 18, comparar planes reales antes y después de crear índices y observar un caso en el que el motor decide ignorar uno. Basta conocer SELECT, WHERE y ORDER BY para seguirlo.

Qué guarda un índice

Un índice es una estructura auxiliar que organiza claves y referencias a filas. Piensa en el índice de un libro: localizar un tema y saltar a sus páginas evita revisar todo el contenido. La diferencia es que la base de datos debe mantener esa estructura mientras recibe escrituras.

En PostgreSQL, el índice se almacena separado del área principal de la tabla, llamada heap. Un índice sobre cliente_id ayuda a localizar pedidos de ese cliente, pero no contiene automáticamente todas sus columnas. Un recorrido ordinario del índice todavía puede necesitar visitar la tabla para recuperar total, por ejemplo. La documentación explica esta separación al introducir los recorridos de índice y de solo índice.

El índice tampoco es una copia del resultado de una consulta ni reordena permanentemente toda la tabla. Sigue siendo necesario escribir ORDER BY si necesitas un orden garantizado en el resultado.

Cómo funciona conceptualmente un B-Tree

B-Tree es el tipo predeterminado de CREATE INDEX. Es un árbol balanceado con varias ramas por página, no un árbol binario de exactamente dos hijos. Sus páginas internas orientan la búsqueda hacia rangos de claves; las hojas contienen entradas que apuntan a filas.

Para buscar una clave, el motor desciende por las ramas pertinentes. Para un rango, puede localizar el comienzo y continuar por las hojas necesarias. Esa organización favorece comparaciones de igualdad, rangos y ciertos recorridos ordenados. No evita el trabajo de recuperar miles de resultados si la consulta los solicita. Véanse los tipos de índices y la estructura B-Tree.

Este esquema simplifica una búsqueda de la clave 140; no representa un índice físico ni un plan real del laboratorio:

flowchart TB
    accTitle: Búsqueda conceptual en un índice B-Tree
    accDescr: La raíz selecciona el rango que contiene 140; la hoja seleccionada conduce a una fila de la tabla.
    R[Raíz con separadores] --> A[Claves menores que 100]
    R -->|buscar 140| B[Claves de 100 a 199]
    R --> C[Claves desde 200]
    B -->|referencia| T[Fila de la tabla]

Sequential scan, index scan y bitmap scan

El planificador compara alternativas usando estimaciones de filas y costos. No ejecuta todas las alternativas para cronometrarlas antes de elegir.

La última opción no significa siempre cero accesos a la tabla: PostgreSQL consulta un mapa de visibilidad y puede necesitar comprobar filas en el heap. Heap Fetches permite observarlo. No añadiremos columnas a los índices solo para conseguir ese nombre en el plan.

Un filtro es selectivo cuando deja pasar una fracción pequeña de las filas. No hay un porcentaje universal que obligue a usar o descartar un índice. Importan también el ancho de las filas, su distribución física, las columnas solicitadas, las estadísticas, la caché y la configuración de costos. La guía de EXPLAIN desarrolla estas alternativas.

Preparar un laboratorio aislado

Necesitas Docker con el motor iniciado. Usaremos la imagen oficial de PostgreSQL, sin publicar puertos y sin red para el contenedor. Los datos son ficticios y se guardan en memoria mediante tmpfs; se perderán al detenerlo. El motor necesita acceso al registro para descargar la imagen inicialmente.

docker run --name uncodigo-indices-lab \
  --rm --detach --network none \
  --memory 768m --cpus 2 --shm-size 256m \
  --mount type=tmpfs,destination=/var/lib/postgresql \
  --env POSTGRES_PASSWORD=solo-laboratorio \
  --env POSTGRES_DB=indices_lab \
  postgres:18.6

La contraseña escrita aquí es exclusiva del contenedor de demostración. No la reutilices como credencial de una base de datos accesible en red. Si ese nombre de contenedor ya existe, elige otro y utilízalo en los comandos siguientes.

Comprueba que PostgreSQL esté listo. Si aún está arrancando, repite la comprobación:

docker exec uncodigo-indices-lab \
  pg_isready -U postgres -d indices_lab

Abre una sesión y ejecuta los bloques SQL en orden, una sola vez sobre esta base vacía:

docker exec -it uncodigo-indices-lab \
  psql -X -v ON_ERROR_STOP=1 -U postgres -d indices_lab

Desactivaremos el paralelismo de consultas solo en esta sesión para que los planes sean más fáciles de leer. No es una recomendación para producción y no estamos deshabilitando los recorridos secuenciales.

SELECT version();

SET max_parallel_workers_per_gather = 0;

Crea la tabla y carga datos deterministas:

CREATE TABLE pedidos (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    referencia text NOT NULL,
    cliente_id integer NOT NULL,
    creado_en timestamptz NOT NULL,
    estado text NOT NULL,
    total numeric(10, 2) NOT NULL,
    detalle text NOT NULL
);

INSERT INTO pedidos (
    referencia, cliente_id, creado_en, estado, total, detalle
)
SELECT
    'PED-' || g,
    (g % 10000) + 1,
    TIMESTAMPTZ '2026-01-01 00:00:00+00'
        + g * INTERVAL '1 minute',
    CASE WHEN g % 20 = 0 THEN 'pendiente' ELSE 'completado' END,
    ((g % 50000) + 100)::numeric / 100,
    repeat('x', 80)
FROM generate_series(1, 200000) AS serie(g);

ANALYZE pedidos;

SELECT count(*) AS filas, count(DISTINCT cliente_id) AS clientes
FROM pedidos;

Hay 200.000 filas, 10.000 clientes y veinte pedidos por cliente. El 5 % de los pedidos está pendiente; el resto está completado. La clave primaria ya creó un índice sobre id, pero todavía no hay uno sobre cliente_id.

ANALYZE recoge estadísticas para que el planificador estime cuántas filas cumplen los filtros. No es lo mismo que la opción ANALYZE de EXPLAIN, que ejecuta una sentencia para observarla. Es una distinción útil antes de examinar el uso de índices.

Primera comparación: buscar los pedidos de un cliente

Primero observa el plan sin ejecutar la búsqueda:

EXPLAIN
SELECT id, creado_en, total
FROM pedidos
WHERE cliente_id = 4242;

Después ejecútala con instrumentación:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creado_en, total
FROM pedidos
WHERE cliente_id = 4242;

En la ejecución realizada para este artículo apareció este plan, omitiendo la sección de planificación:

Seq Scan on pedidos  (cost=0.00..6582.00 rows=20 width=22) (actual time=0.120..5.204 rows=20.00 loops=1)
  Filter: (cliente_id = 4242)
  Rows Removed by Filter: 199980
  Buffers: shared hit=4082
Execution Time: 5.212 ms

El motor devolvió veinte filas y descartó otras 199.980 mediante el filtro. Crea ahora un índice simple, es decir, con una sola columna clave:

CREATE INDEX pedidos_cliente_idx ON pedidos (cliente_id);

Repite la misma consulta:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creado_en, total
FROM pedidos
WHERE cliente_id = 4242;

Este fue el resultado, también sin la sección de planificación:

Bitmap Heap Scan on pedidos  (cost=4.45..80.50 rows=20 width=22) (actual time=0.026..0.046 rows=20.00 loops=1)
  Recheck Cond: (cliente_id = 4242)
  Heap Blocks: exact=20
  Buffers: shared hit=20 read=2
  ->  Bitmap Index Scan on pedidos_cliente_idx  (cost=0.00..4.45 rows=20 width=0) (actual time=0.008..0.008 rows=20.00 loops=1)
        Index Cond: (cliente_id = 4242)
        Index Searches: 1
        Buffers: shared read=2
Execution Time: 0.055 ms

El índice se utilizó aunque la primera línea diga Bitmap Heap Scan: el nodo hijo Bitmap Index Scan localizó las coincidencias. Después se visitaron veinte páginas de la tabla. No hay que buscar únicamente la etiqueta Index Scan para reconocer una consulta que aprovecha un índice.

Qué significan los números

• cost=inicio..total muestra estimaciones en unidades del planificador, no milisegundos. No lo compares directamente con el tiempo observado.
• rows junto a cost estima las filas que entrega el nodo; junto a actual informa las observadas. No es automáticamente el número de filas examinadas.
• actual time=inicio..fin está en milisegundos. Cuando loops es mayor que uno, los tiempos y filas mostrados son promedios por ejecución del nodo.
• Index Cond identifica condiciones usadas por el índice; Filter identifica comprobaciones posteriores. Un filtro no es un error por sí mismo.

Los costos de un nodo incluyen los de sus hijos: sumarlos todos duplica trabajo. La misma precaución aplica al sumar tiempos inclusivos. Estas convenciones están explicadas en Using EXPLAIN.

Buffers: shared hit indica accesos resueltos en la caché compartida de PostgreSQL; read indica bloques cargados en ella. Un read no demuestra una lectura física de disco: el sistema operativo puede tener el bloque en caché y este laboratorio usa almacenamiento en memoria. Los contadores de un padre incluyen los de sus hijos y tampoco son un recuento de bloques únicos de toda la ejecución.

Planning Time y Execution Time separan planificación y ejecución instrumentada. No representan por sí solos la latencia completa de una petición de tu aplicación. Además, EXPLAIN ANALYZE añade trabajo de medición. La referencia del comando EXPLAIN detalla estas opciones.

Qué demuestra esta ejecución y qué no

Son mediciones reales de una ejecución del laboratorio, no promedios ni una promesa de mejora. Se obtuvieron con PostgreSQL 18.6 en Docker sobre macOS ARM64, con dos CPU asignadas al contenedor, límite de 768 MB y datos en tmpfs. La tabla acababa de cargarse y la creación del índice también había leído datos: no es una comparación controlada con caché fría.

Lo que sí observamos es una reducción clara del trabajo para esta búsqueda. Para evaluar producción hay que repetir pruebas con datos y carga representativos, distinguir caché fría y caliente y medir tanto lecturas como escrituras. No traslades estos milisegundos a otro servidor.

Índices compuestos: el orden responde a las consultas

Ahora queremos los cinco pedidos más recientes del cliente. Ejecuta lo siguiente antes de crear el índice compuesto:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creado_en, total
FROM pedidos
WHERE cliente_id = 4242
ORDER BY creado_en DESC
LIMIT 5;

Con el índice simple, el plan observado recuperó veinte pedidos y utilizó Sort antes de entregar cinco. Probemos una estructura que combine filtro y orden:

CREATE INDEX pedidos_cliente_fecha_idx
ON pedidos (cliente_id, creado_en DESC);

Repite la consulta anterior. En nuestra ejecución apareció Index Scan sobre el nuevo índice, debajo de Limit, sin un nodo Sort separado.

Conceptualmente, las entradas se ordenan primero por cliente y después por fecha descendente dentro de cada cliente. La igualdad sobre cliente_id permite localizar ese grupo y obtener sus primeras cinco entradas en el orden solicitado. Un índice sobre (creado_en, cliente_id) organiza los datos de otra manera: primero fecha, después cliente.

Para un B-Tree compuesto, las igualdades en las columnas iniciales y el primer rango posterior permiten acotar el recorrido. Pero evita la regla absoluta «sin la primera columna nunca sirve»: PostgreSQL 18 puede aplicar skip scan, haciendo búsquedas internas repetidas cuando eso resulta rentable, especialmente si la columna inicial tiene pocos valores distintos. Consulta índices multicolumna.

Tampoco basta con poner siempre «la columna más selectiva primero». Parte de los filtros, rangos, ordenaciones y consultas que necesitas atender. Con igualdad sobre ambas columnas, cambiar su orden no tiene la misma consecuencia que buscar solo por una de ellas. Y el orden en que escribas condiciones unidas por AND en el SQL no cambia el orden físico del índice.

Los índices y ORDER BY merecen evaluarse juntos, sobre todo con LIMIT. En este laboratorio ocurrió algo que conviene conservar en la comparación:

Eliminar el sort no produjo un tiempo menor en esa ejecución. Son tiempos muy pequeños, con distinto estado de caché, y no permiten concluir cuál será más rápido bajo carga. El compuesto dio una ruta que recupera directamente las primeras filas; su beneficio real se debe comprobar con más evidencia.

Conservamos ambos índices durante el ejercicio para comparar sus estructuras. En una aplicación, revisa si el simple sigue aportando valor antes de mantener los dos: comparten una columna inicial, pero no tienen el mismo tamaño ni costo. Tampoco elimines automáticamente uno sin revisar las consultas que lo utilizan.

Un índice puede existir y no ser elegido

Crea un índice sobre el estado y consulta los pedidos completados:

CREATE INDEX pedidos_estado_idx ON pedidos (estado);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creado_en, total
FROM pedidos
WHERE estado = 'completado';

El plan real volvió a usar Seq Scan. Devolvió 190.000 filas y descartó 10.000. El planificador estimó 190.760: una aproximación, no un conteo previo exacto.

El índice no evita recuperar el 95 % de la tabla. Para esta consulta, el planificador prefirió recorrerla directamente. Esto no prueba que un índice sobre una columna con pocos valores sea siempre inútil: consultar el estado minoritario puede tener otro costo. Importa la distribución de frecuencias, no solo cuántos valores distintos existen.

Retira únicamente ese índice experimental antes de continuar:

DROP INDEX pedidos_estado_idx;

Otras razones para no elegir un índice incluyen una tabla pequeña, estadísticas desactualizadas, un orden que no ayuda a la consulta o una expresión distinta de la indexada. Por ejemplo, un índice ordinario sobre referencia no equivale a uno sobre lower(referencia); PostgreSQL admite índices sobre expresiones para necesidades de ese tipo.

Las estadísticas del planificador influyen en la elección. Si estima unas pocas filas y aparecen miles, investiga la distribución y la actualización de estadísticas antes de intentar forzar un plan. Desactivar enable_seqscan para obtener el resultado que esperabas no demuestra que tu índice sea una buena decisión.

Índices parciales: indexar el subconjunto que interesa

Supongamos que una pantalla consulta con frecuencia los pedidos pendientes más recientes, aunque sean una minoría del historial:

CREATE INDEX pedidos_pendientes_fecha_idx
ON pedidos (creado_en DESC)
WHERE estado = 'pendiente';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creado_en, total
FROM pedidos
WHERE estado = 'pendiente'
ORDER BY creado_en DESC
LIMIT 10;

El índice contiene entradas solo para las filas que cumplen su predicado, la condición escrita después de WHERE. En la prueba se eligió Index Scan sobre ese índice bajo Limit, y se entregaron diez filas sin una ordenación separada.

estado selecciona qué filas entran; creado_en determina cómo se organizan sus claves. No tienen que ser la misma columna.

La consulta debe permitir demostrar que sus resultados pertenecen al subconjunto indexado. Buscar todos los estados no puede resolverse exclusivamente con este índice porque faltan filas. Tampoco asumas que PostgreSQL reconocerá cualquier expresión matemáticamente equivalente. La documentación de índices parciales explica estos límites.

En una sentencia preparada, un plan genérico para estado = $1 no puede asumir que el parámetro siempre será 'pendiente'. Los planes personalizados pueden incorporar valores concretos; revisa el plan que realmente ejecuta tu aplicación. PostgreSQL describe la elección entre planes genéricos y personalizados en PREPARE.

Índices únicos: rendimiento e integridad son objetivos distintos

Cada pedido necesita una referencia irrepetible. Exprésalo como una restricción de la tabla:

ALTER TABLE pedidos
ADD CONSTRAINT pedidos_referencia_unica UNIQUE (referencia);

PostgreSQL crea el índice único que respalda esa restricción. No agregues otro índice idéntico por costumbre. Ya ocurría lo mismo con la clave primaria id.

Una clave única compuesta prohíbe repetir la combinación completa, no cada columna de forma independiente. Por defecto, los valores nulos se consideran distintos para esta comprobación; existe NULLS NOT DISTINCT si necesitas otro comportamiento. Aquí referencia es NOT NULL, así que ese caso no se presenta. Véase Unique Indexes.

Un índice que mantiene una regla de integridad puede ser necesario aunque rara vez aparezca en las consultas de lectura. Su valor no se reduce al número de búsquedas aceleradas.

El costo que pagas al escribir y almacenar

Cada índice ocupa espacio adicional. Puedes inspeccionar el estado del laboratorio después de los pasos anteriores:

SELECT
    pg_size_pretty(pg_table_size('pedidos')) AS tabla,
    pg_size_pretty(pg_indexes_size('pedidos')) AS indices,
    pg_size_pretty(pg_total_relation_size('pedidos')) AS total;

SELECT
    indexrelname AS indice,
    pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
FROM pg_stat_user_indexes
WHERE relname = 'pedidos'
ORDER BY pg_relation_size(indexrelid) DESC;

En esta ejecución, PostgreSQL informó 32 MB para la tabla, 18 MB para sus índices y 50 MB en total, usando el formato redondeado de esas funciones. El compuesto ocupaba 6184 kB y el parcial, 240 kB. No son estructuras equivalentes: el parcial indexa menos filas y tiene una clave distinta. Son observaciones del laboratorio, no una proporción universal de ahorro. La referencia de funciones de tamaño detalla qué incluye cada medida.

El almacenamiento no es el único costo:

Un índice puede acelerar la búsqueda de las filas que vas a actualizar o borrar. Al mismo tiempo, aumenta el trabajo de conservar y limpiar estructuras. Las dos cosas pueden ocurrir en la misma sentencia.

No es exacto afirmar que un UPDATE siempre reescribe todos los índices. PostgreSQL puede aplicar HOT, Heap-Only Tuples, cuando se cumplen condiciones como no cambiar columnas referenciadas por los índices relevantes y disponer de espacio en la misma página. Agregar índices sobre columnas que cambian puede reducir esas oportunidades. Hay particularidades para índices de resumen como BRIN; este laboratorio utiliza B-Tree. Consulta HOT.

Tampoco un DELETE elimina inmediatamente toda huella física. El control de concurrencia por versiones permite que otras transacciones sigan viendo versiones anteriores. VACUUM y autovacuum participan en recuperar espacio reutilizable y limpiar entradas obsoletas. Más índices también significan más estructuras que mantener. La guía de vacuum explica ese ciclo.

EXPLAIN ANALYZE sobre escrituras sí modifica datos

Para observar una actualización solo en este laboratorio, podemos revertir el cambio lógico al terminar:

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE pedidos
SET estado = 'completado'
WHERE id = 20;

ROLLBACK;

SELECT estado FROM pedidos WHERE id = 20;

El estado sigue siendo pendiente después del rollback. WAL muestra actividad del registro de escritura anticipada, que PostgreSQL utiliza para recuperación. La actualización sí se ejecutó: revertir no evita su consumo de CPU, sus bloqueos ni todo el trabajo físico. No extrapoles este patrón a operaciones con efectos externos o a una base productiva para «probar sin consecuencias».

No hemos medido el costo sostenido de inserciones, actualizaciones y borrados con diferentes conjuntos de índices. Los efectos de mantenimiento anteriores están documentados; los planes y tamaños mostrados sí proceden del laboratorio ejecutado. Son evidencias distintas.

Llevar la decisión a una aplicación real

Empieza por una consulta importante y una hipótesis: «este filtro devuelve pocas filas», «este orden con límite evita ordenar un conjunto grande» o «este subconjunto pequeño concentra las lecturas». Comprueba el plan antes y después, con parámetros y datos que representen el uso real.

Si la consulta combina tablas, analiza también la condición de unión y cuántas filas recibe cada paso. La guía de tipos de JOIN ayuda a revisar qué resultado estás solicitando. En agregaciones, distingue filtrar filas de filtrar grupos, como explica la guía de HAVING: no todas las condiciones reducen el trabajo en la misma etapa.

Una clave foránea tampoco obliga a crear automáticamente un índice en el lado que referencia. PostgreSQL no lo crea por defecto; puede ser conveniente para consultas y comprobaciones asociadas a cambios en la tabla referenciada, pero hay que decidirlo según la carga. La documentación de restricciones describe esa distinción.

Finalmente, crear el índice también es una operación con impacto. CREATE INDEX ordinario bloquea escrituras durante su construcción. CREATE INDEX CONCURRENTLY permite continuar escribiendo, pero requiere más trabajo, tiene restricciones —no se ejecuta dentro de un bloque de transacción— y puede dejar un índice inválido si falla. Planifica y supervisa la operación; no copies automáticamente los comandos del laboratorio en producción. Consulta construcción concurrente.

Al terminar el laboratorio, sal de psql con \q y detén el contenedor:

docker stop uncodigo-indices-lab

--rm elimina ese contenedor y sus datos temporales. No afecta a otras bases de datos.

Conserva un índice porque resuelve una necesidad comprobada o sostiene una regla de integridad. Si no puedes explicar qué consulta beneficia, qué regla protege y qué mantenimiento añade, todavía falta información para justificarlo. El objetivo es mejorar el trabajo total de la aplicación, no conseguir que todos los planes muestren un índice.

Referencias

• PostgreSQL 18: introducción a los índices, tipos de índices y estructura B-Tree.
• PostgreSQL 18: Using EXPLAIN, referencia EXPLAIN y estadísticas del planificador.
• PostgreSQL 18: índices multicolumna, ORDER BY, parciales, únicos y recorridos de solo índice.
• PostgreSQL 18: HOT, vacuum y CREATE INDEX.
• Docker Official Image: PostgreSQL.