5 Minuten Lesezeit (1001 Worte)

Kurz und gut – Episode #19 Was wäre wenn? - Hypothetische Indizes in PostgreSQL mit HypoPG

Freitag, 21. Juni 2024

Meine Abfrage ist langsam - vielleicht hilft mir ein Index? Aber wie kann ich das testen, ohne zuerst aufwändig den Index anzulegen und dann den Ausführungsplan zu überprüfen? Bei PostgreSQL hilft die Erweiterung HypoPG mit ihren hypothetischen Indizes bei der Beantwortung dieser Frage.

HypoPG ist eine Open-Source-Erweiterung für PostgreSQL, die es ermöglicht, verschiedene Indexkonfigurationen zu testen und zu analysieren. Sie erlaubt es, hypothetische Indizes zu erstellen und die Auswirkungen auf die Ausführungspläne von Abfragen zu testen.

Was sind hypothetische Indizes?

Hypothetische Indizes sind temporäre Indizes, die nicht dauerhaft in der Datenbank gespeichert werden. Im Gegensatz zu regulären Indizes, die einen beträchtlichen Overhead bei Aktualisierungen, Einfügungen und Löschungen verursachen können, werden hypothetische Indizes nur für die Dauer einer Sitzung oder einer Transaktion erstellt. Diese temporären Indizes ermöglichen es Entwickler:innen und Datenbankadministrator:innen, die Auswirkungen potenzieller Indizes auf die Leistung zu testen, ohne tatsächlich dauerhafte Änderungen an der Datenbankstruktur vorzunehmen.

Wie funktioniert HypoPG?

HypoPG erweitert PostgreSQL, um temporäre hypothetische Indizes zu unterstützen. Um einen hypothetischen Index zu erstellen, wird die Funktion hypopg_create_index mit den gewünschten Indexparametern aufgerufen. Dieser Index bleibt während der Sitzung oder Transaktion aktiv und wird beim Beenden der Sitzung oder Transaktion automatisch entfernt. Der Prozess ist einfach und erfordert keine dauerhaften Änderungen an der Datenbank.

Vorbereitungen

Installation (RHEL/PostgreSQL 16)

# dnf install -y hypopg_16

Alternativ kann der Source-Code auch von der Projektseite auf Github heruntergeladen und selbst kompiliert werden.

Testdaten

Wir testen die Erweiterung mit den Daten der PostgreSQL-Demo-Datenbank "bookings".

Die Tabelle boarding_passes der Demo-Datenbank hat folgende Struktur:

[local] postgres@demo=# \d bookings.boarding_passes 

                  Table "bookings.boarding_passes" 

   Column    |         Type         | Collation | Nullable | Default 
-------------+----------------------+-----------+----------+--------- 
 ticket_no   | character(13)        |           | not null | 
 flight_id   | integer              |           | not null | 
 boarding_no | integer              |           | not null | 
 seat_no     | character varying(4) |           | not null | 

Indexes: 

    "boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id) 

    "boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no) 

    "boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no) 

Foreign-key constraints: 

    "boarding_passes_ticket_no_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

Die Tabelle hat 7925812 Datensätze.

Wir sehen, dass die Tabelle keinen Index auf die Spalte seat_no hat. Demzufolge dauern Abfragen auf die Spalte entsprechend lange, denn PostgreSQL muss einen Sequential Scan auf die gesamte Tabelle machen. Dazu ein Beispiel:

[local] postgres@demo=# select count(distinct seat_no) from bookings.boarding_passes; 
 count 
------- 
   461 
(1 row) 
 
Time: 15025.857 ms (00:15.026) 
[local] postgres@demo=# explain select count(distinct seat_no) from bookings.boarding_passes; 
QUERY PLAN 
 Aggregate  (cost=1148885.07..1148885.08 rows=1 width=8) 
   ->  Sort  (cost=1109256.01..1129070.54 rows=7925812 width=3) 
         Sort Key: seat_no 
         ->  Seq Scan on boarding_passes  (cost=0.00..137562.12 rows=7925812 width=3) 
 JIT: 
   Functions: 5 
   Options: Inlining true, Optimization true, Expressions true, Deforming true 
(7 rows) 
 
Time: 2.272 ms

Würde uns ein Index auf die Spalte "seat_no" bei dieser Abfrage helfen? Das testen wir jetzt mit der Erweiterung HypoPG.

Der Index wird in diesem Fall nicht mit "create index", sondern mit der Funktion hypopg_create_index angelegt:

[local] postgres@demo=# select hypopg_create_index('create index i_demo on bookings.boarding_passes(seat_no)'); 
                  hypopg_create_index 
------------------------------------------------------- 
 (13554,<13554>btree_bookings_boarding_passes_seat_no) 
(1 row) 
 
Time: 2.232 ms

Die View hypopg_list_indexes zeigt uns alle hypothetischen Indizes:

[local] postgres@demo=# select * from hypopg_list_indexes; 
 indexrelid |                  index_name                   | schema_name |   table_name    | am_name 
------------+-----------------------------------------------+-------------+-----------------+--------- 
      13554 | <13554>btree_bookings_boarding_passes_seat_no | bookings    | boarding_passes | btree 
(1 row) 
 
Time: 7.476 ms

Es gibt keinen "realen Index" auf die Spalte seat_no, denn der hypothetische Index existiert nur im Speicher der aktuellen Datenbank-Sitzung.

[local] postgres@demo=# select indexname,indexdef from pg_indexes where tablename='boarding_passes';
-[ RECORD 1 ]---------------------------------------------- 
indexname | boarding_passes_flight_id_boarding_no_key 
indexdef  | CREATE UNIQUE INDEX boarding_passes_flight_id_boarding_no_key ON bookings.boarding_passes USING btree (flight_id, boarding_no) 
-[ RECORD 2 ]---------------------------------------------- 
indexname | boarding_passes_flight_id_seat_no_key 
indexdef  | CREATE UNIQUE INDEX boarding_passes_flight_id_seat_no_key ON bookings.boarding_passes USING btree (flight_id, seat_no) 
-[ RECORD 3 ]---------------------------------------------- 
indexname | boarding_passes_pkey 
indexdef  | CREATE UNIQUE INDEX boarding_passes_pkey ON bookings.boarding_passes USING btree (ticket_no, flight_id) 
 
Time: 5.620 ms

Der "EXPLAIN"-Befehl zeigt den Ausführungsplan, den die Abfrage "hypothetisch", d. h. mit dem neuen Index, machen würde:

[local] postgres@demo=# explain select count(distinct seat_no) from bookings.boarding_passes;
 
QUERY PLAN 
 Aggregate  (cost=165144.67..165144.68 rows=1 width=8) 
   ->  Index Only Scan using "<13554>btree_bookings_boarding_passes_seat_no" on boarding_passes  (cost=0.06..145330.14 rows=79258 
12 width=3) 
 JIT: 
   Functions: 2 
   Options: Inlining false, Optimization false, Expressions true, Deforming true 
(5 rows) 
 
Time: 2.079 ms

An dem Index-Namen sieht man, dass die Abfrage den hypothetischen Index nutzen würde.

Nachdem wir nun gesehen haben, dass die Abfrage den Index nutzen würde, können wir den hypothetischen Index droppen und ihn als richtigen Index anlegen.

Zum Droppen des hypothetischen Indexes müssen wir die angegebene Index-Nummer nutzen:

[local] postgres@demo=# select hypopg_drop_index('13554');
 hypopg_drop_index 
------------------- 
 t 
(1 row) 
 
Time: 2.765 ms

Alternativ können wir auch einfach die Session, in der wir den hypothetischen Index angelegt haben, beenden, denn der Index existiert nur im Speicher der Session und nicht in der Datenbank.

Vorteile von HypoPG

Flexibilität bei der Indexoptimierung: HypoPG ermöglicht es, verschiedene Indexkonfigurationen zu testen, um die optimale Leistung für bestimmte Abfragen zu ermitteln.
Vermeidung von unnötigem Overhead: Da hypothetische Indizes temporär sind, verursachen sie keinen langfristigen Overhead bei DML-Operationen.
Schnelle und einfache Analyse: Mit der Fähigkeit, hypothetische Indizes während einer Sitzung zu erstellen und zu testen, können Entwickler:innen und DBAs schnell feststellen, welche Indizes die gewünschten Leistungsverbesserungen bringen.

Weitere Informationen zu PostgreSQL gibt es in unserem Seminar "PostgreSQL-Administration".