La tabla se ve algo como esto:
CREATE TABLE "audit_log" ( "id" int4 NOT NULL DEFAULT nextval('audit_log_id_seq'::regclass), "entity" varchar(50) COLLATE "public"."ci", "updated" timestamp(6) NOT NULL, "transaction_id" uuid, CONSTRAINT "PK_audit_log" PRIMARY KEY ("id") );Contiene millones de filas.
Intenté agregar un índice en una columna como esta:
CREATE INDEX "testing" ON "audit_log" USING btree ( "entity" COLLATE "public"."ci" "pg_catalog"."text_ops" ASC NULLS LAST );Luego ejecutó la siguiente consulta sobre la columna indexada y la clave principal:
EXPLAIN ANALYZE SELECT entity, id FROM audit_log WHERE entity = 'abcd'Como esperaba, el plan de consulta utiliza un escaneo de índice de mapa de bits (para encontrar la columna 'entidad', presumiblemente) y un escaneo de montón de mapa de bits (para recuperar la columna 'id', supongo):
Gather (cost=2640.10..260915.23 rows=87166 width=122) (actual time=2.828..3.764 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on audit_log (cost=1640.10..251198.63 rows=36319 width=122) (actual time=0.061..0.062 rows=0 loops=3) Recheck Cond: ((entity)::text = '1234'::text) -> Bitmap Index Scan on testing (cost=0.00..1618.31 rows=87166 width=0) (actual time=0.036..0.036 rows=0 loops=1) Index Cond: ((entity)::text = '1234'::text)A continuación, agregué una columna INCLUDE al índice para que cubriera la consulta anterior:
DROP INDEX testing CREATE INDEX testing ON audit_log USING btree ( "entity" COLLATE "public"."ci" "pg_catalog"."text_ops" ASC NULLS LAST ) INCLUDE ( "id" )Luego volví a ejecutar mi consulta, pero aún hace el escaneo de montón de mapa de bits:
Gather (cost=2964.10..261239.23 rows=87166 width=122) (actual time=2.711..3.570 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on audit_log (cost=1964.10..251522.63 rows=36319 width=122) (actual time=0.062..0.062 rows=0 loops=3) Recheck Cond: ((entity)::text = '1234'::text) -> Bitmap Index Scan on testing (cost=0.00..1942.31 rows=87166 width=0) (actual time=0.029..0.029 rows=0 loops=1) Index Cond: ((entity)::text = '1234'::text)¿Porqué es eso?
PostgreSQL implementa el control de versiones de filas utilizando un concepto llamado visibilidad . Cada consulta sabe qué versión de una fila puede ver.
Ahora que la información de visibilidad se almacena en la fila de la tabla, pero no en la entrada del índice, por lo que se debe visitar esa tabla solo para probar si la fila es visible o no.
Por eso, cada escaneo de índice de mapa de bits necesita un escaneo de montón de mapa de bits.
Para superar la desafortunada propiedad, PostgreSQL ha introducido el mapa de visibilidad , una estructura de datos que almacena para cada bloque de 8kB de la tabla si todas las filas de ese bloque son visibles para todos. Si ese es el caso, se puede omitir la búsqueda de la fila de la tabla. Esto solo es posible para un escaneo de índice normal, no para un escaneo de índice de mapa de bits.
Ese mapa de visibilidad lo mantiene VACUUM . Así que ejecute VACUUM en la tabla, luego puede obtener un escaneo de solo índice en la tabla.
Si eso por sí solo no es suficiente, puede intentar CLUSTER para reescribir la tabla en orden de índice.
Alguna información detallada sobre cómo PostgreSQL estima el costo de un escaneo de índice. El siguiente código es de cost_index en src/backend/optimizer/path/costsize.c :
/*---------- [...] * If it's an index-only scan, then we will not need to fetch any heap * pages for which the visibility map shows all tuples are visible. * Hence, reduce the estimated number of heap fetches accordingly. * We use the measured fraction of the entire heap that is all-visible, * which might not be particularly relevant to the subset of the heap * that this query will fetch; but it's not clear how to do better. *---------- */ [...] if (indexonly) pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac)); allvisfrac se calcula utilizando pg_class.relallvisible , que contiene una estimación del número de páginas visibles en la tabla, y pg_class.relpages .