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.conf
log_min_duration_statement = 500

-- Or for a single session:
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_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE 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.email
ORDER BY o.created_at DESC
LIMIT 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 on orders o  (cost=0.00..185432.00 rows=892145 width=48)
  Filter: (created_at >= '2025-01-01' AND status = ANY(...))
  Rows Removed by Filter: 3241876
  Buffers: shared hit=12 read=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:

-- Composite index matching the WHERE clause
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC)
  WHERE status IN ('completed', 'shipped');

-- Index on the foreign key join (often missed!)
CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id)
  INCLUDE (quantity, unit_price);  -- covering 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.unit_price) as calculated_total
FROM recent_orders ro
JOIN customers c ON c.id = ro.customer_id
JOIN order_items oi ON oi.order_id = ro.id
GROUP BY ro.id, ro.total, c.name, c.email, ro.created_at
ORDER 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