PostgreSQL Indexoptimierung

Einführung

In diesem Lab lernen Sie, wie Sie die Leistung der PostgreSQL-Datenbank durch Indizierung optimieren können. Sie beginnen mit der Erstellung einer Beispiel-users-Tabelle und dem Befüllen dieser mit Daten. Anschließend erstellen Sie einen Einzelspaltenindex, analysieren Abfragepläne mit EXPLAIN, erstellen einen Mehrspaltenindex und lernen schließlich, wie Sie einen nicht verwendeten Index entfernen, um die Effizienz der Datenbank aufrechtzuerhalten. Diese praktische Erfahrung vermittelt Ihnen praktische Fähigkeiten im PostgreSQL-Indexmanagement (Indexverwaltung).

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/idx_simple("Create Simple Index") postgresql/PostgreSQLGroup -.-> postgresql/idx_drop("Drop Existing Index") subgraph Lab Skills postgresql/table_init -.-> lab-550955{{"PostgreSQL Indexoptimierung"}} postgresql/row_add -.-> lab-550955{{"PostgreSQL Indexoptimierung"}} postgresql/data_where -.-> lab-550955{{"PostgreSQL Indexoptimierung"}} postgresql/idx_simple -.-> lab-550955{{"PostgreSQL Indexoptimierung"}} postgresql/idx_drop -.-> lab-550955{{"PostgreSQL Indexoptimierung"}} end

Erstellen eines Einzelspaltenindex (Single-Column Index)

In diesem Schritt erstellen Sie eine Beispieltabelle namens users und anschließend einen Einzelspaltenindex für die Spalte email. Indizes sind entscheidend für die Verbesserung der Leistung von Datenbankabfragen (Database Queries), insbesondere bei großen Tabellen.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank:

sudo -u postgres psql

Erstellen Sie nun die Tabelle users. Führen Sie den folgenden SQL-Befehl aus:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

Dieser Befehl erstellt eine Tabelle namens users mit Spalten für id, username, email und created_at. Die Spalte id ist der Primärschlüssel (Primary Key) und wird automatisch inkrementiert.

Fügen Sie als Nächstes einige Beispieldaten in die Tabelle users ein. Führen Sie die folgenden SQL-Befehle aus:

INSERT INTO users (username, email, created_at) VALUES
('john_doe', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('jane_smith', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('peter_jones', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('mary_brown', '[email protected]', NOW());

Sie haben nun vier Datenzeilen in die Tabelle users eingefügt.

Um Abfragen basierend auf der Spalte email zu beschleunigen, erstellen Sie einen Index für die Spalte email. Führen Sie den folgenden SQL-Befehl aus:

CREATE INDEX idx_users_email ON users (email);

Dieser Befehl erstellt einen Index namens idx_users_email für die Spalte email der Tabelle users.

Um zu überprüfen, ob der Index erstellt wurde, können Sie den Befehl \di in psql verwenden. Führen Sie den folgenden Befehl aus:

\di

Sie sollten den Index idx_users_email in der Ausgabe sehen.

Beenden Sie abschließend die psql-Shell, indem Sie Folgendes eingeben:

\q

Verwenden von EXPLAIN zur Analyse von Abfrageplänen (Query Plans)

In diesem Schritt lernen Sie, wie Sie den Befehl EXPLAIN in PostgreSQL verwenden, um Abfragepläne zu analysieren. Das Verständnis von Abfrageplänen ist entscheidend für die Optimierung von Datenbankabfragen und die Sicherstellung einer effizienten Leistung.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank:

sudo -u postgres psql

Verwenden wir nun den Befehl EXPLAIN, um eine einfache Abfrage zu analysieren. Führen Sie den folgenden Befehl aus:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Dieser Befehl zeigt den Abfrageplan für die SELECT-Anweisung an. Die Ausgabe zeigt, wie PostgreSQL die Abfrage ausführen möchte, einschließlich der Frage, ob ein Index verwendet wird.

Um detailliertere Informationen, einschließlich der Kosten (Cost), zu erhalten, können Sie EXPLAIN ANALYZE verwenden. Für dieses einfache Beispiel ist EXPLAIN jedoch ausreichend.

Analysieren wir nun eine Abfrage, die den Index möglicherweise nicht verwendet. Führen Sie den folgenden Befehl aus:

EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

Die Ausgabe zeigt wahrscheinlich einen "Seq Scan" (Sequential Scan - sequenzieller Scan) auf der Tabelle users, was bedeutet, dass PostgreSQL die gesamte Tabelle durchsucht, um die übereinstimmenden Zeilen zu finden. Dies ist weniger effizient als die Verwendung eines Index.

Durch die Analyse von Abfrageplänen mit EXPLAIN können Sie potenzielle Leistungsengpässe (Performance Bottlenecks) identifizieren und feststellen, ob Ihre Indizes effektiv genutzt werden.

Beenden Sie abschließend die psql-Shell, indem Sie Folgendes eingeben:

\q

Erstellen eines Mehrspaltenindex (Multi-Column Index)

In diesem Schritt lernen Sie, wie Sie einen Mehrspaltenindex in PostgreSQL erstellen. Ein Mehrspaltenindex ist ein Index, der zwei oder mehr Spalten enthält. Er kann die Abfrageleistung (Query Performance) erheblich verbessern, wenn Abfragen gleichzeitig nach mehreren Spalten filtern oder sortieren.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank:

sudo -u postgres psql

Nehmen wir an, Sie fragen die Tabelle users häufig basierend auf den Spalten username und email ab. Um diese Abfragen zu optimieren, können Sie einen Mehrspaltenindex für diese beiden Spalten erstellen. Führen Sie den folgenden SQL-Befehl aus:

CREATE INDEX idx_users_username_email ON users (username, email);

Dieser Befehl erstellt einen Index namens idx_users_username_email für die Spalten username und email der Tabelle users. Die Reihenfolge der Spalten in der Indexdefinition ist wichtig. Der Index ist am effektivsten, wenn die Abfrage nach den Spalten in der gleichen Reihenfolge filtert, in der sie im Index erscheinen.

Um zu überprüfen, ob der Index erstellt wurde, können Sie den Befehl \di in psql verwenden. Führen Sie den folgenden Befehl aus:

\di

Sie sollten den Index idx_users_username_email in der Ausgabe sehen.

Analysieren wir nun eine Abfrage, die von diesem Mehrspaltenindex profitieren kann. Führen Sie den folgenden Befehl aus:

EXPLAIN SELECT * FROM users WHERE username = 'john_doe' AND email = '[email protected]';

Die Ausgabe sollte zeigen, dass PostgreSQL den Index idx_users_username_email verwendet, um die Abfrage auszuführen.

Beenden Sie abschließend die psql-Shell, indem Sie Folgendes eingeben:

\q

Entfernen eines ungenutzten Index

In diesem Schritt lernen Sie, wie Sie einen ungenutzten Index in PostgreSQL entfernen. Indizes verbessern die Abfrageleistung (Query Performance), verbrauchen aber auch Speicherplatz und können Schreibvorgänge (Einfügungen, Aktualisierungen und Löschungen) verlangsamen. Daher ist es wichtig, Indizes zu identifizieren und zu entfernen, die nicht mehr verwendet werden.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank:

sudo -u postgres psql

Nehmen wir an, dass Sie nach der Analyse Ihrer Abfragemuster feststellen, dass der Index idx_users_email selten verwendet wird. Um diesen Index zu entfernen, führen Sie den folgenden SQL-Befehl aus:

DROP INDEX idx_users_email;

Dieser Befehl entfernt den Index namens idx_users_email aus der Datenbank.

Um zu überprüfen, ob der Index entfernt wurde, können Sie den Befehl \di in psql verwenden. Führen Sie den folgenden Befehl aus:

\di

Sie sollten den Index idx_users_email nicht mehr in der Ausgabe sehen.

Bevor Sie einen Index löschen (dropping), ist es wichtig sicherzustellen, dass er wirklich ungenutzt ist. Sie können den Statistik-Collector (Statistics Collector) von PostgreSQL verwenden, um Informationen über die Indexnutzung zu sammeln. Das Aktivieren und Analysieren dieser Statistiken geht jedoch über den Rahmen dieses Labs hinaus. In einem realen Szenario würden Sie die Indexnutzung über einen bestimmten Zeitraum überwachen, bevor Sie sich entscheiden, einen Index zu löschen.

Das Löschen des falschen Index kann die Abfrageleistung negativ beeinflussen. Seien Sie daher vorsichtig und analysieren Sie Ihre Abfragemuster gründlich, bevor Sie einen Index entfernen.

Beenden Sie abschließend die psql-Shell, indem Sie Folgendes eingeben:

\q

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Single-Column- (Einspalten-) und Multi-Column-Indizes (Mehrspaltenindizes) in PostgreSQL erstellen, um die Abfrageleistung (Query Performance) zu verbessern. Sie haben auch gelernt, wie Sie Abfragepläne mit EXPLAIN analysieren, um festzustellen, ob Ihre Indizes effektiv genutzt werden. Abschließend haben Sie gelernt, wie Sie einen ungenutzten Index entfernen, um die Datenbankeffizienz aufrechtzuerhalten. Diese Fähigkeiten sind für die Optimierung der PostgreSQL-Datenbankleistung unerlässlich.