Nützliche Abfragen für die PostgreSQL-Indexpflege
PostgreSQL verfügt über zahlreiche Indexierungsfunktionen und es gibt viele Artikel, in denen die Syntax, Verwendung und der Wert des Index erläutert werden. In diesem Artikel werden wir grundlegende und nützliche Abfragen vorstellen, die den Status von Datenbankindexen anzeigen. Die Benutzer entwickeln Datenbanken und vergessen nach einiger Zeit, wenn Änderungen an der Architektur der Software vorgenommen werden müssen, die Bereinigung der vorherigen Indexe durchzuführen. Dies führt zu einem Durcheinander und verlangsamt manchmal die Datenbank aufgrund zu vieler Indexe. Jedes Mal, wenn wir eine Aktualisierung oder Einfügung durchführen, wird der Index zusammen mit der tatsächlichen Tabelle aktualisiert, sodass eine Bereinigung erforderlich ist.
Bevor wir die Abfragen schreiben, möchten wir eine Katalogtabelle pg_index einführen. Die Tabelle enthält Informationen zum Index. Dies ist die grundlegende Katalogtabelle. Alle indexbasierten Ansichten verwenden dieselbe Tabelle.
- Anzahl an Indexen anzeigen lassen
Um herauszufinden, wie viele Indexe Ihre Tabelle hat, kann Ihnen die folgende Abfrage weiterhelfen. Sie zeigt den schemaqualifizierten Tabellennamen und seine Indexnamen an.
SELECT CONCAT(n.nspname,'.', c.relname) AS table,
i.relname AS index_name FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pg_opfamily';
Hierbei fügen wir den Befehl “AND c.relname like 'pg_opfamily' “ hinzu, damit unsere pg_index-Tabelle zur Veranschaulichung nicht zu groß wird. Lassen Sie den Zusatz weg, können Sie sich die gesamten Tabellen- und Indexnamen ausgeben lassen.
- Eindeutigkeit des Indexes
Wie wir alle wissen, ist ein Index ein Leistungsmerkmal, wird aber auch verwendet, um die Eindeutigkeit sicherzustellen. Um die Eindeutigkeit zu gewährleisten, benötigen wir jedoch einen separaten Indextyp, der als eindeutiger Index bezeichnet wird. Um zu überprüfen, ob ein Index eindeutig ist oder nicht, verfügt pg_index über eine Spalte mit dem Namen indisunique, um die Eindeutigkeit des Index zu ermitteln. Mit folgender Abfrage wird Ihnen die Eindeutigkeit des Indexes ausgegeben:
SELECT i.relname AS index_name,
indisunique is_unique
FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r', 't'])
AND c.relname LIKE 'pg_opfamily';
- Index-Größe
Mit folgender Abfrage können Sie die Größe des PostgreSQL-Index ermitteln:
SELECT pg_size_pretty(pg_relation_size('pg_opfamily_oid_index'));
- Gesamttabellengröße ermitteln
Mithilfe des nächsten Befehls können Sie sich eine Liste der Indexe mit der Gesamttabellengröße und der Indexgröße anzeigen lassen. Dies ist sehr nützlich, um Ihre Tabellengröße mit den entsprechenden Indexen zu vergleichen.
SELECT CONCAT(n.nspname,'.', c.relname) AS table,
i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r', 't'])
AND n.oid NOT IN (99, 11, 12375);
- Abfrage zur Indexerstellung
Lassen Sie sich mit folgendem Befehl die Abfrage zur Erstellung des Indexes ausgeben:
SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index WHERE indrelid = 'pg_opfamily'::regclass;
- ‘REINDEX‘ um Ihren Index erneut zu erstellen
Falls Ihr Index beschädigt oder überfüllt wird, müssen Sie diesen Index erneut erstellen. Gleichzeitig möchten Sie die Ausführung von Aktionen in Ihrer Tabelle nicht blockieren, daher ist der Befehl REINDEX CONCURRENTLY die richtige Wahl:
REINDEX INDEX CONCURRENTLY pg_opfamily_oid_index;
- Unterstützte Datentypen abrufen
PostgreSQL verfügt über viele Indexmethoden wie BTree, Hash, BRIN, GIST und GIN. Manchmal möchten wir einen bestimmten Index für eine Spalte erstellen, können dies jedoch nicht. PostgreSQL hat Einschränkungen, dass einige Indexe für einige Datentypen und Operatoren nicht erstellt werden können und das ist auch sinnvoll. Beispielsweise kann der Hash-Index nur für gleiche Operatoren verwendet werden. Hier ist eine Abfrage, um die Liste der unterstützten Datentypen für einen bestimmten Index abzurufen:
SELECT amname,
opfname
FROM pg_opfamily,
pg_am
WHERE opfmethod = pg_am.oid
AND amname = 'btree';
- Nicht verwendete Indexe
Die folgende Abfrage findet die nicht verwendeten Indexe. Wenn index_scans 0 oder nahe 0 ist, können Sie diese Indexe löschen. Aber seien Sie vorsichtig, da diese Indexe möglicherweise nur für bestimmte Zwecke bestimmt sind.
SELECT s.relname AS table_name,
indexrelname AS index_name,
i.indisunique,
idx_scan AS index_scans
FROM pg_catalog.pg_stat_user_indexes s,
pg_index i
WHERE i.indexrelid = s.indexrelid;
- Doppelte Indexe
Benutzen Sie folgende Abfrage zum Suchen eines doppelten Index. Es ist nicht erforderlich, mehrere gleiche Indexe mit einem anderen Namen in einer Tabelle zu haben. Wie bereits erwähnt, werden beim Aktualisieren/Einfügen alle Indexe zusammen mit der tatsächlichen Tabelle aktualisiert, was die Leistung beeinträchtigt.
SELECT indrelid::regclass table_name,
att.attname column_name,
amname index_method
FROM pg_index i,
pg_class c,
pg_opclass o,
pg_am a,
pg_attribute att
WHERE o.oid = ALL (indclass)
AND att.attnum = ANY(i.indkey)
AND a.oid = o.opcmethod
AND att.attrelid = c.oid
AND c.oid = i.indrelid
GROUP BY table_name,
att.attname,
indclass,
amname, indkey
HAVING count(*) > 1;
PostgreSQL verfügt über Katalogtabellen zum Speichern der Indexinformationen. Daher können wir so viele Abfragen schreiben, wie wir benötigen. Sie haben nun einige grundlegende Abfragen kennengelernt, die die vorhanden Katalogtabellen zur Ausgabe von Informationen zu Ihren Indexen nutzen.
Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.
Kontaktieren Sie uns gerne über das
Kontaktformular und vereinbaren ein unverbindliches
Beratungsgespräch mit unseren Berater:innen zur
Bedarfsevaluierung. Gemeinsam optimieren wir Ihre
Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!
55118 Mainz
info@madafa.de
+49 6131 3331612
Freitags: