Wie wir eine 10-Sekunden-Datenbankabfrage auf 50 ms verkürzt haben

Das Problem: Ein Dashboard, dessen Laden 10 Sekunden dauerte

Das Analyse-Dashboard eines Kunden brauchte über 10 Sekunden zum Laden. Die Nutzer brachen den Vorgang ab. Der CTO geriet in Panik. Das Backend-Team meinte: „Lokal funktioniert es einwandfrei.“ Kommt Ihnen das bekannt vor?

Hier erfahren Sie genau, wie wir das Problem aufgespürt, behoben und die Reaktionszeit auf 50 ms gesenkt haben. Keine Zauberei – nur systematische Fehlersuche.

Schritt 1: Die langsame Abfrage finden

Bevor wir Vermutungen anstellten, haben wir das Slow-Query-Protokoll von PostgreSQL aktiviert, um alle Abfragen mit einer Dauer von mehr als 500 ms zu erfassen:

-- In PostgreSQL: `
log_min_duration_statement = 500

` -- Oder für eine einzelne Sitzung: 
`SET log_min_duration_statement = 500;`

Der Übeltäter war sofort klar – eine einzige Abfrage, die vier Tabellen miteinander verknüpfte, dauerte 10,2 Sekunden:

SELECT o.id, o.total, c.name, c.email,      
 COUNT(oi.id) AS item_count,      
 SUM(oi.quantity * oi.unit_price) as 
calculated_totalFROM orders 
oJOIN customers c ON c.id = o.
customer_idJOIN order_items oi ON oi.order_id = o.
idJOIN products p ON p.id = oi.
product_idWHERE o.created_at >= '2025-01-01'
  AND o.status IN ('completed', 'shipped')
  AND c.region = 
'asia-pacific'GROUP BY o.id, o.total, c.name, c.
emailORDER BY o.created_at 
DESCLIMIT 50;

Schritt 2: EXPLAIN ANALYZE – Sehen Sie, was PostgreSQL tatsächlich tut

Die Ausführung von EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) brachte das eigentliche Problem ans Licht:

Seq Scan auf orders o  (Kosten=0,00..185432,00 Zeilen=892145 Breite=48)
  Filter: (created_at >= '2025-01-01' UND status = ANY(...))
  Durch Filter entfernte Zeilen: 3241876
  Puffer: gemeinsam genutzt Treffer=12 gelesen=142891

Sequentieller Scan über 4 Millionen Zeilen. PostgreSQL las die gesamte Tabelle „orders“ von der Festplatte, da es keinen Index für „created_at“ oder „status“ gab. Der Wert „Buffers: read=142891“ bestätigte einen enormen Festplatten-I/O-Aufwand.

Schritt 3: Die Lösung – zusammengesetzter Index + Teilindex

Ein einspaltiger Index auf `created_at` wäre hilfreich, aber ein zusammengesetzter Index, der genau auf dieses Abfragemuster zugeschnitten ist, ist weitaus besser:

-- Zusammengesetzter Index, der der WHERE-Klausel 
entsprichtCREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Index auf die Fremdschlüsselverknüpfung (wird oft übersehen!)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- Abdeckender Index

Der WHERE-Status IN (...) im Index macht ihn zu einem Teilindex – er indiziert nur die Zeilen, die wir tatsächlich abfragen, wodurch er klein und schnell bleibt. Das INCLUDE im zweiten Index macht ihn zu einem abdeckenden Index – PostgreSQL kann die Abfrage allein anhand des Indexes beantworten, ohne auf die Tabelle zurückzugreifen.

Schritt 4: Umstrukturierung der Abfrage mit CTE

Die ursprüngliche Abfrage berechnete die Aggregate über die gesamte Ergebnismenge, bevor die LIMIT-Klausel angewendet wurde. Wir haben sie so umgestaltet, dass zuerst gefiltert und dann aggregiert wird:

WITH recent_orders AS (
  SELECT o.id, o.total, o.customer_id, o.created_at
  FROM orders o
  WHERE o.created_at >= '2025-01-01'
    AND o.status IN ('completed', 'shipped')
  ORDER BY o.created_at DESC
  LIMIT 50
)
SELECT ro.id, ro.total, c.name, c.email,      
 COUNT(oi.id) as item_count,      
 SUM(oi.quantity * oi.Stückpreis) as 
berechnete_GesamtsummeFROM recent_orders 
roJOIN customers c ON c.id = ro.
customer_idJOIN order_items oi ON oi.order_id = ro.
idGROUP BY ro.id, ro.total, c.name, c.email, ro.
created_atORDER BY ro.created_at DESC;

Hierbei werden zunächst nur 50 Datensätze abgerufen, dann erfolgt die Verknüpfung. Anstatt über Millionen von Zeilen zu aggregieren, führen wir die Verknüpfung mit nur 50 Datensätzen durch.

Das Ergebnis

Metrisch	Zuvor	Danach
Abfragezeit	10.200 ms	47 ms
Gescannte Zeilen	4.134.021	50
Festplattenzugriffe	142.891 Puffer	12 Puffer
Verwendeter Index	Keine (sequenzieller Scan)	idx_orders_status_created

Gewonnene Erkenntnisse

1. Überprüfen Sie „EXPLAIN ANALYZE“ immer in der Produktionsumgebung, nicht nur lokal. Lokale Datenbanken verfügen über vorgewärmte Caches und kleine Datensätze.

2. Fremdschlüsselspalten benötigen Indizes. Django/Rails erstellen diese automatisch, doch in reinen SQL-Schemas fehlen sie oft.

3. LIMIT verhindert in Kombination mit GROUP BY keine vollständigen Tabellenscans. Strukturieren Sie die Abfrage so um, dass zuerst gefiltert wird.

4. Verwenden Sie bei der Erstellung von Indizes in der Produktionsumgebung die Option „CONCURRENTLY“, um eine Sperrung der Tabelle zu vermeiden.

Die teuerste Abfrage ist die, die niemand profiliert hat. Machen Sie „EXPLAIN ANALYZE“ zu einem festen Bestandteil Ihres Code-Review-Prozesses für alle Abfragen, die mehr als 10.000 Zeilen betreffen.
— alokknight Engineering