So optimieren Sie die PostgreSQL-Abfrageleistung im Jahr 2026: Indizes, EXPLAIN und Tuning

Die meisten Leistungsprobleme von PostgreSQL lassen sich auf eine Handvoll Ursachen zurückführen: ein fehlender Index, ein Index, den der Planer nicht verwenden kann, eine Abfrage, die weit mehr Zeilen abruft, als sie benötigt, oder Statistiken, die die Daten nicht mehr widerspiegeln. In diesem Leitfaden erfahren Sie, wie Sie herausfinden, welches Problem Sie haben, und wie Sie es beheben, und zwar in der Reihenfolge, in der Probleme am schnellsten gefunden werden.

Schritt 1: Finden Sie die langsamen Abfragen

Raten Sie nicht. Aktivieren Siepg_stat_statements, das Ausführungsstatistiken für jede Abfrageform aufzeichnet.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then restart, and in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Highest total time — usually where the real wins are
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sortieren nachinsgesamt Zeit statt Mittel. Eine Abfrage, die 20 ms dauert, aber 100.000 Mal pro Stunde ausgeführt wird, kostet weitaus mehr als eine Abfrage, die zweimal täglich 2 Sekunden dauert, und ist normalerweise einfacher zu beheben.

Protokollieren Sie auch langsame Abfragen, damit Sie erkennen, was in der Produktion passiert, aber nicht beim Testen.

-- postgresql.conf
log_min_duration_statement = 500   -- log anything over 500ms
log_lock_waits = on
log_temp_files = 0                 -- log every temp file (indicates spilling)

Schritt 2: Lesen Sie EXPLAIN ANALYZE richtig

Verwenden Sie immerEXPLAIN (ANALYZE, BUFFERS). EinfachEXPLAIN zeigt die Schätzung des Planers; ANALYZE führt tatsächlich die Abfrage aus und meldet die Realität, und die Lücke zwischen beiden ist oft der Fehler.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

Lesen Sie die Ausgabe vom innersten Knoten nach außen und suchen Sie nach vier Dingen.

Geschätzte im Vergleich zu tatsächlichen Zeilen. rows=10 ... actual rows=48000 bedeutet, dass der Planer völlig falsch lag und jede Entscheidung, die er nachgelagert getroffen hat, verdächtig ist. Normalerweise veraltete Statistiken.

Sequentielle Scans auf großen Tabellen. Gut bei kleinen Tischen und eine rote Fahne bei großen Tischen mit einem selektiven Filter.

Durch Filter entfernte Zeilen. Eine hohe Zahl bedeutet, dass die Datenbank weit mehr Zeilen gelesen hat, als sie zurückgegeben hat – die klassische Signatur für fehlende Indizes.

Externe Zusammenführung oder festplattenbasierte Sortierung. Die Sortierung wurde auf die Festplatte übertragen, weilwork_mem war zu klein dafür.

-- Refresh statistics when estimates are wrong
ANALYZE orders;

-- Collect more detail on a column with skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Schritt 3: Indexieren Sie richtig

Die Spaltenreihenfolge in einem zusammengesetzten Index ist von enormer Bedeutung. Die Regel: Zuerst Gleichheitsspalten, dann Bereichsspalten und dann die Spalten, nach denen Sie sortieren.

-- For: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

Mitstatus Der Index beschränkt sich zunächst auf übereinstimmende Zeilen und liest diese dann bereits geordnet nachcreated_at, sodass die Sortierung vollständig verschwindet. Wenn Sie die Reihenfolge umkehren, wird der Index weitaus weniger nützlich.

Teilindizes sind erheblich kleiner, wenn Sie immer nur eine Teilmenge abfragen.

-- If 98% of orders are completed and you only query pending ones
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

Abdeckindizes Lassen Sie die Abfrage allein über den Index beantworten und vermeiden Sie die Tabelle vollständig.

CREATE INDEX CONCURRENTLY idx_orders_lookup
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);
-- EXPLAIN then shows "Index Only Scan"

Erstellen Sie Indizes auf Live-Systemen immer mitCONCURRENTLY, was Schreibvorgänge nicht blockiert.

Schritt 4: Warum ein Index ignoriert wird

Eine häufige Frustration: Der Index existiert und der Planer wird ihn nicht verwenden. Übliche Ursachen:

Auf die Spalte wird eine Funktion angewendet. Dadurch wird der Index unbrauchbar.

-- ❌ Index on email cannot be used
WHERE lower(email) = 'user@example.com';

-- ✅ Index the expression instead
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));

Typkonflikt. Vergleich einesbigint Spalte in einen String erzwingt eine Umwandlung, die den Index zunichte macht. Passen Sie die Typen in Ihren Abfrageparametern an.

Führender Platzhalter in LIKE. LIKE '%term' kann keinen B-Tree-Index verwenden. Verwenden Sie dazu einen Trigrammindex.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);
-- now ILIKE '%widget%' can use an index

Die Abfrage ist nicht selektiv. Wenn ein Filter mit einem großen Teil der Tabelle übereinstimmt, ist ein sequentielles Scannen tatsächlich schneller und der Planer ist korrekt. Nicht jeder „unbenutzte Index“ ist ein Fehler.

Schritt 5: N+1-Abfragen beheben

Die häufigste Ursache für die Datenbanklast auf Anwendungsebene und in langsamen Abfrageprotokollen normalerweise unsichtbar, da jede einzelne Abfrage schnell ist.

-- 1 query for orders, then one per order for the user: 101 round trips
SELECT * FROM orders LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;   -- ... and so on

-- ✅ One query
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

In einem ORM bedeutet dies Eager Loading –includes im aktiven Datensatz,selectinload in SQLAlchemy,include in Prisma. Erkennen Sie das Muster, indem Sie die Abfragen pro Anfrage in der Entwicklung zählen. Ein plötzlicher Sprung mit der Listengröße ist die Signatur.

Schritt 6: Skalierbare Paginierung

OFFSET wird umso langsamer, je tiefer Sie blättern, da die Datenbank jede übersprungene Zeile generieren und verwerfen muss.

-- ❌ Reads and throws away 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination — constant time at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Einschließlichid Als Tiebreaker wird die Gesamtsumme der Reihenfolge ermittelt, wodurch verhindert wird, dass Zeilen übersprungen oder wiederholt werden, wenn Zeitstempel kollidieren.

Schritt 7: Konfiguration, die es wert ist, geändert zu werden

Die Standardeinstellungen sind konservativ und gehen von einer sehr bescheidenen Hardware aus.

random_page_cost ist die einflussreichste und am wenigsten bekannte. Der Standardwert 4,0 geht davon aus, dass zufällige Lesevorgänge viermal teurer sind als sequentielle Lesevorgänge, was für mechanische Laufwerke zutrifft. Auf SSDs vermeidet es den Planer, Indizes zu verwenden, die er verwenden sollte.

-- Test the effect on a single session before changing it globally
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Schritt 8: Aufblasen und absaugen

PostgreSQL behält alte Zeilenversionen, bis das Vakuum sie zurückfordert. Starker Aktualisierungs- oder Löschverkehr führt zu einer Aufblähung, was dazu führt, dass bei Scans mehr Seiten als nötig gelesen werden.

-- Which tables are bloated and when were they last vacuumed?
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Rebuild a bloated table without a long exclusive lock
VACUUM (ANALYZE, VERBOSE) orders;

-- For severe bloat, rebuild indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

Machen Sie bei Tabellen mit hohem Schreibverkehr aggressiver für die jeweilige Tabelle und nicht für global.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Nicht verwendete und doppelte Indizes finden

Jeder Index verlangsamt Schreibvorgänge und verbraucht Speicherplatz. Überprüfen Sie sie regelmäßig.

-- Indexes that have never been scanned
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Überprüfen Sie die Betriebszeit, bevor Sie handeln – ein seit einem Neustart letzte Woche nicht genutzter Index kann für einen monatlichen Bericht unerlässlich sein.

Fazit

Die Optimierung von PostgreSQL ist eine Reihenfolge, keine Vermutung:Finden Sie die teuren Abfragen mitpg_stat_statements sortiert nach Gesamtzeit, lesen SieEXPLAIN (ANALYZE, BUFFERS) Fügen Sie für Schätzungs-gegen-Ist-Lücken und Zeilen, die durch den Filter entfernt wurden, zusammengesetzte Indizes mit Gleichheitsspalten zuerst und Bereichsspalten danach hinzu, beseitigen Sie N+1-Muster mit Eager Loading, ersetzen Sie Deep OFFSET durch Keyset-Paginierung und legen Sierandom_page_costfest passend für SSDs. Halten Sie dann die Statistiken auf dem neuesten Stand und optimieren Sie das automatische Vakuum, denn eine gut indizierte Datenbank mit veralteten Statistiken führt immer noch zu schlechten Plänen.