Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 5: Indizes und EXPLAIN

Ziel: Du liest Ausführungspläne, erkennst Seq Scan, Index Scan und Bitmap Scan, baust den passenden Index (B-Tree, mehrspaltig, partiell, Ausdruck, GIN) ohne Schreibsperre und findest Indizes, die nur Platz kosten. · Dauer: ca. 90 min · Voraussetzung: Tag 1

Briefing

  • Der Planer schätzt für jeden möglichen Weg die Kosten und nimmt den billigsten. Die Schätzung beruht auf Statistiken (ANALYZE).
  • EXPLAIN zeigt den Plan, EXPLAIN ANALYZE führt die Abfrage aus und stellt Schätzung und Wirklichkeit nebeneinander.
  • Ein Index beschleunigt Lesen und verlangsamt jedes Schreiben. Er lohnt sich, wenn die Abfrage einen kleinen Teil der Tabelle braucht.
  • Alles auf pg1 in der Datenbank tag05. Die Zeiten hängen von deiner VM ab.

Drill 1: Testtabelle, EXPLAIN und EXPLAIN ANALYZE

Zwei Millionen Bestellungen mit generate_series; das dauert etwa eine Minute:

createdb tag05
psql -d tag05 <<'EOF'
CREATE TABLE bestellung (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kunde_id   int NOT NULL,
  status     text NOT NULL,
  betrag     numeric(10,2) NOT NULL,
  email      text NOT NULL,
  angelegt   timestamptz NOT NULL,
  daten      jsonb NOT NULL,
  notiz      text
);
INSERT INTO bestellung (kunde_id, status, betrag, email, angelegt, daten, notiz)
SELECT (random() * 49999)::int + 1,
       CASE WHEN i % 100 = 0 THEN 'offen' WHEN i % 10 = 0 THEN 'storniert'
            ELSE 'geliefert' END,
       round((random() * 500)::numeric, 2),
       'Kunde' || (i % 50000) || '@Example.org',
       now() - i * interval '10 seconds',
       jsonb_build_object('kanal', (ARRAY['web','app','telefon'])[i % 3 + 1],
                          'gutschein', i % 7 = 0),
       (ARRAY['schnelle Lieferung bitte', 'Geschenkverpackung',
              'beim Nachbarn abgeben', 'Rechnung per Post'])[i % 4 + 1] || ' ' || i
FROM generate_series(1, 2000000) AS i;
VACUUM ANALYZE bestellung;
SELECT pg_size_pretty(pg_table_size('bestellung')) AS tabelle,
       pg_size_pretty(pg_indexes_size('bestellung')) AS indizes;
EOF

Erwartet (gekürzt):

INSERT 0 2000000
VACUUM
 tabelle | indizes
---------+---------
 313 MB  | 43 MB

Die 43 MB Index sind der Primärschlüssel. Jetzt eine Abfrage ohne passenden Index:

psql -d tag05 <<'EOF'
EXPLAIN SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EOF

Erwartet (lange Zeilen umbrochen):

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Gather  (cost=1000.00..51420.77 rows=41 width=124)
   Workers Planned: 2
   ->  Parallel Seq Scan on bestellung  (cost=0.00..50416.67 rows=17 width=124)
         Filter: (kunde_id = 4711)

 Gather  (cost=1000.00..51420.77 rows=41 width=124) (actual rows=39.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared read=40000
   ->  Parallel Seq Scan on bestellung  (cost=0.00..50416.67 rows=17 width=124)
         (actual rows=13.00 loops=3)
         Filter: (kunde_id = 4711)
         Rows Removed by Filter: 666654
         Buffers: shared read=40000
 Planning Time: 0.097 ms
 Execution Time: 319.649 ms

So liest du den Plan: von innen nach außen. cost=Start..Gesamt in abstrakten Einheiten, rows ist die Schätzung (41), actual rows die Wirklichkeit (39) – pro Durchlauf, bei loops=3 (zwei Worker plus Leader) also 3 × 13. Seit PostgreSQL 18 zeigt EXPLAIN ANALYZE die Puffer automatisch: 40 000 Seiten à 8 kB gelesen, also die ganze Tabelle, um 39 Zeilen zu finden. TIMING OFF spart die Zeitmessung pro Knoten und macht die Ausgabe schmaler.

Achtung: EXPLAIN ANALYZE führt die Abfrage wirklich aus – auch DELETE und UPDATE. Solche in BEGIN; … ROLLBACK; einpacken.

Fortschritt speichern? Kostenlos registrieren

Drill 2: B-Tree – Index Scan, Bitmap Scan, Index Only Scan

psql -d tag05 <<'EOF'
\timing on
CREATE INDEX ix_kunde ON bestellung (kunde_id);
\timing off
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM bestellung WHERE id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*) FROM bestellung WHERE kunde_id BETWEEN 1000 AND 1100;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id > 100;
EOF

Erwartet (gekürzt):

CREATE INDEX
Time: 2171.529 ms (00:02.172)
 Bitmap Heap Scan on bestellung (actual rows=39.00 loops=1)
   Recheck Cond: (kunde_id = 4711)
   Heap Blocks: exact=39
   Buffers: shared read=42
   ->  Bitmap Index Scan on ix_kunde (actual rows=39.00 loops=1)
         Index Cond: (kunde_id = 4711)
         Index Searches: 1
         Buffers: shared read=3
 Execution Time: 2.818 ms

 Index Scan using bestellung_pkey on bestellung (actual rows=1.00 loops=1)
   Index Cond: (id = 4711)
   Index Searches: 1
   Buffers: shared hit=3 read=4
 Execution Time: 0.222 ms

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=204 read=4
   ->  Index Only Scan using ix_kunde on bestellung (actual rows=4099.00 loops=1)
         Index Cond: ((kunde_id >= 1000) AND (kunde_id <= 1100))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=204 read=4

 Seq Scan on bestellung
   Filter: (kunde_id > 100)

Von 320 ms auf 3 ms, 42 statt 40 000 Seiten. Die vier Zugriffsarten:

Plan Wann Merkmal
Index Scan sehr wenige Zeilen Index, dann Tabelle, Zeile für Zeile
Bitmap Heap Scan einige bis viele Zeilen erst Seiten-Bitmap, dann Tabelle
Index Only Scan alle Spalten im Index Tabelle nur bei Heap Fetches
Seq Scan großer Anteil der Tabelle ganze Tabelle sequenziell

Index Only Scan funktioniert nur, wenn die Visibility Map die Seiten als „alle sichtbar“ kennt – das pflegt VACUUM (Tag 6). kunde_id > 100 trifft fast alle Zeilen, da ist der Seq Scan richtig. Index Searches ist neu in PostgreSQL 18.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Mehrspaltige Indizes und Skip Scan

psql -d tag05 <<'EOF'
CREATE INDEX ix_status_zeit ON bestellung (status, angelegt);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT id FROM bestellung
  WHERE status = 'offen' AND angelegt > now() - interval '1 day';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*) FROM bestellung WHERE angelegt > now() - interval '1 hour';
EOF

Erwartet (gekürzt):

 Index Scan using ix_status_zeit on bestellung (actual rows=86.00 loops=1)
   Index Cond: ((status = 'offen'::text) AND (angelegt > (now() - '1 day'::interval)))
   Index Searches: 1
 Execution Time: 3.133 ms

 Aggregate (actual rows=1.00 loops=1)
   ->  Index Only Scan using ix_status_zeit on bestellung (actual rows=348.00 loops=1)
         Index Cond: (angelegt > (now() - '01:00:00'::interval))
         Heap Fetches: 0
         Index Searches: 4
 Execution Time: 2.106 ms

Ein mehrspaltiger B-Tree ist nach der ersten Spalte sortiert, innerhalb gleicher Werte nach der zweiten – wie ein Telefonbuch nach Nachname, dann Vorname. Regel: Gleichheitsbedingungen vorne, Bereiche hinten. Die zweite Abfrage filtert nur auf die zweite Spalte. Bis PostgreSQL 17 hieß das meist Seq Scan; seit 18 kann der Planer per Skip Scan für jeden der drei status-Werte gezielt springen (Index Searches: 4). Das lohnt nur, wenn die erste Spalte wenige verschiedene Werte hat.

Fortschritt speichern? Kostenlos registrieren

Drill 4: Partieller Index und Ausdrucksindex

Offene Bestellungen sind 1 % der Tabelle, werden aber ständig abgefragt:

psql -d tag05 <<'EOF'
CREATE INDEX ix_offen ON bestellung (angelegt) WHERE status = 'offen';
SELECT indexrelid::regclass AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS groesse
FROM pg_index WHERE indrelid = 'bestellung'::regclass ORDER BY 1;
EXPLAIN (COSTS OFF)
  SELECT id FROM bestellung WHERE status = 'offen' ORDER BY angelegt DESC LIMIT 10;
EOF

Erwartet:

      index      | groesse
-----------------+---------
 bestellung_pkey | 43 MB
 ix_kunde        | 14 MB
 ix_status_zeit  | 77 MB
 ix_offen        | 456 kB
(4 rows)

                       QUERY PLAN
--------------------------------------------------------
 Limit
   ->  Index Scan Backward using ix_offen on bestellung
(2 rows)

456 kB statt 77 MB: Der partielle Index enthält nur Zeilen, die die WHERE-Bedingung erfüllen. Der Planer nimmt ihn nur, wenn die Abfrage dieselbe Bedingung enthält. Den Index rückwärts lesen erspart das Sortieren.

Jetzt eine Suche, die Groß-/Kleinschreibung ignoriert:

psql -d tag05 <<'EOF'
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT id FROM bestellung WHERE lower(email) = '[email protected]';
CREATE INDEX ix_email_lower ON bestellung (lower(email));
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT id FROM bestellung WHERE lower(email) = '[email protected]';
EXPLAIN (COSTS OFF) SELECT id FROM bestellung WHERE email = '[email protected]';
EOF

Erwartet (gekürzt):

 Gather (actual rows=40.00 loops=1)
   ->  Parallel Seq Scan on bestellung (actual rows=13.33 loops=3)
         Filter: (lower(email) = '[email protected]'::text)
         Rows Removed by Filter: 666653
 Execution Time: 814.361 ms
CREATE INDEX
 Bitmap Heap Scan on bestellung (actual rows=40.00 loops=1)
   Recheck Cond: (lower(email) = '[email protected]'::text)
   ->  Bitmap Index Scan on ix_email_lower (actual rows=40.00 loops=1)
         Index Cond: (lower(email) = '[email protected]'::text)
 Execution Time: 1.273 ms
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on bestellung
         Filter: (email = '[email protected]'::text)

Ein Ausdrucksindex speichert das Ergebnis von lower(email) und hilft nur Abfragen, die genau diesen Ausdruck verwenden – die Suche auf email selbst geht leer aus.

Fortschritt speichern? Kostenlos registrieren

Drill 5: GIN für jsonb und Volltext

B-Trees können „enthält“ nicht. Dafür gibt es GIN (Generalized Inverted Index): ein Eintrag pro Schlüssel bzw. Wort, mit der Liste aller Zeilen. Erst ein seltenes Merkmal ins JSON:

psql -d tag05 <<'EOF'
UPDATE bestellung SET daten = daten || '{"reklamation": true}' WHERE id % 5000 = 0;
VACUUM ANALYZE bestellung;
CREATE INDEX ix_daten ON bestellung USING gin (daten jsonb_path_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*) FROM bestellung WHERE daten @> '{"reklamation": true}';
EXPLAIN (COSTS OFF)
  SELECT count(*) FROM bestellung WHERE daten->>'kanal' = 'telefon';
EOF

Erwartet (gekürzt):

UPDATE 400
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on bestellung (actual rows=400.00 loops=1)
         Recheck Cond: (daten @> '{"reklamation": true}'::jsonb)
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on ix_daten (actual rows=400.00 loops=1)
               Index Cond: (daten @> '{"reklamation": true}'::jsonb)
 Execution Time: 1.002 ms
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on bestellung
                     Filter: ((daten ->> 'kanal'::text) = 'telefon'::text)

Der GIN-Index hilft dem Operator @> (enthält), nicht ->> mit =. Für Letzteres wäre ein Ausdrucksindex auf (daten->>'kanal') der richtige. jsonb_path_ops ist kleiner als die Standardklasse, kann aber nur @> und JSON-Pfade.

Volltextsuche zerlegt Text in normalisierte Wörter (Lexeme):

psql -d tag05 <<'EOF'
SELECT to_tsvector('german', 'beim Nachbarn abgeben 4710');
\timing on
CREATE INDEX ix_notiz_fts ON bestellung USING gin (to_tsvector('german', notiz));
\timing off
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*) FROM bestellung
  WHERE to_tsvector('german', notiz) @@ to_tsquery('german', 'Nachbarn & 4710');
SELECT pg_size_pretty(pg_relation_size('ix_daten')) AS daten,
       pg_size_pretty(pg_relation_size('ix_notiz_fts')) AS fts;
EOF

Erwartet (gekürzt, lange Zeilen umbrochen):

               to_tsvector
------------------------------------------
 '4710':4 'abgeb':3 'beim':1 'nachbarn':2
CREATE INDEX
Time: 26015.517 ms (00:26.016)
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on bestellung (actual rows=1.00 loops=1)
         Recheck Cond: (to_tsvector('german'::regconfig, notiz) @@
           '''nachbarn'' & ''4710'''::tsquery)
         ->  Bitmap Index Scan on ix_notiz_fts (actual rows=1.00 loops=1)
               Index Cond: (to_tsvector('german'::regconfig, notiz) @@
                 '''nachbarn'' & ''4710'''::tsquery)
 Execution Time: 0.575 ms
  daten  |  fts
---------+--------
 4432 kB | 112 MB

Die Abfrage muss to_tsvector mit derselben Sprachkonfiguration aufrufen wie der Index. GIN-Indizes sind groß und teuer im Aufbau (hier 26 s), Suchen sind dafür schnell.

Fortschritt speichern? Kostenlos registrieren

Drill 6: CREATE INDEX CONCURRENTLY

Ein normales CREATE INDEX sperrt alle Schreibzugriffe für die ganze Bauzeit:

psql -d tag05 -c 'CREATE INDEX ix_betrag ON bestellung (betrag)' &
sleep 1
psql -d tag05 -c "SELECT DISTINCT l.mode FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
  WHERE l.relation = 'bestellung'::regclass AND a.query LIKE 'CREATE%'"
wait
psql -d tag05 -c 'DROP INDEX ix_betrag'
psql -d tag05 -c 'CREATE INDEX CONCURRENTLY ix_betrag ON bestellung (betrag)' &
sleep 1
psql -d tag05 -c "SELECT DISTINCT l.mode FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
  WHERE l.relation = 'bestellung'::regclass AND a.query LIKE 'CREATE%'" \
  -c 'SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index'
wait

Erwartet (gekürzt):

   mode
-----------
 ShareLock
CREATE INDEX
DROP INDEX
           mode
--------------------------
 ShareUpdateExclusiveLock
             phase              | blocks_done | blocks_total
--------------------------------+-------------+--------------
 building index: scanning table |       33676 |        40009
CREATE INDEX

ShareLock lässt Lesen zu, aber kein INSERT/UPDATE/DELETE. ShareUpdateExclusiveLock blockiert nur andere DDL. Dafür liest CONCURRENTLY die Tabelle zweimal und wartet auf alle laufenden Transaktionen – es darf nicht in einem BEGIN-Block laufen. Geht es schief, bleibt ein Wrack:

psql -d tag05 <<'EOF'
CREATE UNIQUE INDEX CONCURRENTLY ix_email_uniq ON bestellung (email);
\d bestellung
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY ix_email_uniq;
EOF

Erwartet (gekürzt):

ERROR:  could not create unique index "ix_email_uniq"
DETAIL:  Key (email)=([email protected]) is duplicated.
...
    "ix_email_uniq" UNIQUE, btree (email) INVALID
...
  indexrelid   | indisvalid
---------------+------------
 ix_email_uniq | f
DROP INDEX

Ein INVALID-Index wird nie zum Lesen benutzt. Ist er schon bereit (indisready = t, Abbruch erst in der Prüfphase), pflegt ihn trotzdem jedes Schreiben – reine Last. Entweder löschen oder mit REINDEX INDEX CONCURRENTLY neu bauen, wenn die Ursache behoben ist.

Fortschritt speichern? Kostenlos registrieren

Drill 7: Ungenutzte Indizes finden

psql -d tag05 <<'EOF'
SELECT indexrelname AS index, idx_scan, last_idx_scan IS NOT NULL AS benutzt,
       pg_size_pretty(pg_relation_size(indexrelid)) AS groesse
FROM pg_stat_user_indexes WHERE relname = 'bestellung'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;
SELECT pg_size_pretty(pg_indexes_size('bestellung')) AS alle_indizes,
       pg_size_pretty(pg_table_size('bestellung')) AS tabelle;
EOF

Erwartet (Zahlen abhängig von deinen Abfragen):

      index      | idx_scan | benutzt | groesse
-----------------+----------+---------+---------
 ix_betrag       |        0 | f       | 43 MB
 ix_offen        |        0 | f       | 456 kB
 ix_email_lower  |        1 | t       | 15 MB
 ix_daten        |        1 | t       | 4432 kB
 ix_notiz_fts    |        3 | t       | 112 MB
 bestellung_pkey |        3 | t       | 43 MB
 ix_kunde        |        3 | t       | 14 MB
 ix_status_zeit  |        5 | t       | 77 MB
(8 rows)

 alle_indizes | tabelle
--------------+---------
 309 MB       | 313 MB

Die Indizes sind inzwischen fast so groß wie die Tabelle. ix_offen zeigt 0, weil ein EXPLAIN ohne ANALYZE nichts ausführt. last_idx_scan (seit 16) sagt, wann ein Index zuletzt gebraucht wurde. Vor dem Löschen prüfen: Zähler seit wann (pg_stat_reset setzt sie zurück), läuft die Abfrage nur monatlich, stützt der Index einen UNIQUE- oder Primärschlüssel – und auf Standbys wird separat gezählt.

Fortschritt speichern? Kostenlos registrieren

Drill 8: Statistiken und Planer-Fallen

Was der Planer über die Daten weiß:

psql -d tag05 -c "SELECT attname, n_distinct, most_common_vals, most_common_freqs
  FROM pg_stats WHERE tablename = 'bestellung' AND attname IN ('status', 'kunde_id')"

Erwartet:

 attname  | n_distinct |      most_common_vals       |       most_common_freqs
----------+------------+-----------------------------+--------------------------------
 kunde_id |      48917 | {23737,9911,23095}          | {0.00023333334,0.0002,0.0002}
 status   |          3 | {geliefert,storniert,offen} | {0.90043336,0.08976667,0.0098}
(2 rows)

ANALYZE liest eine Stichprobe von 30 000 Zeilen (300 × default_statistics_target) und speichert häufigste Werte, Histogramme und die Zahl verschiedener Werte. Veralten die Statistiken, schätzt der Planer falsch:

psql -d tag05 <<'EOF'
CREATE TABLE kopie AS SELECT * FROM bestellung WHERE id <= 200000;
ANALYZE kopie;
UPDATE kopie SET status = 'offen' WHERE status = 'storniert';
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  SELECT id FROM kopie WHERE status = 'offen';
ANALYZE kopie;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
  SELECT id FROM kopie WHERE status = 'offen';
EOF

Erwartet (lange Zeilen umbrochen):

UPDATE 18000
 Seq Scan on kopie  (cost=0.00..7099.01 rows=2137 width=8)
   (actual rows=20000.00 loops=1)
   Filter: (status = 'offen'::text)
   Rows Removed by Filter: 180000
ANALYZE
 Seq Scan on kopie  (cost=0.00..6882.00 rows=20207 width=8)
   (actual rows=20000.00 loops=1)

Geschätzt 2137, tatsächlich 20 000 – Faktor 10 daneben. Bei Joins führt so etwas zu falschen Join-Methoden und Minuten statt Millisekunden. Autovacuum analysiert erst nach 10 % geänderten Zeilen (Tag 6); nach großen Ladevorgängen selbst ANALYZE aufrufen. Zwei Klassiker, bei denen ein vorhandener Index nicht greift:

psql -d tag05 <<'EOF'
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = 4711.0;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = '4711';
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id + 0 = 4711;
EOF

Erwartet (gekürzt):

   ->  Parallel Seq Scan on bestellung
         Filter: ((kunde_id)::numeric = 4711.0)
 Bitmap Heap Scan on bestellung
   Recheck Cond: (kunde_id = 4711)
   ->  Bitmap Index Scan on ix_kunde
   ->  Parallel Seq Scan on bestellung
         Filter: ((kunde_id + 0) = 4711)

4711.0 ist numeric: PostgreSQL wandelt die Spalte um, der Index auf int passt nicht mehr. Das String-Literal '4711' hat noch keinen Typ und wird zu int – kein Problem. Jede Rechnung oder Funktion auf der Spalte (kunde_id + 0, date(angelegt)) schaltet den Index ab, wenn es keinen passenden Ausdrucksindex gibt. Typische Quelle: ORMs, die Parameter als falschen Typ binden.

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige alle Indizes mit ix_ im Namen samt Größe und Typ. Zeitziel: 10 s
  2. Zeige den Plan für SELECT * FROM bestellung WHERE id = 1, ohne ihn auszuführen. Zeitziel: 15 s
  3. Aktualisiere die Statistiken von bestellung und zeige, wie viele Seiten ANALYZE liest. Zeitziel: 20 s
  4. Liste alle ungültigen Indizes der Datenbank. Zeitziel: 30 s

Lösung

psql -d tag05 -c '\di+ ix_*'
psql -d tag05 -c 'EXPLAIN SELECT * FROM bestellung WHERE id = 1'
psql -d tag05 -c 'ANALYZE VERBOSE bestellung' 2>&1 | grep scanned
psql -d tag05 -Atc 'SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid'

Erwartet (gekürzt):

 Schema |      Name      | Type  | Owner  |   Table    | Persistence | ...
--------+----------------+-------+--------+------------+-------------+ ...
 public | ix_betrag      | index | ubuntu | bestellung | permanent   | ...
...
 Index Scan using bestellung_pkey on bestellung  (cost=0.43..8.45 rows=1 width=124)
   Index Cond: (id = 1)
INFO:  "bestellung": scanned 30000 of 40009 pages, containing 1499657 live rows and
292 dead rows; 30000 rows in sample, ...

Die letzte Abfrage liefert nichts – gut so.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

psql -d tag05 -Atc 'SELECT count(*) FROM bestellung'

Erwartet: 2000000

psql -d tag05 -c "EXPLAIN (COSTS OFF) SELECT id FROM bestellung
  WHERE status = 'offen' ORDER BY angelegt DESC LIMIT 10"

Erwartet: Index Scan Backward using ix_offen on bestellung

psql -d tag05 -c "EXPLAIN (COSTS OFF) SELECT count(*) FROM bestellung
  WHERE daten @> '{\"reklamation\": true}'"

Erwartet: Bitmap Index Scan on ix_daten

psql -d tag05 -Atc "SELECT count(*) FROM pg_index WHERE NOT indisvalid"

Erwartet: 0

Challenge

Die Kundenseite zeigt die fünf neuesten Bestellungen eines Kunden: SELECT id, angelegt FROM bestellung WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5. Bring den Plan ohne Sort aus, ohne Schreibzugriffe zu sperren, und entferne danach den Index, der überflüssig geworden ist.

Hinweis: Ein Index kann Filter und Sortierung zugleich liefern, wenn die Spalten in der richtigen Reihenfolge stehen.

Lösung

psql -d tag05 <<'EOF'
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT id, angelegt FROM bestellung
  WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5;
CREATE INDEX CONCURRENTLY ix_kunde_zeit ON bestellung (kunde_id, angelegt);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT id, angelegt FROM bestellung
  WHERE kunde_id = 4711 ORDER BY angelegt DESC LIMIT 5;
DROP INDEX CONCURRENTLY ix_kunde;
EXPLAIN (COSTS OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EOF

Erwartet (gekürzt):

 Limit (actual rows=5.00 loops=1)
   ->  Sort (actual rows=5.00 loops=1)
         Sort Key: angelegt DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on bestellung (actual rows=39.00 loops=1)
 Execution Time: 2.297 ms
CREATE INDEX
 Limit (actual rows=5.00 loops=1)
   Buffers: shared hit=8
   ->  Index Scan Backward using ix_kunde_zeit on bestellung (actual rows=5.00 loops=1)
         Index Cond: (kunde_id = 4711)
 Execution Time: 0.290 ms
DROP INDEX
 Bitmap Heap Scan on bestellung
   Recheck Cond: (kunde_id = 4711)
   ->  Bitmap Index Scan on ix_kunde_zeit

(kunde_id, angelegt) deckt alle Abfragen ab, die ix_kunde bedient hat – ein Index, der mit den Spalten eines anderen beginnt, macht diesen überflüssig.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

dropdb tag05

Die 2 Millionen Zeilen sind schnell neu erzeugt; Tag 6 baut sich eigene Daten.

Interview-Notiz

  • „Warum nimmt PostgreSQL meinen Index nicht?“ – Abfrage trifft zu viele Zeilen, Statistiken veraltet, Typ passt nicht oder eine Funktion umhüllt die Spalte; mit EXPLAIN (ANALYZE) Schätzung gegen Wirklichkeit prüfen.
  • „Wie legst du einen Index in Produktion an?“ – CREATE INDEX CONCURRENTLY, danach auf INVALID prüfen; in Migrationen ohne Transaktionsblock.
  • „B-Tree oder GIN?“ – B-Tree für Gleichheit, Bereiche, Sortierung; GIN für „enthält“: jsonb, Arrays, Volltext.

Enterprise-Notiz

  • Langsame Abfragen findest du nicht per Zufall: pg_stat_statements und auto_explain (Tag 7) zeigen, welche Pläne wirklich Zeit kosten.
  • Index-Reviews regelmäßig: ungenutzte, doppelte und INVALID Indizes kosten Schreibleistung, WAL-Volumen und Backup-Platz.
  • Große Indizes in Wartungsfenstern oder mit CONCURRENTLY und maintenance_work_mem; Fortschritt in pg_stat_progress_create_index.
  • Pläne können nach einem Major-Upgrade oder neuen Statistiken kippen – nach pg_upgrade fehlende Statistiken nachziehen – ab 18 vacuumdb --all --analyze-in-stages --missing-stats-only (Tag 13).
  • Für korrelierte Spalten helfen erweiterte Statistiken (CREATE STATISTICS), bevor man an Planer-Parametern dreht.