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:

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:

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:

Die View hypopg_list_indexes zeigt uns alle hypothetischen Indizes: 

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

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

 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:

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​

1. Flexibilität bei der Indexoptimierung: HypoPG ermöglicht es, verschiedene Indexkonfigurationen zu testen, um die optimale Leistung für bestimmte Abfragen zu ermitteln.
2. Vermeidung von unnötigem Overhead: Da hypothetische Indizes temporär sind, verursachen sie keinen langfristigen Overhead bei DML-Operationen.
3. 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.

Seminarempfehlung