Approach (methodical steps)1. Reproduce & measure: capture query plan (EXPLAIN/EXPLAIN ANALYZE), runtime, I/O, row estimates and hotspots.2. Push predicates early: ensure WHERE filters are applied as soon as possible (predicate pushdown) and on partition columns for partition pruning.3. Replace expensive nested loops/subqueries with joins or window functions where appropriate.4. Pre-aggregate or materialize expensive work if reused (materialized view / aggregated table).5. Add or adjust indexes (covering, filtered) only after verifying plan benefits.6. Iterate: test cost and runtime for each change.Example: nested correlated subquery -> window functionSlow pattern (per-row correlated subquery):sql
SELECT f.user_id,
f.event_time,
(SELECT COUNT(*) FROM fact f2
WHERE f2.user_id = f.user_id
AND f2.event_time >= f.event_time - interval '7 days'
AND f2.event_time <= f.event_time) AS cnt_7d
FROM fact f
WHERE f.event_time BETWEEN '2025-01-01' AND '2025-02-01';
sql
SELECT user_id, event_time, cnt_7d
FROM (
SELECT user_id, event_time,
COUNT(*) OVER (PARTITION BY user_id
ORDER BY event_time
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS cnt_7d,
ROW_NUMBER() OVER () rn
FROM fact
WHERE event_time BETWEEN '2025-01-01' AND '2025-02-01' -- predicate pushdown, enables partition pruning
) t;
