Seq Scan frente a Index Scan e Index-Only

Cuando el planificador necesita filas de una tabla, elige uno de tres métodos de acceso, y los entrevistadores esperan que mencione los tres:

• Seq Scan, leer todas las filas de la tabla de principio a fin.
• Index Scan, recorrer un índice para encontrar las filas coincidentes y, después, obtener cada una de la tabla.
• Index-Only Scan, responder por completo desde el índice sin acceder a la tabla.

Comprender por qué el planificador elige cada uno es el núcleo de esta lección y una pregunta sénior habitual.

Un Seq Scan lee las páginas de la tabla una tras otra y aplica cualquier filtro a cada fila. No se consulta ningún índice.

Esto puede parecer negativo, pero a menudo es la opción correcta. Las lecturas secuenciales son rápidas para el disco (no requieren saltos aleatorios), por lo que, cuando una consulta devuelve una gran parte de la tabla, escanearla completa es mejor que recorrer un índice millones de veces.

El ejemplo: escanear orders y conservar las filas donde amount > 100. Si la mayoría de los pedidos supera 100, un escaneo secuencial es correcto.

Un Index Scan utiliza un árbol B para saltar directamente a las claves coincidentes y, después, leer las filas correspondientes del heap de la tabla.

Es especialmente eficaz cuando el filtro es selectivo y devuelve una pequeña parte de la tabla. Buscar 5 filas mediante un índice es mejor que leer 10 millones.

El plan indica el índice que ha utilizado. Cada coincidencia cuesta una búsqueda en el índice más una obtención desde el heap (una lectura aleatoria), por lo que los escaneos mediante índice pierden su ventaja cuando devuelven demasiadas filas.

El concepto único que determina todo esto es la selectividad: la fracción de filas que conserva un predicado.

• Una selectividad alta (coinciden pocas filas, como con un identificador único) favorece un Index Scan.
• Una selectividad baja (coinciden muchas filas, como con status IS NOT NULL) favorece un Seq Scan.

Una regla práctica habitual: cuando una consulta devuelve más o menos del 5 al 10 % de una tabla, el planificador suele preferir un escaneo secuencial, porque las obtenciones aleatorias del heap que requiere un índice resultan más costosas que leerlo todo en orden.

Un Index-Only Scan es el más rápido de los tres. Si todas las columnas que necesita la consulta ya están en el índice, el motor no accede al heap de la tabla.

La consulta de ejemplo selecciona únicamente customer_id y aplica el filtro sobre esa columna, y el índice está creado sobre customer_id. Todos los datos necesarios están en el índice, por lo que Postgres informa de un Index Only Scan.

Esto evita las lecturas aleatorias del heap que ralentizan un escaneo normal mediante índice, una gran ventaja en tablas anchas.

A los entrevistadores les encanta este matiz. Un escaneo solo mediante índice aún debe confirmar que cada fila es visible para su transacción (MVCC), y el índice por sí solo no almacena la visibilidad.

Postgres utiliza el mapa de visibilidad: si una página está marcada como all-visible, omite el heap; si no, debe obtener la fila del heap de todos modos. El plan muestra Heap Fetches: N.

Por eso una tabla recién actualizada puede mostrar muchas obtenciones del heap y ralentizar los escaneos solo mediante índice hasta que VACUUM actualiza el mapa de visibilidad.

Hay un cuarto método que aparece con frecuencia: Bitmap Heap Scan. El planificador lo elige cuando un predicado coincide con más filas de las que conviene devolver mediante un escaneo normal con índice, pero con menos filas de las que contiene la tabla completa.

Primero construye un bitmap de las ubicaciones de las filas coincidentes a partir del índice (Bitmap Index Scan) y, después, obtiene las páginas del heap en orden físico en lugar de hacerlo en orden aleatorio. Las obtenciones ordenadas son mucho más económicas que las lecturas dispersas de un escaneo normal mediante índice.

Una pregunta clásica de entrevista: He añadido un índice, pero el plan sigue haciendo un Seq Scan, ¿por qué? Motivos habituales:

• El predicado no es selectivo; escanear es realmente más barato.
• Una función envuelve la columna: WHERE lower(email) = ... no puede utilizar un índice normal sobre email.
• Una incompatibilidad de tipos fuerza una conversión implícita que inutiliza el índice.
• Estadísticas desactualizadas; ejecute ANALYZE.
• La tabla es pequeña; escanear unas pocas páginas es mejor que asumir la sobrecarga de un índice.

Suponga que orders tiene un índice sobre created_at, pero esta consulta aún realiza un escaneo secuencial:

El culpable es DATE(created_at). Al envolver la columna en una función, no se puede utilizar el índice sobre el created_at sin modificar. La solución consiste en reescribir la consulta como un predicado de rango que deje la columna sin envolver, o crear un índice de expresión sobre DATE(created_at).

Para la entrevista, tenga presente esta comparación:

• Seq Scan, mejor al devolver una gran parte de las filas; E/S secuencial.
• Index Scan, mejor para búsquedas selectivas; recorrido del índice más obtenciones aleatorias del heap.
• Bitmap Heap Scan, para una cantidad intermedia de coincidencias; índice a bitmap y, después, lecturas ordenadas del heap.
• Index-Only Scan, el más rápido cuando el índice cubre todas las columnas necesarias y las páginas están marcadas como all-visible.

El planificador elige según el coste estimado, determinado principalmente por la selectividad y las estadísticas.

Para demostrar un punto durante el desarrollo, puede influir temporalmente en el planificador: SET enable_seqscan = off; lo obliga a preferir los índices para que pueda comparar los planes.

Este es un recurso de diagnóstico, nunca una solución para producción. En las entrevistas, mencione que las soluciones reales son disponer de mejores estadísticas, utilizar un índice adecuado o reescribir el predicado, no desactivar globalmente las funciones del planificador.

Una consulta selecciona únicamente email y aplica el filtro sobre email, y existe un índice B-tree sobre email. El plan muestra Index Only Scan. ¿Por qué es más rápido que un Index Scan normal?

Conclusiones clave sobre los métodos de acceso:

• Seq Scan es la mejor opción para consultas con baja selectividad; Index Scan lo es para las selectivas.
• Index-Only Scan evita el heap cuando el índice cubre todas las columnas necesarias; preste atención a Heap Fetches y al mapa de visibilidad.
• Bitmap Heap Scan cubre el punto intermedio al obtener las páginas del heap en orden físico.
• El planificador decide según la selectividad y las estadísticas; las funciones sobre columnas, las incompatibilidades de tipos y las estadísticas desactualizadas son motivos por los que se ignora un índice.