Blog
Thursday, 12. November 2020

Nützliche Abfragen für die PostgreSQL-Indexpflege

Denise
IT-Consultant

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.

  1. 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';
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege

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.

  1. 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';
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. 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'));
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. 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);
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. 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;
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. ‘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;
  1. 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';
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. 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;
Mainzer Datenfabrik - Nützliche Abfragen für die PostgreSQL-Indexpflege
  1. 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.

Interesse geweckt?

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!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ