EXPLAIN ANALYZE je váš debugger pro SQL. Seq Scan na velké tabulce = problém. Index Scan = řešení.
EXPLAIN čtení¶
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123 AND status = ‘active’; – Špatně: – Seq Scan on orders (cost=0..10000 rows=100 width=200) – Filter: (user_id = 123 AND status = ‘active’) – Rows Removed by Filter: 99900 – Dobře: – Index Scan using idx_orders_user_status (cost=0..10 rows=100 width=200) – Index Cond: (user_id = 123 AND status = ‘active’)
Optimalizační techniky¶
- Přidejte chybějící index (viz EXPLAIN)
- Rewrite subquery na JOIN
- Materialized view pro opakované agregace
- LIMIT pro top-N dotazy
- Partition velké tabulky
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; – Refresh (manuálně nebo cron) REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
Key Takeaway¶
EXPLAIN ANALYZE vždy. Seq Scan = přidejte index. Materialized views pro aggregace. Monitoring pomalých dotazů.