- Einführung von unkonventionellen Methoden zur Steigerung der PostgreSQL-Abfrageleistung, die sich vom üblichen Schema lösen und statt zusätzlicher Indizes oder Query-Rewrites drei kreative Ansätze vorstellen
- Vermeidung von Full Table Scans auf Basis von CHECK-Constraints, Optimierung niedriger Kardinalität mit funktionsbasierten Indizes und Umsetzung von Unique Constraints mit Hash-Indizes
- 1. Nutzung der Einstellung
constraint_exclusion, um bei Abfragen mit falschen Bedingungen unnötige Scans zu verhindern
- 2. Einsatz funktionsbasierter Indizes und virtueller generierter Spalten (virtual generated columns), um die Indexgröße zu verringern und Query-Konsistenz sicherzustellen
- 3. Kombination von Hash-Indizes und Exclusion Constraints für eine effiziente Umsetzung von Unique Constraints bei großen Textspalten, mit deutlicher Einsparung von Speicherplatz
Vermeidung von Full Table Scans mit CHECK-Constraints
- Obwohl für die Spalte
plan ein CHECK-Constraint existiert, das nur die Werte 'free' und 'pro' zulässt, führt PostgreSQL bei einer fehlerhaften Abfrage mit 'Pro' einen Full Table Scan aus
- Im Ausführungsplan werden alle 100.000 Zeilen gelesen, das tatsächliche Ergebnis sind jedoch 0 Zeilen
- Wird der Parameter
constraint_exclusion auf 'on' gesetzt, berücksichtigt PostgreSQL die Constraints und überspringt den Scan vollständig
- Die Ausführungszeit sinkt von 7,4 ms auf 0,008 ms
- Der Standardwert ist
'partition'; bei einfachen Abfragen kann dadurch der Planungs-Overhead sogar steigen
- In BI- und Reporting-Umgebungen ist die Einstellung
'on' jedoch nützlich, da Nutzer dort häufig fehlerhafte Bedingungen eingeben
Optimierung niedriger Kardinalität mit funktionsbasierten Indizes
- In der Tabelle
sale mit 10 Millionen Verkaufsdatensätzen benötigt eine Query zur täglichen Umsatzaggregation bei einem Full Table Scan 627 ms
- Ein zusätzlicher B-Tree-Index auf der Spalte
sold_at verkürzt die Zeit auf 187 ms, der Index wächst jedoch auf 214 MB an
- Wird ein funktionsbasierter Index auf dem Ausdruck
date_trunc('day', sold_at) erstellt, schrumpft die Größe auf 66 MB und die Ausführungszeit verbessert sich weiter auf 145 ms
- Dank der niedrigen Kardinalität ist Index-Deduplication möglich
- Allerdings muss der Query-Ausdruck exakt mit der Indexdefinition übereinstimmen, daher ist Konsistenz der Ausdrücke erforderlich
- Dafür kann ein VIEW mit demselben Ausdruck erstellt werden, oder
- es kann eine virtuelle generierte Spalte (virtual generated column) hinzugefügt werden, die ab PostgreSQL 18 unterstützt wird, um diese Konsistenz zu automatisieren
- Mit virtuellen generierten Spalten wird der Index automatisch genutzt, sodass sich kleiner Index, schnellere Queries und Ausdruckskonsistenz gleichzeitig erreichen lassen
- In PostgreSQL 18 wird die Erstellung von Indizes auf virtuellen Spalten allerdings noch nicht unterstützt; geplant ist dies für Version 19
Umsetzung von Unique Constraints mit Hash-Indizes
- In der Tabelle
urls, die lange URLs speichert, erreicht ein B-Tree-basierter Unique-Index zur Vermeidung doppelter URLs eine Größe von 154 MB
- Ein Hash-Index speichert nicht den eigentlichen Wert, sondern nur den Hashwert und ist deshalb deutlich kleiner
- PostgreSQL unterstützt standardmäßig zwar keine Unique-Hash-Indizes,
- über ein Exclusion Constraint lässt sich ein Unique Constraint jedoch indirekt in der Form
EXCLUDE USING HASH (url WITH =) umsetzen
- Auch mit dieser Methode tritt bei doppelten Inserts ein Fehler auf, und die Query-Performance ist ebenfalls besser als mit B-Tree (0,022 ms vs. 0,046 ms)
- Die Indexgröße beträgt 32 MB und ist damit mehr als fünfmal kleiner als bei B-Tree
- Nachteile:
- keine Referenzierung per Fremdschlüssel möglich (
REFERENCES-Constraint nicht möglich)
- eingeschränkte Kompatibilität mit der Syntax
INSERT ... ON CONFLICT
- stattdessen können
ON CONFLICT ON CONSTRAINT oder MERGE verwendet werden
- Hash-Indizes eignen sich zur Sicherstellung der Eindeutigkeit großer Textspalten und sind dann eine platzsparende Alternative, wenn keine Fremdschlüssel benötigt werden
Noch keine Kommentare.