🗄️ SQL Query Optimizer — Find + Fix Slow Queries

Query Optimization: User List with Order Counts + Spending, Postgres 15, 8.2s → target <100ms

A SQL query optimization produces systematic performance tuning. Your query has 2 major issues (correlated subqueries + missing composite index) producing 8.2s execution time. This plan rewrites query with LEFT JOIN + GROUP BY + adds 1 composite index, reducing to ~80-150ms. Will deploy production-safely with testing.

Query Plan Analysis

What planner shows:

• Sequential scan on orders for EACH row in users (correlated subquery pattern)
• For 100 users × 2 subqueries = 200 orders table scans
• Orders has 12M rows → 200 full scans = significant I/O
• Total cost dominated by subquery execution

Index usage:

• users: idx_users_created_at used for initial filtering
• orders: idx_orders_user_id NOT being used for subqueries (optimizer choosing scan for small-estimated result sets)

Performance Killers Identified

1. Correlated Subqueries (Primary Issue)

Pattern: each outer row triggers subquery execution.

Impact: 100 users × 2 subqueries = 200 queries against orders table internally.

Fix: rewrite as JOINs with aggregation.

2. Missing Composite Index

Current: idx_orders_user_id + separate idx_orders_status

Problem: for 'total spent on completed orders' query, optimizer can't efficiently use 2 separate indexes.

Fix: composite index on (user_id, status) with INCLUDE(total).

3. Missing Users Composite

Current: separate indexes on created_at + status

For query filter: WHERE status='active' AND created_at > date

Fix: composite index (status, created_at) with INCLUDE.

Optimization Plan

Priority 1: Rewrite query (biggest impact)

Priority 2: Add composite indexes (support rewrite)

Priority 3: ANALYZE tables (statistics freshness)

Index Recommendations

New Index 1: Users Composite

CREATE INDEX CONCURRENTLY idx_users_status_created_at
ON users (status, created_at)
INCLUDE (id, email, name);

Why: supports WHERE filter + covers SELECT columns = index-only scan possible.

Estimated size: ~200 MB for 2.5M rows.

New Index 2: Orders Composite

CREATE INDEX CONCURRENTLY idx_orders_user_status_total
ON orders (user_id, status)
INCLUDE (total);

Why: supports JOIN + aggregation + SUM calculation from index alone.

Estimated size: ~800 MB for 12M rows.

Drop Existing (Optional):

-- idx_orders_status can be dropped (rarely used standalone)
-- Verify first via pg_stat_user_indexes usage

Rewritten Query

SELECT 
  u.id, 
  u.email, 
  u.name,
  COALESCE(o.order_count, 0) as order_count,
  COALESCE(o.total_spent, 0) as total_spent
FROM users u
LEFT JOIN (
  SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) as total_spent
  FROM orders
  GROUP BY user_id
) o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
  AND u.status = 'active'
ORDER BY u.created_at DESC
LIMIT 100;

Changes:

• Correlated subqueries → single LEFT JOIN with aggregated subquery
• Single scan of orders with GROUP BY (uses index)
• CASE statement for conditional sum (faster than second subquery)
• COALESCE handles users with no orders

Alternative (even better if orders table is huge):

-- Only aggregate orders for matching users (pre-filter)
SELECT 
  u.id, u.email, u.name,
  COALESCE(o.order_count, 0) as order_count,
  COALESCE(o.total_spent, 0) as total_spent
FROM (
  SELECT id, email, name, created_at
  FROM users
  WHERE status = 'active' 
    AND created_at > '2024-01-01'
  ORDER BY created_at DESC
  LIMIT 100
) u
LEFT JOIN (
  SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) as total_spent
  FROM orders
  WHERE user_id IN (
    SELECT id FROM users 
    WHERE status = 'active' AND created_at > '2024-01-01'
    ORDER BY created_at DESC
    LIMIT 100
  )
  GROUP BY user_id
) o ON o.user_id = u.id
ORDER BY u.created_at DESC;

Pre-filters orders to only include relevant user_ids = much faster for 12M row orders table.

Testing Plan

Phase 1: Development Testing

1. Take production backup to staging

2. Add new indexes (CONCURRENTLY to avoid locking)

3. Run EXPLAIN ANALYZE on optimized query

4. Compare execution plans

5. Measure execution time at scale

Phase 2: Validation

1. Verify query results match original

2. Run 100 times to measure average + percentile performance

3. Check index usage via `pg_stat_user_indexes`

Phase 3: Production Deploy

1. Add indexes during low-traffic window (CONCURRENTLY still needs resources)

2. Deploy application query change

3. Monitor query performance

4. Monitor overall database load

5. Rollback plan: revert app, drop indexes if needed

Monitoring after deploy:

• P50/P95/P99 query execution times
• Overall database CPU/IO
• Index usage statistics
• Application error rates

Expected Performance

Current: 8.2 seconds.

After optimization:

• With composite indexes: ~200-400ms
• With query rewrite: ~80-150ms
• Combined with alternative pre-filter version: ~50-100ms

Improvement: ~100x faster.

Production impact:

• 500 executions/hour × 8 seconds saved = 1 hour of cumulative query time saved per hour
• Database CPU reduction: 15-25%
• User experience: dashboard loads noticeably faster

Key Takeaways

• Root cause: correlated subqueries (N+1 within single query). Rewrite as LEFT JOIN + aggregation = ~100x speedup.
• Add 2 composite indexes: users(status, created_at) INCLUDE (id, email, name) + orders(user_id, status) INCLUDE (total). Enables index-only scans.
• Alternative pre-filter version (subquery filtering orders by user_ids in filter) is optimal for huge orders table. ~50-100ms vs. 200-400ms base optimization.
• Deploy CONCURRENTLY to avoid table locks during index creation. Takes longer to build but production-safe.
• 100x speedup: 8.2 seconds → 80-150ms. At 500 executions/hour, significant database load reduction + dashboard user experience improvement.