Versionen vor PostgreSQL 9.2 müssen, auch wenn alle abgefragten Spalten indiziert sind, dennoch immer auf die Tuples in der Tabelle zugreifen. Das liegt daran, dass es für Indizes unter PostgreSQL keine Informationen darüber gibt, ob alle Tuples für die jeweilige Transaktion sichtbar sind.
Gelöst wird dieses Problem dadurch, dass seit Version 9.2 auf die Visibility Map zurückgegriffen wird um die Sichtbarkeit aller Tuples sicherzustellen. Die Visibility Map stellt Informationen über die Sichtbarkeit jedes Tuples in einer 8K Page bereit. Diese Information ist je Page ein Bit groß. Ist dieses Bit gesetzt heißt dass, das alle Tuples innerhalb der angefragten Page für alle Transaktionen zu Verfügung stehen (sichtbar sind). Gesetzt wird das Bit durch VACUUM, gelöscht durch modifizierende Queries, wie INSERT, UPDATE und DELETE.
Schauen wir uns das Ganze anhand eine kurzen Beispiels an.
Wir erstellen eine Tabelle.
CREATE TABLE t1 (id int, price float, name text);
Befüllen diese mit Test-Daten.
INSERT INTO t1 SELECT generate_series(1,10000000), random(), 'foobar';
Wir erstellen einen Index der die Spalten price und id enthält.
CREATE INDEX ON t1 (price,id);
Es fehlt noch ein VACUUM zur Aktualisierung der Visibility Map.
VACUUM t1;
Es folgt ein SELECT auf eine der indizierten Spalten, mit der Ausgabe des Query-Plans.
explain analyze select id from t1 where price between 0.05 and 0.06;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using t1_price_id_idx on t1 (cost=0.43..3362.33 rows=94895 width=4) (actual time=0.018..14.322 rows=99411 loops=1)
Index Cond: ((price >= 0.05::double precision) AND (price <= 0.06::double precision))
Heap Fetches: 0
Total runtime: 18.106 ms
Es wird, was zu erwarten war, ein Index-only Scan genutzt. An der Ausgabe des Query-Plans interessieren uns insbesondere die Kosten und die Laufzeit. Diese werden wir nun mit deaktiviertem Index-only Scan vergleichen. Am Statement selbst ändern wir nichts.
test=# set enable_indexonlyscan = 'off';
SET
test=# explain analyze select id from t1 where price between 0.05 and 0.06;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=2437.11..70640.29 rows=94895 width=4) (actual time=18.704..958.437 rows=99411 loops=1)
Recheck Cond: ((price >= 0.05::double precision) AND (price <= 0.06::double precision))
Rows Removed by Index Recheck: 6153906
-> Bitmap Index Scan on t1_price_id_idx (cost=0.00..2413.38 rows=94895 width=0) (actual time=16.411..16.411 rows=99411 loops=1)
Index Cond: ((price >= 0.05::double precision) AND (price <= 0.06::double precision))
Total runtime: 968.811 ms
Beim nun genutzten Bitmap Heap Scan sehen wir die deutlich schlechtere Performance gegenüber dem zuvor verwendetet Index-only Scan. Die Laufzeit ist von 18ms auf über 960ms angewachsen. Die messbar bessere Performance wird zum Großteil dadurch erreicht, dass deutlich weniger I/O auf den Festplatten benötigt wird. Dies kann mit Hilfe von Tools wie iostat oder vmstat sehr gut beobachtet werden.
Die Vorteile haben wir nun beleuchtet, kommen wir zu den Nachteilen. Man bekommt die Performance nicht geschenkt, Indizes müssen gut geplant und angelegt werden um in den Genuss dieses Features zu kommen. Es können sogar zusätzliche Indizes von Vorteil sein. Hierbei sollte man allerdings auch immer die Update-Performance seiner Datenbank im Auge behalten. Durch zusätzlich Indizierte Spalten sinkt generell die Chance auf HOT-Updates. Darüber hinaus ist eine gute VACUUM-Policy essentiell, da nur ein regelmäßiger VACUUM für eine aktuelle Visibilty Map sorgt.
