Index-Varianten#
« Übersicht: Manage PostgreSQL
Ein Datenbankindex, oder kurz Index (im Plural „Indexe“ oder „Indizes“), ist eine von der Datenstruktur getrennte Indexstruktur in einer Datenbank, welche die Suche und das Sortieren nach bestimmten Feldern beschleunigt.
Ein Index besteht aus einer Ansammlung von Zeigern (Verweisen), die eine Ordnungsrelation auf eine oder mehrere Spalten in einer Tabelle definieren. Wird bei einer Abfrage eine indizierte Spalte als Suchkriterium herangezogen, sucht das Datenbankmanagementsystem (DBMS) die gewünschten Datensätze anhand dieser Zeiger. In der Regel finden hier B+-Bäume Anwendung. Ohne Index müsste die Spalte sequenziell durchsucht werden, während eine Suche mit Hilfe des Baums nur logarithmische Komplexität hat.
CREATE INDEX CONCURRENTLY kann nicht Teil einer Transaktion sein.
Besitzt die Relation einen Index?#
\d worte
Table "public.worte"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
wort | character varying(100) | | |
Einen Index anlegen#
CREATE INDEX wort_idx ON worte(wort);
\d worte
Table "public.worte"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
wort | character varying(100) | | |
Indexes:
"wort_idx" btree (wort)
Alle Indices…#
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and t.relname like '%%'
order by
t.relname,
i.relname;
Wie groß sind die Idices?#
SELECT
nspname,relname,
round(100 * pg_relation_size(indexrelid) /
pg_relation_size(indrelid)) / 100
AS index_ratio,
pg_size_pretty(pg_relation_size(indexrelid))
AS index_size,
pg_size_pretty(pg_relation_size(indrelid))
AS table_size
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND
C.relkind='i' AND
pg_relation_size(indrelid) > 0;
Konfiguration für die Messung#
\set pager off
\timing
select * from worte where wort like '%aa%';
Welcher Index wird nicht genutzt?#
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;
Nächster Versuch mit anderer Abfrage#
select * from worte where wort = 'Senaard';
Wurde der Index genutzt?#
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;
Dauer der Abfrage mit und ohne Index?#
select * from worte where wort = 'Senaard';
wort
---------
Senaard
(1 row)
Time: 0,547 ms
drop index wort_idx;
select * from worte where wort = 'Senaard';
wort
---------
Senaard
(1 row)
Time: 76,921 ms
Index erneuern#
Ohne das System zu blockieren
Neuen Index anlegen
alten Index löschen
Umbenennen des neuen Index zum alten Index-Namen
Test-Tabelle anlegen#
DROP TABLE IF EXISTS test;
CREATE TABLE test
(id INTEGER PRIMARY KEY
, category TEXT
, value TEXT);
CREATE INDEX ON test (category);
Welche ID hat der Index?#
SELECT oid, relname, relfilenode
FROM pg_class
WHERE oid in (SELECT indexrelid
FROM pg_index
WHERE indrelid = 'test'::regclass);
Index erneuern…#
CREATE INDEX CONCURRENTLY new_index
ON test (category);
BEGIN;
DROP INDEX test_category_idx;
ALTER INDEX new_index RENAME TO test_category_idx;
COMMIT;