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, die 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

Alle Indices auflisten

Einer Relation…

 \d hico.documents

 \d documents
                                    Table "hico.documents"
    Column    |       Type        |                            Modifiers
 -------------+-------------------+-----------------------------------------------------------------
  document_id | integer           | not null default nextval('documents_document_id_seq'::regclass)
  author_id   | integer           | not null
  part        | integer           | not null
  content     | character varying |
 Indexes:
    "documents_pkey" PRIMARY KEY, btree (document_id)
Foreign-key constraints:
    "documents_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(author_id)
Referenced by:
    TABLE "words" CONSTRAINT "words_part_fkey" FOREIGN KEY (part) REFERENCES documents(document_id)

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;

Index erstellen

-- -----------------------------------------------------
-- Table hico.words
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS hico.words (
  words_id SERIAL PRIMARY KEY
 ,doc_id INT NOT NULL
 ,word VARCHAR(45) NOT NULL
);

CREATE INDEX word_idx ON hico.words (word);

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;

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;

Prüfen der ID für den Index

DROP TABLE IF EXISTS test;
     CREATE TABLE test
     (id INTEGER PRIMARY KEY
     ,category TEXT
     , value TEXT);
     CREATE INDEX ON test (category);

Welcher Index wird nicht genutzt?

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;