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). EXPLAINzeigt den Plan,EXPLAIN ANALYZEfü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
pg1in der Datenbanktag05. 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;
EOFErwartet (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;
EOFErwartet (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 ANALYZEführt die Abfrage wirklich aus – auchDELETEundUPDATE. Solche inBEGIN; … 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;
EOFErwartet (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';
EOFErwartet (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;
EOFErwartet:
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]';
EOFErwartet (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';
EOFErwartet (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;
EOFErwartet (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'
waitErwartet (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;
EOFErwartet (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;
EOFErwartet (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';
EOFErwartet (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;
EOFErwartet (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
- Zeige alle Indizes mit
ix_im Namen samt Größe und Typ. Zeitziel: 10 s - Zeige den Plan für
SELECT * FROM bestellung WHERE id = 1, ohne ihn auszuführen. Zeitziel: 15 s - Aktualisiere die Statistiken von
bestellungund zeige, wie viele SeitenANALYZEliest. Zeitziel: 20 s - 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;
EOFErwartet (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 tag05Die 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 aufINVALIDprü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_statementsundauto_explain(Tag 7) zeigen, welche Pläne wirklich Zeit kosten. - Index-Reviews regelmäßig: ungenutzte, doppelte und
INVALIDIndizes kosten Schreibleistung, WAL-Volumen und Backup-Platz. - Große Indizes in Wartungsfenstern oder mit
CONCURRENTLYundmaintenance_work_mem; Fortschritt inpg_stat_progress_create_index. - Pläne können nach einem Major-Upgrade oder neuen Statistiken kippen
– nach
pg_upgradefehlende Statistiken nachziehen – ab 18vacuumdb --all --analyze-in-stages --missing-stats-only(Tag 13). - Für korrelierte Spalten helfen erweiterte Statistiken
(
CREATE STATISTICS), bevor man an Planer-Parametern dreht.