Index-Varianten#

../../_images/kunst-am-bau-streetart-florenz.jpg

« Ü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.

Quelle:

https://de.wikipedia.org/wiki/Datenbankindex

  • 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

  1. Neuen Index anlegen

  2. alten Index löschen

  3. 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;