En una tabla de un millón de filas, la misma SELECT puede tardar cincuenta milisegundos o bastante menos de uno. La única diferencia es si la base de datos lee cada fila para encontrar las que pediste, o salta directamente a ellas. Un índice es lo que le permite saltar, y lo pagas en cada escritura.

En concreto, un índice es una segunda copia ordenada de una o más columnas, guardada junto a la tabla y actualizada automáticamente cada vez que la tabla cambia. La base de datos la usa para llegar a las filas que coinciden sin tocar el resto.

Por qué una consulta sin índice lee toda la tabla

Sin un índice, WHERE customer_id = 42 obliga a Postgres a leer cada fila y probar la condición en cada una. Eso es un escaneo secuencial (Seq Scan). En mil filas ni lo notas. En un millón de filas donde casi nada coincide, estás leyendo y descartando 999.989 filas para obtener 11.

Puedes verlo tú mismo. Arranca un contenedor de Postgres, configurado igual que en Qué Es Docker Compose y Cómo Usarlo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

1
2

docker compose up -d db
docker compose exec db psql -U postgres

Luego crea una tabla lo bastante grande como para que se note la diferencia:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents integer NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT
  (random() * 50000)::int,
  (ARRAY['pending', 'paid', 'shipped', 'refunded'])[(floor(random() * 4) + 1)::int],
  now() - (random() * interval '365 days'),
  (random() * 20000)::int
FROM generate_series(1, 1000000);

ANALYZE orders;

Un millón de filas, sin índice en customer_id. Pide los pedidos de un cliente. Los ejemplos siguientes desactivan primero los workers paralelos (SET max_parallel_workers_per_gather = 0), para que el plan se quede en un solo nodo — en una máquina multinúcleo, Postgres podría repartir el mismo escaneo entre un Gather y varios workers Parallel Seq Scan, que es la misma idea con más procesos corriendo a la vez:

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..20834.00 rows=21 width=31) (actual time=1.066..53.238 rows=11 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 999989
 Planning Time: 0.301 ms
 Execution Time: 53.284 ms

Rows Removed by Filter: 999989 es la factura. Postgres leyó toda la tabla para devolver 11 filas.

Qué guarda realmente un índice B-tree

Una tabla sin índice es una caja de recibos, tirados uno encima de otro en el orden en que llegaron. Encontrar todos los recibos de un cliente significa vaciar la caja.

Un índice B-tree es esos mismos recibos archivados en un cajón, ordenados por cliente. El nivel más bajo, las páginas hoja, guarda cada valor indexado junto a un puntero a su fila en disco. Por encima de las hojas hay unas pocas páginas internas, cada una con el valor más bajo de un rango y un puntero hacia él. Las páginas de Postgres son de 8 KB por defecto y una página interna puede apuntar a cientos de hijos, así que tres o cuatro niveles cubren decenas de millones de filas. Una búsqueda lee una página por nivel y aterriza en la hoja correcta. Unas pocas lecturas de página en vez de un recorrido por toda la tabla.

Añade el índice y ejecuta la misma consulta:

1

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5
6
7

 Bitmap Heap Scan on orders  (cost=4.59..85.69 rows=21 width=31) (actual time=0.217..0.295 rows=11 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.58 rows=21 width=0) (actual time=0.090..0.090 rows=11 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.979 ms
 Execution Time: 0.449 ms

De 53 ms a 0,45 ms — unas 120 veces más rápido — y el coste estimado baja de 20834 a 86. El Seq Scan ha desaparecido, y no hay línea Rows Removed by Filter: no había nada que descartar.

Postgres eligió una Bitmap Heap Scan en lugar de un simple Index Scan. Con unas 20 filas coincidentes repartidas por la tabla, sale más barato sacar primero todas las entradas que coinciden del índice (la línea Bitmap Index Scan), ordenarlas por página física y visitar cada página del heap una sola vez, que saltar al heap por separado para cada fila. Eso es lo que muestra Heap Blocks: exact=11. Filtra por una sola fila (una igualdad sobre una columna única, por ejemplo id = 42) y verás en cambio un simple Index Scan. En ambos casos, el índice hace el mismo trabajo: llegar a las filas que coinciden sin leer las que no.

Cómo funciona el orden de columnas en un índice compuesto

Un índice puede cubrir más de una columna, y el orden en que las lista decide qué consultas puede resolver. CREATE INDEX ON orders (customer_id, status) ordena primero por customer_id, y luego por status dentro de cada customer_id. Así que puede responder a:

  • WHERE customer_id = 42, usando la primera columna.
  • WHERE customer_id = 42 AND status = 'paid', usando ambas.

No puede responder de forma eficiente a WHERE status = 'paid' por sí sola. status solo está ordenada dentro del grupo de cada cliente, así que no existe una única secuencia ordenada de filas 'paid' a la que el árbol pueda saltar. Esta es la regla del prefijo más a la izquierda (leftmost prefix rule): un índice compuesto ayuda a una consulta solo si esta restringe sus columnas desde la izquierda, en orden, sin huecos.

Dos reglas para elegir el orden. Primero, pon delante la columna por la que tus consultas filtran sola, porque es la única que el índice puede servir por sí misma. Segundo, pon las columnas que comparas con = antes de una columna que comparas con un rango (>, <, BETWEEN), porque todo lo que va después de la columna de rango deja de acotar la búsqueda. Para esta tabla, customer_id (unos 50.000 valores distintos) va antes que status (cuatro valores): buscas clientes todo el tiempo, y nunca quieres barrer todos los pedidos 'paid' sin distinguir el cliente.

Si además necesitas búsquedas rápidas solo por status, crea un segundo índice. No reordenes el primero.

Cuándo un index-only scan se salta la tabla

La Bitmap Heap Scan de antes sigue tocando la tabla — eso es lo que hace Heap Blocks: exact=11. El índice tiene customer_id y un puntero a cada fila; SELECT * también quiere status, total_cents y created_at, y esas solo viven en la tabla. Una cláusula INCLUDE mete columnas extra dentro del propio índice:

1
2
3

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_cents);

status y total_cents viajan como payload. No forman parte de la clave de orden, así que un WHERE status = ... no puede usarlas para buscar. Pero pide solo las columnas que el índice ahora lleva consigo:

1

EXPLAIN ANALYZE SELECT customer_id, status, total_cents FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.79 rows=21 width=15) (actual time=0.071..0.073 rows=11 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.442 ms
 Execution Time: 0.108 ms

Index Only Scan y Heap Fetches: 0: cada fila viene directa del índice, sin ninguna visita a la tabla. Eso es un index-only scan.

Heap Fetches: 0 depende del mapa de visibilidad de la tabla, que marca las páginas cuyas filas son visibles para todas las transacciones. Un escaneo solo se salta el heap en las páginas marcadas. VACUUM pone esas marcas (normalmente autovacuum, que corre con su propio calendario, no algo que lances a mano) y cualquier escritura sobre una página las borra de nuevo. En una tabla con muchas escrituras recientes, muchas páginas no están marcadas, Heap Fetches sube por encima de cero, y Postgres visita igualmente el heap para esas filas. Los índices de cobertura compensan más en tablas que se leen mucho más de lo que se escriben.

Qué cuesta un índice y por qué Postgres lo ignora

Cada INSERT escribe una entrada en cada índice de la tabla, y un UPDATE que cambia una columna indexada también escribe nuevas entradas. Cinco índices significan cinco estructuras extra que mantener en cada una de esas escrituras. Los índices también ocupan disco. Compara los dos tamaños directamente:

1
2

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_relation_size('idx_orders_customer_id')) AS index_size;

Y a veces creas el índice y el planner no lo usa. Rara vez es un bug de Postgres. Suele ser alguno de estos casos:

  • Una función envuelve la columna. WHERE lower(email) = '[email protected]' no puede usar un índice simple sobre email, porque el índice guarda los valores en crudo, no lower(email). Indexa la expresión (CREATE INDEX ON users (lower(email))) o filtra sobre la columna en crudo.
  • Un comodín al principio. WHERE name LIKE '%smith' no le da al árbol ningún prefijo por el que buscar. LIKE 'smith%' sí.
  • Baja selectividad. Un índice sobre un booleano repartido 50/50 no ahorra nada. Postgres leería igual la mitad del índice y la mayoría de las páginas de la tabla, así que el planner elige correctamente un escaneo secuencial.
  • Estadísticas desactualizadas. El planner elige entre tipos de escaneo usando las estadísticas que ANALYZE recoge en pg_statistic. Tras una carga masiva sin ANALYZE, sus estimaciones de filas pueden estar muy lejos de la realidad. Ejecútalo después de cada carga grande, como en la configuración de arriba.

Cuando veas un Seq Scan donde esperabas un Index Scan, revisa la consulta contra esta lista antes de tocar el índice.

Cuándo añadir un índice y cuándo evitarlo

Añade uno cuando una columna aparece en una WHERE, un JOIN o un ORDER BY sobre una tabla lo bastante grande como para que un escaneo cueste tiempo de verdad, y el filtro es selectivo: reduce el resultado a una porción pequeña de la tabla, no a la mitad. Evítalo cuando la tabla es lo bastante pequeña como para que un escaneo ya sea rápido, cuando la tabla recibe muchas escrituras y pocas lecturas, o cuando la columna no puede acotar nada.

No lo añadas por intuición. Ejecuta primero EXPLAIN ANALYZE sobre la consulta lenta. Si ya muestra un Index Scan y sigue siendo lenta, el índice no es tu problema: reescribe la consulta, o pon el resultado en caché con Redis. Si muestra un Seq Scan sobre una tabla grande con un filtro selectivo, crea el índice exactamente sobre las columnas que usa la WHERE, en el orden que pide la regla del prefijo más a la izquierda, y luego ejecuta EXPLAIN ANALYZE otra vez para confirmar que el plan cambió.

Artículos relacionados