SQL Query Optimization Guide

Practical optimization techniques for the queries in this library. Covers indexing strategy, common pitfalls, materialization patterns, and query plan reading.

Index Strategy by Query Category

Cohort Analysis

-- Core indexes for cohort queries
CREATE INDEX idx_users_signup_date ON users(signup_date);
CREATE INDEX idx_events_user_ts ON events(user_id, event_timestamp);
CREATE INDEX idx_events_name_user ON events(event_name, user_id, event_timestamp);

Why: Cohort queries always filter by signup_date and then join to events by user_id + time range. The compound index on events prevents a full table scan on the fact table.

Funnel Metrics

-- Funnel queries need fast lookup by event_name within time windows
CREATE INDEX idx_events_name_ts_user ON events(event_name, event_timestamp, user_id);
CREATE INDEX idx_events_session_ts ON events(session_id, event_timestamp);

Why: Funnel queries filter by event_name first, then by time window. Covering the user_id in the index avoids table lookups.

Retention

-- Retention checks "was user X active on day Y?"
CREATE INDEX idx_events_user_date ON events(user_id, DATE(event_timestamp));
-- Postgres: use expression index
CREATE INDEX idx_events_user_event_date ON events(user_id, (event_timestamp::date));

Revenue

CREATE INDEX idx_orders_user_date_status ON orders(user_id, order_date, status);
CREATE INDEX idx_orders_date_status ON orders(order_date, status) INCLUDE (total_amount, user_id);
CREATE INDEX idx_subs_status_period ON subscriptions(status, current_period_start, current_period_end);

Operational (Support Tickets)

CREATE INDEX idx_tickets_created_priority ON support_tickets(created_at, priority);
CREATE INDEX idx_tickets_status_priority ON support_tickets(status, priority, created_at);
CREATE INDEX idx_tickets_assigned ON support_tickets(assigned_to, resolved_at);

Common Performance Pitfalls

1. Correlated Subqueries in WHERE

Slow:

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM events e
    WHERE e.user_id = u.user_id
      AND e.event_timestamp > u.signup_date + INTERVAL '30 days'
);

Fast: Rewrite as JOIN

SELECT DISTINCT u.*
FROM users u
INNER JOIN events e ON u.user_id = e.user_id
WHERE e.event_timestamp > u.signup_date + INTERVAL '30 days';

2. Functions on Indexed Columns

Slow: (index on event_timestamp is useless here)

WHERE DATE(event_timestamp) = '2024-03-15'

Fast: (uses the index)

WHERE event_timestamp >= '2024-03-15'
  AND event_timestamp < '2024-03-16'

3. DISTINCT on Large Result Sets

Slow:

SELECT DISTINCT user_id FROM events WHERE event_name = 'purchase_completed'

Fast: (if you only need existence)

SELECT user_id FROM events WHERE event_name = 'purchase_completed' GROUP BY user_id

Or better, use EXISTS in the parent query.

4. Window Functions Over Entire Table

Problem: Without PARTITION BY, window functions scan the full result set.

Solution: Always limit the dataset with WHERE before applying windows:

-- Filter first, window second
WITH filtered AS (
    SELECT * FROM events WHERE event_timestamp > CURRENT_DATE - 30
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp)
FROM filtered;

5. FULL OUTER JOIN with Large Tables

Problem: FULL OUTER JOINs prevent index usage and force hash joins.

Solution: Rewrite as UNION of LEFT JOINs when possible:

SELECT a.*, b.* FROM table_a a LEFT JOIN table_b b ON ...
UNION ALL
SELECT a.*, b.* FROM table_b b LEFT JOIN table_a a ON ... WHERE a.id IS NULL

Materialized View Patterns

For expensive aggregations that run frequently:

-- Create materialized view for daily metrics
CREATE MATERIALIZED VIEW mv_daily_metrics AS
SELECT
    DATE(event_timestamp) AS metric_date,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(DISTINCT session_id) AS sessions,
    COUNT(*) AS total_events
FROM events
GROUP BY DATE(event_timestamp);

-- Refresh on schedule (e.g., via cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_metrics;

Best candidates for materialization:

• Daily/weekly aggregate tables (DAU, revenue, signups)
• Cohort membership (pre-compute which cohort each user belongs to)
• Feature adoption flags (boolean: "has user X ever done Y?")
• Running totals that don't change for past dates

Query Plan Reading Cheatsheet

Reading Cost Numbers

EXPLAIN output: (cost=0.43..8.45 rows=1 width=36)
                  ^         ^     ^       ^
                  |         |     |       Bytes per row
                  |         |     Estimated row count
                  |         Total cost (higher = slower)
                  Startup cost

Rules of thumb:

• Total cost > 10,000: Consider optimization
• rows estimate wildly wrong: Update statistics (ANALYZE table_name)
• Multiple Seq Scans on same table: Consolidate into one pass

EXPLAIN ANALYZE

Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for real performance data:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... your query ...;

This shows actual execution time, not just estimates.

Dialect-Specific Optimization Tips

PostgreSQL

• Use pg_stat_statements to find slow queries automatically
• Partial indexes: CREATE INDEX ... WHERE status = 'active'
• BRIN indexes for time-series data (smaller than B-tree)
• work_mem setting affects sort/hash performance

BigQuery

• Partition tables by date (PARTITION BY DATE(event_timestamp))
• Cluster by frequently-filtered columns
• Avoid SELECT * — column-oriented storage charges per column scanned
• Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT) for estimates

Snowflake

• Cluster keys on large tables (similar to BigQuery clustering)
• Use result caching (ALTER SESSION SET USE_CACHED_RESULT = TRUE)
• Micro-partitions are automatic — focus on cluster key choice
• Query profile in web UI shows exact bottlenecks