PostgreSQL-Abfrageoptimierung: Von langsam zu Sub-Millisekunde | Akousa

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

sql

-- Abfrage: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Gut: Gleichheit zuerst, dann Bereich, dann Sortierung
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Schlecht: Bereich vor Gleichheit
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

sql

-- Ein weiteres häufiges Muster: Soft Deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique Constraint nur auf aktive Datensätze
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

sql

-- JSONB-Containment-Abfragen
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Jetzt ist das schnell:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Volltextsuche
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

sql

-- IP-Range-Lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Zeitliche Ranges (Terminüberschneidungen)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Überlappende Buchungen finden:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

sql

-- Gut für reine Gleichheits-Lookups auf großen Text-Spalten
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Das ist schnell:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Das KANN den Hash-Index NICHT nutzen:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

sql

-- Ungenutzte Indizes finden
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- Primary Key und Unique Constraints ausschließen
  )
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- Doppelte Indizes finden (gleiche Spalten, gleiche Reihenfolge)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

python

# Python / SQLAlchemy — die klassische Falle
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Das löst eine separate Abfrage für JEDE Bestellung aus
    print(f"Customer: {order.customer.name}")

sql

-- Abfrage 1: Alle ausstehenden Bestellungen holen
SELECT * FROM orders WHERE status = 'pending';
 
-- Abfrage 2: Kunde für Bestellung 1 holen
SELECT * FROM customers WHERE id = 101;
 
-- Abfrage 3: Kunde für Bestellung 2 holen
SELECT * FROM customers WHERE id = 102;
 
-- ...Abfrage 502: Kunde für Bestellung 500 holen
SELECT * FROM customers WHERE id = 600;

sql

-- Alle Abfragen loggen, die mehr als 0ms dauern (also alle)
SET log_min_duration_statement = 0;
 
-- Besser für Produktion: nur langsame Abfragen loggen
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms Schwelle
SELECT pg_reload_conf();

sql

-- auto_explain für langsame Abfragen aktivieren
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

python

# SQLAlchemy — joinedload holt Kunden in derselben Abfrage
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

sql

-- Ansatz 1: Einzelner JOIN (kann Duplikate erzeugen bei One-to-Many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Ansatz 2: Zwei Abfragen (weniger Datentransfer wenn Bestellungen viele Spalten haben)
SELECT * FROM orders WHERE status = 'pending';
-- Anwendung sammelt order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

sql

-- Subquery in WHERE — oft okay, manchmal furchtbar
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — normalerweise die beste Wahl
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialisiert in PG 11 und darunter, optimiert in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

sql

-- EXISTS — stoppt beim ersten Treffer, großartig zur Existenzprüfung
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN mit Subquery — baut zuerst die vollständige Ergebnismenge auf
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — kann Duplikate erzeugen wenn returns mehrere Zeilen pro Bestellung hat
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

sql

-- Schlecht: holt alle 25 Spalten inklusive einer TEXT-Spalte mit Kilobytes an Daten
SELECT * FROM products WHERE category = 'electronics';
 
-- Gut: holt nur was du brauchst
SELECT id, name, price FROM products WHERE category = 'electronics';

sql

-- Langsam: korrelierte Subquery läuft einmal pro Zeile
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Schnell: Window Functions berechnen in einem einzigen Durchgang
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

sql

-- Langsam: korrelierte Subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Schnell: DISTINCT ON (PostgreSQL-spezifisch, extrem nützlich)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Auch schnell: ROW_NUMBER Window Function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

sql

-- Seite 1: schnell
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Seite 500: langsam — PostgreSQL muss 9.980 Zeilen scannen und verwerfen
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Seite 5000: sehr langsam — scannt 99.980 Zeilen um 20 zurückzugeben
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

sql

-- Eine einzelne Tabelle analysieren
ANALYZE orders;
 
-- Die gesamte Datenbank analysieren
ANALYZE;
 
-- Aktuelle Statistiken für eine Spalte anzeigen
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

sql

-- Autovacuum-Status prüfen
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

sql

-- Aggressiveres Autovacuum für schreibintensive Tabellen
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- Vacuum bei 1% toten Tupeln (Standard 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- Analyze bei 0,5% Änderungen (Standard 10%)
  autovacuum_vacuum_cost_delay = 2           -- Schnelleres Vacuum (Standard 2ms in neueren PG)
);

sql

-- Tabellen-Bloat schätzen (vereinfachte Version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool-Modus
pool_mode = transaction
 
; Pool-Sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

javascript

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer-Port, nicht PostgreSQL-Port
  database: "mydb",
  max: 20, // entspricht PgBouncer default_pool_size
  statement_timeout: 30000,
  // Prepared Statements für PgBouncer Transaction Mode deaktivieren
  prepare: false,
});

sql

-- Top 10 Abfragen nach gesamter Ausführungszeit
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

sql

-- Welche Indizes gibt es auf dieser Tabelle?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

sql

-- Index concurrent erstellen (sperrt die Tabelle nicht)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifiziere, dass der Index genutzt wird
EXPLAIN (ANALYZE, BUFFERS) <die langsame Abfrage>;

sql

-- work_mem: Speicher für Sorts und Hash Joins (pro Operation, nicht pro Verbindung)
-- Standard ist 4MB, das ist zu niedrig für komplexe Abfragen
SET work_mem = '64MB';  -- probiere das und führe EXPLAIN ANALYZE erneut aus
 
-- effective_cache_size: teilt dem Planner mit, wie viel Disk-Cache zu erwarten ist
-- Setze auf ~75% des gesamten RAM
SHOW effective_cache_size;
 
-- random_page_cost: Verhältnis von Random I/O zu sequenzieller I/O
-- Standard ist 4.0, setze auf 1.1-1.5 für SSDs
SHOW random_page_cost;

sql

-- pg_stat_statements zurücksetzen für frische Daten nach deinen Änderungen
SELECT pg_stat_statements_reset();
 
-- In einer Stunde/einem Tag nachschauen
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

sql

-- 1. pg_stat_statements installieren
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Slow Query Logging aktivieren (50ms ist meine Schwelle)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-geeignete Einstellungen
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. work_mem vom mageren Standard erhöhen
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. effective_cache_size auf 75% des RAM setzen
-- Für einen 16GB-Server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. shared_buffers auf 25% des RAM setzen
-- Für einen 16GB-Server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Autovacuum-Reaktionsfähigkeit verbessern
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Änderungen anwenden
SELECT pg_reload_conf();
-- Hinweis: shared_buffers erfordert einen Neustart

sql

-- Tabellen, die Aufmerksamkeit brauchen
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Die Debugging-Denkweise: Nicht raten, messen

EXPLAIN — Der Bauplan

EXPLAIN ANALYZE — Die Wahrheit

EXPLAIN (ANALYZE, BUFFERS) — Das vollständige Bild

EXPLAIN-Optionen, die ich tatsächlich verwende

Abfragepläne wie ein Detektiv lesen

Seq Scan — Der volle Tabellenscan

Index Scan — Der gezielte Lookup

Index Only Scan — Der Heilige Gral

Bitmap Scan — Der Mittelweg

Schlechte Schätzungen erkennen

Index-Strategie: Der richtige Index für den richtigen Job

B-tree — Das Standard-Arbeitspferd

Covering Indexes mit INCLUDE

Partial Indexes — Indiziere nur, was wichtig ist

GIN Indexes — Volltextsuche und JSONB

GiST Indexes — Geometrische und Range-Typen

Hash Indexes — Nische aber nützlich

Wann Indizes schaden

Das N+1-Abfrageproblem

Wie N+1 aussieht

N+1 in Logs erkennen

Der Fix: Eager Loading oder JOINs

JOINs vs mehrere Abfragen

Häufige Abfrage-Rewrites

Subquery vs JOIN vs CTE

EXISTS vs IN vs JOIN

SELECT * vermeiden

Window Functions vs Subqueries

Pagination richtig machen

Tabellenstatistiken und Vacuuming

ANALYZE: Statistiken aktualisieren

Autovacuum-Tuning

Table Bloat

Connection Pooling

Warum du einen Connection Pooler brauchst

PgBouncer-Konfiguration

Transaction vs Session Mode

Pool-Sizing-Formel

Praktische Checkliste: Die genauen Schritte für jede langsame Abfrage

Schritt 1: Die tatsächliche Abfrage besorgen

Schritt 2: EXPLAIN (ANALYZE, BUFFERS) ausführen

Schritt 3: Tabellenstatistiken prüfen

Schritt 4: Vorhandene Indizes prüfen

Schritt 5: Indizes erstellen oder ändern

Schritt 6: Abfrage-Rewrites erwägen

Schritt 7: Server-Einstellungen prüfen

Schritt 8: Nach dem Fix überwachen

Bonus: Quick Wins, die ich auf jede neue Datenbank anwende

Abschließende Gedanken

Ähnliche Beiträge

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Moderne Authentifizierung 2026: JWT, Sessions, OAuth und Passkeys