In der Postgres-Welt sind Indizes entscheidend für die effiziente Navigation in einem Datenbank-Repository (Heap, Heap genannt). Postgres unterstützt kein Clustering und die MVCC-Architektur führt dazu, dass Sie viele Versionen desselben Tupels ansammeln. Daher ist es sehr wichtig, effektive Indizes zur Unterstützung von Anwendungen erstellen und verwalten zu können.
Hier finden Sie einige Tipps zur Optimierung und Verbesserung der Verwendung von Indizes.
Hinweis: Die unten gezeigten Abfragen funktionieren in einer unveränderten Pagila-Beispieldatenbank .Abdecken von Indizes
Lassen Sie uns die Anforderung zum Abrufen von E-Mail-Adressen für inaktive Benutzer überprüfen. Die
customer hat eine
active Spalte und die Anfrage ist einfach:
pagila=
Die Abfrage ruft die vollständige Sequenz des Scannens der
customer . Erstellen wir einen Index für die
active Spalte:
pagila=
Es hat geholfen, der nachfolgende Scan wurde zu einem "
index scan ". Dies bedeutet, dass Postgres den Index
idx_cust1 und dann den Heap der Tabelle weiter durchsucht, um die Werte der anderen Spalten (in diesem Fall der
email Spalte) zu lesen, die die Abfrage benötigt.
PostgreSQL 11 führte Indexe ein. Mit ihnen können Sie eine oder mehrere zusätzliche Spalten in den Index selbst aufnehmen - ihre Werte werden im Indexdatenspeicher gespeichert.
Wenn wir diese Funktion verwenden und einen E-Mail-Wert in den Index einfügen würden, müsste Postgres den
email Wert im Tabellenheap nicht nachschlagen. Mal sehen, ob das funktioniert:
pagila=
"
Index Only Scan " gibt an, dass die Abfrage nur noch einen Index benötigt, wodurch alle Festplatten-E / A zum Lesen des Tabellenheaps vermieden werden.
Abdeckungsindizes sind heute nur für B-Bäume verfügbar. In diesem Fall sind die Begleitbemühungen jedoch höher.
Teilindizes verwenden
Teilindizes indizieren nur eine Teilmenge der Zeilen in einer Tabelle. Dies spart die Größe von Indizes und schnellere Scans.
Angenommen, wir benötigen eine Liste mit E-Mail-Adressen von unseren Kunden in Kalifornien. Die Anfrage lautet wie folgt:
SELECT c.email FROM customer c JOIN address a ON c.address_id = a.address_id WHERE a.district = 'California'; which has a query plan that involves scanning both the tables that are joined: pagila=
Welche gewöhnlichen Indizes geben uns:
pagila=
Der
address wurde durch den
idx_address1 , und dann wurde der
idx_address1 gescannt.
Da dies eine häufige Abfrage ist und optimiert werden muss, können wir einen Teilindex verwenden, der nur die Zeilen mit Adressen indiziert, in denen sich die
'California' :
pagila=
Jetzt liest die Anfrage nur
idx_address2 und berührt die
idx_address2 nicht.
Verwenden mehrwertiger Indizes
Einige Spalten, die indiziert werden müssen, enthalten möglicherweise keinen skalaren Datentyp.
jsonb wie
jsonb ,
arrays und
tsvector enthalten mehrere oder mehrere Werte. Wenn Sie solche Spalten indizieren müssen, müssen Sie normalerweise nach allen einzelnen Werten in diesen Spalten suchen.
Versuchen wir, die Namen aller Filme zu finden, die Schnitte aus erfolglosen Einstellungen enthalten. Die
special_features enthält eine
special_features Namen
special_features . Wenn der Film diese „besondere Eigenschaft“ hat, enthält die Spalte ein Element in Form eines Textarrays
Behind The Scenes . Um nach all diesen Filmen zu suchen, müssen wir alle Zeilen mit "Behind The Scenes" für
alle Werte des Arrays
special_features :
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Der Containment-Operator
@> prüft, ob die rechte Seite eine Teilmenge der linken Seite ist.
Plan anfordern:
pagila=
Welches fordert einen vollständigen Heap-Scan mit einem Preis von 67.
Mal sehen, ob der reguläre B-Tree-Index uns hilft:
pagila=
Der Index wurde nicht einmal berücksichtigt. Dem B-Tree-Index ist das Vorhandensein einzelner Elemente in den indizierten Werten nicht bekannt.
Wir brauchen einen GIN-Index.
pagila=
Der GIN-Index unterstützt den Vergleich einzelner Werte mit indizierten zusammengesetzten Werten, wodurch sich die Kosten des Abfrageplans um mehr als die Hälfte reduzieren.
Entfernen Sie doppelte Indizes
Indizes häufen sich im Laufe der Zeit an, und manchmal enthält ein neuer Index dieselbe Definition wie einer der vorherigen. Um von Menschen lesbare SQL-Definitionen von Indizes zu erhalten, können Sie die Katalogansicht
pg_indexes . Sie können auch leicht die gleichen Definitionen finden:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn FROM pg_indexes GROUP BY defn HAVING count(*) > 1; And here's the result when run on the stock pagila database: pagila=
Superset-Indizes
Es kann vorkommen, dass Sie viele Indizes akkumulieren, von denen einer eine Teilmenge der Spalten indiziert, die andere Indizes indizieren. Dies kann entweder wünschenswert sein oder nicht - eine Obermenge kann nur nach Index scannen, was gut ist, aber zu viel Speicherplatz beanspruchen kann, oder die Abfrage, für die diese Obermenge optimiert werden sollte, wird nicht mehr verwendet.
Wenn Sie die Definition solcher Indizes automatisieren müssen, können Sie mit
pg_index aus der Tabelle
pg_catalog .
Nicht verwendete Indizes
Mit der Entwicklung von Anwendungen, die Datenbanken verwenden, entwickeln sich auch die von ihnen verwendeten Abfragen. Zuvor hinzugefügte Indizes können von keiner Abfrage mehr verwendet werden. Jedes Mal, wenn der Index gescannt wird, wird er vom Statistikmanager markiert. In der Systemkatalogansicht
idx_scan Wert
idx_scan , bei dem es sich um einen kumulativen Zähler handelt. Wenn Sie diesen Wert über einen bestimmten Zeitraum (z. B. einen Monat) verfolgen, erhalten Sie eine gute Vorstellung davon, welche Indizes nicht verwendet werden und gelöscht werden können.
Hier ist eine Anfrage, um die aktuellen Scan-Zählungen aller Indizes im
'public' Schema abzurufen:
SELECT relname, indexrelname, idx_scan FROM pg_catalog.pg_stat_user_indexes WHERE schemaname = 'public'; with output like this: pagila=
Erstellen Sie Indizes mit weniger Sperren neu
Oft müssen Indizes neu erstellt werden, wenn sie beispielsweise aufgeblasen sind, und die Neuerstellung kann den Scan beschleunigen. Außerdem können Indizes beschädigt sein. Das Ändern von Indexparametern muss möglicherweise auch neu erstellt werden.
Aktivieren Sie die parallele Indexerstellung
In PostgreSQL 11 ist das Erstellen eines B-Tree-Index wettbewerbsfähig. Um den Erstellungsprozess zu beschleunigen, können mehrere parallele Mitarbeiter verwendet werden. Stellen Sie jedoch sicher, dass diese Konfigurationsparameter richtig eingestellt sind:
SET max_parallel_workers = 32; SET max_parallel_maintenance_workers = 16;
Die Standardwerte sind zu klein. Idealerweise sollten diese Zahlen zusammen mit der Anzahl der Prozessorkerne erhöht werden. Lesen Sie die
Dokumentation für weitere Details.
Erstellung des Hintergrundindex
Sie können einen Index im Hintergrund mit dem Parameter
CONCURRENTLY des Befehls
CREATE INDEX erstellen:
pagila=
Diese Prozedur zur Indexerstellung unterscheidet sich von der üblichen darin, dass sie keine Tabellensperre erfordert und daher keine Schreibvorgänge blockiert. Andererseits dauert es länger und verbraucht mehr Ressourcen.
Postgres bietet viele flexible Optionen zum Erstellen von Indizes und Möglichkeiten zum Lösen bestimmter Fälle sowie Möglichkeiten zum Verwalten der Datenbank im Falle eines explosiven Wachstums Ihrer Anwendung. Wir hoffen, dass diese Tipps Ihnen helfen, Ihre Abfragen schnell und Ihre Datenbank skalierbar zu machen.