EXPLAIN ANALYZE in PostgreSQL: Wie man einen Plan liest und welche Indizes wirklich benötigt werden
Wo soll ich anfangen: nicht mit einem Plan, sondern mit einer Liste von Wünschen
Indizes werden für bestimmte Abfragen hinzugefügt und nicht „für alle Spalten von WHERE“. Finden Sie zunächst heraus, welche Abfragen wirklich teuer sind. Die Erweiterung pg_stat_statements sammelt Statistiken zu normalisierten Abfragen:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;Die Sortierung nach total_exec_time zeigt, wo die Datenbank insgesamt ihre Zeit verbringt. Eine millionenfach aufgerufene 3-ms-Abfrage ist oft wichtiger als ein 2-sekündiger Bericht einmal am Tag.
So entfernen Sie einen Plan
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total, created_at FROM orders WHERE user_id = 4815 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
ANALYZE führt die Anfrage tatsächlich aus. Für UPDATE und DELETE wickeln Sie es in eine Rollback-Transaktion ein:
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now(); ROLLBACK;
BUFFERS zeigt an, wie viele Seiten aus dem Cache (shared hit) und von der Festplatte (read) gelesen wurden. In PostgreSQL 18 werden diese Informationen automatisch zusammen mit ANALYZE angezeigt; In früheren Versionen muss der Parameter explizit angegeben werden.
So lesen Sie Knoten
Plan - Baum. Von innen nach außen lesen: Die am meisten verschachtelten Knoten werden zuerst ausgeführt. Jeder Knoten hat zwei Zahlengruppen:
Limit (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
-> Sort (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on orders (cost=0.00..41250.00 rows=1180 width=24)
(actual time=0.030..95.880 rows=1342 loops=1)
Filter: ((user_id = 4815) AND (status = 'paid'::text))
Rows Removed by Filter: 1998658Dies ist ein illustrativer Plan, aber das Muster ist typisch:
- `cost` – Scheduler-Bewertung in herkömmlichen Einheiten. Es macht keinen Sinn, es zwischen verschiedenen Anfragen zu vergleichen.
- `actual time=A..B` – Millisekunden vor der ersten Zeile und vor der letzten. Die Zeit der Eltern umfasst die Zeit der Kinder.
- `rows` in der Beurteilung und in der Tatsache. Eine Abweichung um Größenordnungen ist das Hauptsymptom eines Problems: Der Planer hat einen Plan für die falsche Anzahl von Leitungen ausgewählt.
- `loops` – wie oft der Knoten ausgeführt wurde. Echtzeit und Strings müssen mit
loopsmultipliziert werden. Interner Nested-Loop-Knoten mitloops=50000und einer Zeit von 0,02 ms ist eine Sekunde. - `Rows Removed by Filter` – Vergeblich gelesene Zeilen. Hier wurden zwei Millionen Leitungen für 1342 benötigte Leitungen durchgesehen.
Welchen Index benötigt diese Abfrage?
Die Abfrage filtert nach user_id und status und sortiert nach created_at. Die Reihenfolge der Spalten in einem zusammengesetzten Index lautet: Zuerst „Gleichspalten“ und dann „Spalten sortieren“.
CREATE INDEX CONCURRENTLY orders_user_status_created_idx ON orders (user_id, status, created_at DESC);
Mit einem solchen Index wird der Plan zu Index Scan mit Limit: Die Datenbank liest genau 20 Datensätze nach Index in der erforderlichen Reihenfolge und sortiert nichts. CONCURRENTLY blockiert das Schreiben in die Tabelle während der Erstellung nicht, funktioniert jedoch nicht innerhalb einer Transaktion und hinterlässt bei einem Fehler einen ungültigen Index, der gelöscht und neu erstellt werden muss.
Wenn fast alle Abfragen einen einzelnen Statuswert berücksichtigen, ist ein Teilindex kleiner und kostengünstiger in der Verwaltung:
CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at) WHERE status = 'pending';
Es wird nur verwendet, wenn die Abfragebedingung status = 'pending' wörtlich enthält.
Knoten zum Anschauen
- `Sort Method: external merge Disk: ...` – die Sortierung passte nicht in
work_memund ging auf die Festplatte. Es wird mit einem Index mit der gewünschten Reihenfolge oder aufsteigendwork_memfür eine bestimmte Sitzung und nicht global behandelt. - `Bitmap Heap Scan` mit `Recheck Cond` und `Heap Blocks: lossy` – die Bitmap passte nicht in den Speicher und wurde ausgelagert.
- `Index Only Scan` mit einem großen `Heap Fetches` – der Index deckt die Abfrage ab, aber die Sichtbarkeitskarte ist veraltet und die Datenbank geht immer noch zur Tabelle.
VACUUMhilft (oder das automatische Vakuumieren für diese Tabelle einstellen). - `Hash Join` mit `Batches: 8` – die Hash-Tabelle passte nicht in den Speicher.
- Schätzung `rows=1`, aber Tatsache - Tausende. Statistiken aktualisieren
ANALYZE orders;. Wenn die Spalten korrelieren (Stadt und Land), helfen erweiterte Statistiken:CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.
Warum gibt es einen Index und wird nicht verwendet?
- 1.Funktion oder Umwandlung für eine Spalte:
WHERE lower(email) = $1verwendet keinen Index füremail. Wir benötigen einen Index basierend auf dem Ausdruck(lower(email)). - 2.
LIKE '%abc'mit führendem Prozentsatz. B-Tree hilft hier nicht weiter, Sie benötigenpg_trgmmit GIN. - 3.Die Bedingung wählt einen großen Teil der Tabelle aus. Sequentielles Lesen ist dann tatsächlich günstiger, und der Planer hat Recht.
- 4.Verschiedene Typen: Spalte
bigint, Parameter übergeben alsnumeric. - 5.Kleiner Tisch. Für ein paar tausend Zeilen ist
Seq Scannormal.
Zusätzliche Indizes kosten auch Geld
Jeder Index verlangsamt Einfügungen und Aktualisierungen und nimmt Speicherplatz ein. Kandidaten für eine Entfernung:
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Statistiken sind lokal auf dem Server: Der Index, der auf dem Primärserver nicht benötigt wird, kann Berichte für das Replikat bereitstellen. Und löschen Sie keine Indizes, die UNIQUE oder den Primärschlüssel bereitstellen.
Wenn EXPLAIN das Problem nicht anzeigt
Wenn eine Abfrage in einer manuellen Ausführung schnell, in der Produktion jedoch langsam ist, kann dies an einer Blockierung, einem Verbindungskonflikt oder einem anderen Plan für die parametrisierte Abfrage liegen. Aktivieren Sie auto_explain mit dem Schwellenwert auto_explain.log_min_duration, um den langsamen Ausführungsplan aus den Protokollen abzurufen, und achten Sie auf pg_stat_activity auf die Lock-Erwartungen.