tutorial - ¿Por qué PostgreSQL realiza un escaneo secuencial en una columna indexada?

tipos de indices en postgresql

Question

Ejemplo muy simple: una tabla, un índice, una consulta:

CREATE TABLE book
(
  id bigserial NOT NULL,
  "year" integer,
  -- other columns...
);

CREATE INDEX book_year_idx ON book (year)

EXPLAIN
 SELECT *
   FROM book b
  WHERE b.year > 2009

me da:

Seq Scan on book b  (cost=0.00..25663.80 rows=105425 width=622)
  Filter: (year > 2009)

¿Por qué NO realiza el análisis de índice en su lugar? ¿Qué me estoy perdiendo?

Answer 1

¿ ANALYZE la tabla / base de datos? ¿Y las statistics ? Cuando hay muchos registros en el año> 2009, un escaneo secuencial puede ser más rápido que un escaneo de índice.

Answer 2

Si el SELECT devuelve más de aproximadamente el 5-10% de todas las filas en la tabla, un escaneo secuencial es mucho más rápido que un escaneo de índice.

Esto se debe a que un análisis de índice requiere varias operaciones de IO para cada fila (busque la fila en el índice y luego recupere la fila del montón). Mientras que un escaneo secuencial solo requiere un IO individual para cada fila, o incluso menos porque un bloque (página) en el disco contiene más de una fila, por lo que se puede obtener más de una fila con una sola operación IO.

Por cierto: esto también es cierto para otros DBMS: algunas optimizaciones como "escaneos de solo índice" se toman a un lado (pero para un SELECT * es muy poco probable que un DBMS así sea para un "escaneo indexable")