PostgreSQL CREATE INDEX

Indizes in PostgreSQL sind Datenbankobjekte, die die Geschwindigkeit von Datenabfragen verbessern. Sie dienen als Zeiger, um Zeilen in einer Tabelle schnell zu finden und die Leistung von Abfragen zu verbessern.

Verwendung

Indizes werden verwendet, um die Leistung von SELECT-Abfragen und WHERE-Klauseln zu optimieren. Sie sind besonders nützlich für große Tabellen, bei denen ein schneller Datenzugriff erforderlich ist.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

In dieser Syntax definiert `CREATE INDEX` einen neuen Index namens `index_name` für die angegebene `Tabelle_name` und die Spalte(n).

Beispiele

1. Grundlegende Indexerstellung

CREATE INDEX idx_customer_name
ON customers (customer_name);

In diesem Beispiel wird ein Index für die Spalte "customer_name" in der Tabelle "customers" erstellt, der die Suche nach dieser Spalte beschleunigt.

2. Komposit-Index

CREATE INDEX idx_order_customer
ON orders (customer_id, order_date);

Dieser zusammengesetzte Index für die Tabelle "Bestellungen" verwendet die Spalten "customer_id" und "order_date" und steigert so die Leistung von Abfragen, die nach diesen Spalten filtern. Zusammengesetzte Indizes sind von Vorteil, wenn Abfragen häufig mehrere Spalten in ihren WHERE-Klauseln enthalten.

3. Einzigartiger Index

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

Ein eindeutiger Index stellt sicher, dass alle Werte in der indizierten Spalte `E-Mail` in der Tabelle `Benutzer` eindeutig sind, verhindert doppelte Einträge und optimiert die eindeutige Suche.

4. Partieller Index

CREATE INDEX idx_active_users
ON users (last_login_date)
WHERE active = true;

Es wird ein Teilindex für die Spalte "last_login_date" erstellt, der sich nur auf die Zeilen bezieht, in denen "active" wahr ist, um Abfragen zu optimieren, die auf diese Teilmenge von Daten abzielen.

5. Volltext-Index

CREATE INDEX idx_document_content
ON documents USING gin(to_tsvector('english', content));

In diesem Beispiel wird ein Volltextindex für die Spalte "Inhalt" der Tabelle "Dokumente" erstellt, um die Leistung von Volltextsuchanfragen zu verbessern.

Auswirkungen der Indizierung

Indizes können die Leistung von Leseabfragen erheblich verbessern, aber auch die Leistung von Schreiboperationen wie `INSERT`, `UPDATE` und `DELETE` beeinträchtigen. Der Kompromiss besteht darin, einen schnelleren Datenabruf mit einer potenziell langsameren Datenänderung in Einklang zu bringen.

Tipps und bewährte Praktiken

• Selektiv indizieren. Indexiere nur Spalten, die häufig in Abfragebedingungen verwendet werden, um unnötigen Overhead zu vermeiden.
• Überwache und pflege. Analysiere und pflege die Indizes regelmäßig, um sicherzustellen, dass sie effektiv genutzt werden.
• Hüte dich vor Überindizierung. Eine übermäßige Indexierung kann Datenänderungen wie `INSERT`, `UPDATE` und `DELETE` verlangsamen.
• Verwende partielle Indizes. Ziehe partielle Indizes für Spalten mit einer hohen Anzahl von NULLs oder eine bestimmte Teilmenge von Daten in Betracht.
• Einzigartige Indizes nutzen. Verwende eindeutige Indizes, um die Datenintegrität zu gewährleisten und die Abfrageleistung zu verbessern.
• Erweiterte Funktionen. Untersuche Indizierungsoptionen wie Expression-Indizes für berechnete Werte oder Covering-Indizes, um zusätzliche Spalten in den Index aufzunehmen.
• Verwende REINDEX. Verwende regelmäßig den Befehl `REINDEX`, um beschädigte Indizes wiederherzustellen oder die Leistung nach größeren Datenänderungen zu verbessern.