EXPLAIN ANALYZE ist Ihr Debugger für SQL. Seq Scan auf einer großen Tabelle = Problem. Index Scan = Lösung.
EXPLAIN lesen¶
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123 AND status = ‘active’; – Schlecht: – Seq Scan on orders (cost=0..10000 rows=100 width=200) – Filter: (user_id = 123 AND status = ‘active’) – Rows Removed by Filter: 99900 – Gut: – Index Scan using idx_orders_user_status (cost=0..10 rows=100 width=200) – Index Cond: (user_id = 123 AND status = ‘active’)
Optimierungstechniken¶
- Fehlenden Index hinzufügen (siehe EXPLAIN)
- Subquery in JOIN umschreiben
- Materialized View für wiederholte Aggregationen
- LIMIT für Top-N-Abfragen
- Große Tabellen partitionieren
Materialized View¶
CREATE MATERIALIZED VIEW monthly_stats AS SELECT DATE_TRUNC(‘month’, created_at) as month, COUNT(*) as orders, SUM(amount) as revenue FROM orders GROUP BY 1; – Aktualisierung (manuell oder per Cron) REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
Wichtigste Erkenntnis¶
EXPLAIN ANALYZE immer. Seq Scan = Index hinzufügen. Materialized Views für Aggregationen. Monitoring langsamer Abfragen.