Index-Only Scan — un índice que se salta la tabla

Salta la lectura de la tabla — el Index-Only Scan

Una búsqueda por índice normal encuentra la fila objetivo en el índice y luego vuelve a la tabla base para leer las otras columnas una fila a la vez.

Este viaje de vuelta se llama table lookup (el paso de extraer una fila de la tabla base vía el índice).

Cuando el conjunto de resultados es grande, esos viajes de ida y vuelta se acumulan y empiezan a costar tiempo real.

Si usas un índice que contiene cada columna que la consulta referencia, todos los valores están ahí mismo en el índice, y no hay necesidad de volver a la tabla en sí.

Este patrón, donde el índice solo entrega el resultado, se llama un Index-Only Scan (índice cubriente) (un índice que cubre cada columna que la consulta toca también se conoce como covering index).

-- Agregando region con un índice que contiene solo region
-- La columna referenciada (region) está completamente dentro del índice → sin viaje de vuelta a la tabla base
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagina el requisito: "Queremos las ventas totales por comercial." Esta consulta solo toca dos columnas: emp_id y amount. Construye un índice que contenga ambas, y confirma que el plan entrega el resultado solo desde el índice, sin volver a la tabla base. Elimina y reconstruye el índice dentro de esta única ejecución de la consola para que el plan sea totalmente autocontenido. (Ejecútalo correctamente para revelar la explicación.)

① Elimina el índice con DROP INDEX IF EXISTS.

② Crea un índice que contenga tanto emp_id como amount — las columnas que la consulta referencia.

③ Usa EXPLAIN QUERY PLAN sobre la consulta de agregación que suma amount por emp_id, y confirma que el plan nunca lee la tabla base.

Falta una sola columna y vuelves a la tabla base

Un Index-Only Scan solo ocurre cuando cada columna que la consulta toca — en SELECT, WHERE, GROUP BY, etcétera — está dentro del índice.

Falta una sola columna y la base de datos tiene que volver a la tabla base para leerla, y USING COVERING INDEX desaparece del plan.

Por ejemplo, con un índice sobre (emp_id, amount), escribir SELECT emp_id, SUM(amount), region añade region, que no está en el índice — así que la consulta vuelve a la tabla base para obtenerla.

-- Con un índice sobre (region, amount),
-- añadir product a las columnas referenciadas rompe el Index-Only Scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) solo → totalmente cubierto por el índice
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- Añade product → no está en el índice, así que vuelve a la tabla base
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

Usando el mismo índice, compara dos agregaciones: una cuyas columnas referenciadas caben dentro del índice, y una que añade una sola columna que no. Construye un índice compuesto sobre emp_id y amount, luego mira ambos planes uno al lado del otro.

① Elimina el índice con DROP INDEX IF EXISTS.

② Crea un índice compuesto que contenga emp_id y amount.

③ Muestra EXPLAIN QUERY PLAN para la agregación que suma amount por emp_id (cabe dentro del índice).

④ Luego muestra EXPLAIN QUERY PLAN para la misma agregación más el máximo de region, y compáralo con ③ para ver cómo cambia el plan (region no está en el índice).

Mantén el Index-Only Scan incluyendo también las columnas del filtro WHERE

SELECT no es el único lugar donde aparecen las columnas en tu consulta.

Las columnas del filtro WHERE también cuentan como columnas que la consulta toca, así que necesitan estar también en el índice — de lo contrario acabarás volviendo a la tabla base.

Pliégalas todas en un solo índice en el orden "columnas de filtro → columnas de salida / agregación", y tanto el filtrado como la recuperación de valores terminan dentro del mismo índice.

Por ejemplo, para filtrar con WHERE region = 'East' y calcular SUM(amount), pon la columna de filtro region primero y la columna de agregación amount segunda: (region, amount).

El índice acota a las filas objetivo por region y lee amount desde el mismo índice, así que no se necesita viaje de vuelta a la tabla base.

-- Pliega una columna de filtro (status) y una columna de agregación (amount) en un solo índice
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

Imagina el requisito: "Queremos las ventas totales para una region específica." La consulta filtra por region en WHERE y calcula el total de amount. Pliega la columna de filtro y la columna de agregación en un solo índice, y confirma que el plan nunca vuelve a la tabla base.

① Elimina el índice con DROP INDEX IF EXISTS.

② Crea un índice compuesto con la columna de filtro region primero y la columna de agregación amount segunda.

③ Usa EXPLAIN QUERY PLAN sobre la agregación que filtra por region y suma amount, y confirma que el plan nunca lee la tabla base.