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.
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.
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';
SELECT pg_size_pretty(pg_relation_size('pg_opfamily_oid_index'));
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);
SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index WHERE indrelid = 'pg_opfamily'::regclass;
REINDEX INDEX CONCURRENTLY pg_opfamily_oid_index;
SELECT amname,
opfname
FROM pg_opfamily,
pg_am
WHERE opfmethod = pg_am.oid
AND amname = 'btree';
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;
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.
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!