Erklärt: insert-only Tabellen, VACUUM und index-only scans

[akretschmer]

Ein neues Feature in PG 13 ist, daß per default der AUTOVACUUM-Prozess nun auch reiner Insert-Only - Tabellen bearbeitet. Doch warum ist dies sinnvoll? Ein Beispiel:

Zuerst erstellen wir eine Tabelle und schalten dieses Feature gezielt ab, indem wir 2 neue Parameter, autovacuum_vacuum_insert_threshold und autovacuum_vacuum_insert_scale_factor, so setzen, daß Autovacuum diese Tabelle nie bearbeiten wird:

test=*# create table demo(i int primary key, val text) with(autovacuum_vacuum_insert_threshold = -1);
CREATE TABLE
test=*# insert into demo select s, md5(s::text) from generate_series(1, 1000000) s;
INSERT 0 1000000
test=*# commit;
COMMIT

Nun eine Abfrage, die via Index-Only - Scan erfüllt wird:

test=# explain analyse select i from demo where i in (1,100,500, 1000, 2000, 3000, 10000, 20000, 30000, 40000, 50000, 60000, 70000);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.42..61.75 rows=13 width=4) (actual time=0.031..0.085 rows=13 loops=1)
   Index Cond: (i = ANY ('{1,100,500,1000,2000,3000,10000,20000,30000,40000,50000,60000,70000}'::integer[]))
   Heap Fetches: 13
 Planning Time: 0.662 ms
 Execution Time: 0.098 ms
(5 rows)

Es gibt hier ein Detail, was zu beachten ist:

   Heap Fetches: 13

Auch wenn die gesuchten Informationen aus dem Index (die Spalte i ist ja der PRIMARY KEY und hat somit einen Index) MUß nochmals in die Tabelle geschaut werden, ob diese Datensätze in dieser Transaktion sichtbar sind. Damit haben wir 'auf dem Papier' zwar einen schnellen Index-Only-Scan, praktisch aber einen normalen Indexscan.

Warum ist das so?

Damit ein Index-Only-Scan wirklich nur die Informationen aus dem Index nutzen kann, gibt es für jede Tabelle noch eine weitere Struktur: die Visibility-Map. Das sind die .vm - Dateien. Diese beinhalten für jeden 8 KByte - Block der Tabelle ein Flag, ob alle Rows in diesem Block in allen Transaktionen sichtbar sind. Ist dies der Fall, können die Daten aus dem Index direkt genutzt werden, ist das Flag nicht gesetzt, muß in der Tabelle (Heap) nochmals die Sichtbarkeit des Datensatzes in dieser Transaktion geprüft werden.

Diese visibility map wird durch den Autovacuum-Prozess immer mit aktualisiert. Und hier ist das Problem: Da es keine Deletes / Updates gibt, beachtet Autovacuum diese Tabelle nicht - bis einschl. Version 12. Daher ist die VM also leer und es kommt zum Heap Fetch.

Ich setze nun diese Eigenschaft auf die Default-Werte:

test=*# alter table demo set (autovacuum_vacuum_insert_threshold = 1000);
ALTER TABLE

Ein weiterer Parameter ist autovacuum_vacuum_insert_scale_factor, dieser ist default auf 0.2, also 20% der Anzahl Rows gemäß den Werten in pg_class.

Mit anderen Worten: nach 20% Inserts (bezogen auf die Tabellengröße) + 1000 Inserts wird ein Autovacuum durchgeführt.

Mittlerweile ist dies erfolgt, der Plan ändert sich zu:

test=*# explain analyse select i from demo where i in (1,100,500, 1000, 2000, 3000, 10000, 20000, 30000, 40000, 50000, 60000, 70000);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.42..57.75 rows=13 width=4) (actual time=0.025..0.067 rows=13 loops=1)
   Index Cond: (i = ANY ('{1,100,500,1000,2000,3000,10000,20000,30000,40000,50000,60000,70000}'::integer[]))
   Heap Fetches: 0
 Planning Time: 0.126 ms
 Execution Time: 0.080 ms

Wir sehen: reduzierte Kosten und Heap Fetches = 0. Nun haben wir den vollen Vorteil von Index-Only-Scans.
Wer Version 12 oder kleiner benutzt und solche Insert-Only-Tabellen hat und den Vorteil von Index-Only-Scans nutzen will sollte also ab und an ein manuelles Vacuum dieser Tabellen expliziet durchführen.