PostgreSQL: Erweiterte Abfrageoptimierung und Indizes

Warum Optimierung Fundamental ist

Langsame Abfragen können eine Anwendung lahmlegen. PostgreSQL bietet leistungsstarke Tools zur Analyse und Optimierung der Datenbankperformance.

1. EXPLAIN ANALYZE

Der erste Schritt ist zu verstehen, was PostgreSQL mit Ihren Abfragen macht:

EXPLAIN ANALYZE
SELECT p.*, c.name as category_name
FROM blog_post p
JOIN blog_category c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10;

Ausgabe Interpretieren

Limit (cost=0.29..1.23 rows=10 width=856) (actual time=0.045..0.089 rows=10 loops=1)
  ->  Nested Loop (cost=0.29..94.12 rows=1000 width=856) (actual time=0.044..0.086 rows=10 loops=1)
        ->  Index Scan using blog_post_published_at on blog_post p
              (cost=0.15..52.15 rows=1000 width=848) (actual time=0.025..0.035 rows=10 loops=1)
              Filter: (status = 'published')
        ->  Index Scan using blog_category_pkey on blog_category c
              (cost=0.14..0.16 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=10)
Planning Time: 0.245 ms
Execution Time: 0.128 ms

Wichtige Metriken:

• cost: Kostenschätzung (willkürliche Einheiten)
• actual time: tatsächliche Zeit in Millisekunden
• rows: verarbeitete/geschätzte Zeilen
• loops: wie oft der Knoten ausgeführt wird

2. Indextypen

B-Tree (Standard)

-- Ideal für =, <, >, <=, >=, BETWEEN, IN, IS NULL
CREATE INDEX idx_post_status ON blog_post(status);
CREATE INDEX idx_post_published ON blog_post(published_at DESC);

Hash Index

-- Nur für Gleichheit (=)
CREATE INDEX idx_post_slug ON blog_post USING hash(slug);

GIN (Generalized Inverted Index)

-- Für Arrays, JSONB, Volltextsuche
CREATE INDEX idx_post_tags ON blog_post USING gin(tags);
CREATE INDEX idx_post_metadata ON blog_post USING gin(metadata jsonb_path_ops);

GiST

-- Für geometrische Daten, Bereiche, Volltext
CREATE INDEX idx_post_search ON blog_post USING gist(to_tsvector('german', title || ' ' || content));

3. Partielle Indizes

-- Indiziert nur bestimmte Zeilen
CREATE INDEX idx_published_posts ON blog_post(published_at DESC)
WHERE status = 'published';

-- Spart Speicherplatz und beschleunigt gefilterte Abfragen

4. Zusammengesetzte Indizes

-- Die Reihenfolge der Spalten ist wichtig!
CREATE INDEX idx_category_date ON blog_post(category_id, published_at DESC);

-- Nützlich für Abfragen wie:
SELECT * FROM blog_post
WHERE category_id = 5
ORDER BY published_at DESC;

5. Volltextsuche

-- tsvector-Spalte hinzufügen
ALTER TABLE blog_post ADD COLUMN search_vector tsvector;

-- Vektor aktualisieren
UPDATE blog_post SET search_vector =
    setweight(to_tsvector('german', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('german', coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('german', coalesce(content, '')), 'C');

-- GIN-Index erstellen
CREATE INDEX idx_post_fts ON blog_post USING gin(search_vector);

-- Volltextabfrage
SELECT title, ts_rank(search_vector, query) as rank
FROM blog_post, to_tsquery('german', 'django & python') query
WHERE search_vector @@ query
ORDER BY rank DESC;

6. Optimierte JSONB-Abfragen

-- Index für bestimmte Schlüssel
CREATE INDEX idx_metadata_author ON blog_post((metadata->>'author'));

-- Index für Schlüsselexistenz
CREATE INDEX idx_metadata_gin ON blog_post USING gin(metadata);

-- Effiziente Abfragen
SELECT * FROM blog_post WHERE metadata @> '{"featured": true}';
SELECT * FROM blog_post WHERE metadata ? 'video_url';

7. Statistik-Optimierung

-- Statistiken für Query-Planner aktualisieren
ANALYZE blog_post;

-- Detailliertere Statistiken für wichtige Spalten
ALTER TABLE blog_post ALTER COLUMN status SET STATISTICS 1000;
ANALYZE blog_post;

8. PostgreSQL-Konfiguration

-- postgresql.conf
shared_buffers = 256MB          # 25% des RAM
effective_cache_size = 768MB    # 75% des RAM
work_mem = 64MB                 # Für Sortierung/Hashing
maintenance_work_mem = 128MB    # Für VACUUM, CREATE INDEX
random_page_cost = 1.1          # SSD (Standard 4.0 für HDD)

Langsame Abfragen Überwachen

-- Logging langsamer Abfragen aktivieren
log_min_duration_statement = 100  # ms

-- pg_stat_statements verwenden
CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Fazit

Optimierung ist ein kontinuierlicher Prozess. Überwachen Sie Abfragen, analysieren Sie mit EXPLAIN und erstellen Sie gezielte Indizes für Ihre häufigsten Abfragen.