SQL Optimization: Improving Query Performance by 10×

A critical reporting query was taking over 45 seconds to execute, blocking users. Through profiling and systematic optimisation I reduced it to 4.2 seconds — a 10.8× improvement.

The Problem Query

-- Slow (45 seconds)
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN customers c  ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id   = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_id, o.order_date, c.customer_name
ORDER BY o.order_date DESC
LIMIT 1000;

The Optimised Query

-- Create indexes first
CREATE INDEX idx_orders_date      ON orders(order_date);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_customers_id     ON customers(customer_id);

-- Pre-aggregate totals in a materialized view
CREATE MATERIALIZED VIEW order_totals AS
SELECT order_id, SUM(quantity * price) AS total_amount
FROM order_items GROUP BY order_id;

-- Rewritten query (4.2 seconds)
SELECT o.order_id, o.order_date, c.customer_name, mv.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_totals mv ON o.order_id = mv.order_id
WHERE o.order_date >= '2023-01-01'
ORDER BY o.order_date DESC
LIMIT 1000;

Before vs After

Execution time (s)

45.2

After optimisation (s)

4.2

Metric	Before	After	Improvement
Execution time	45.2 s	4.2 s	10.8× faster
Rows scanned	2.5 M	125 K	20× reduction
Index usage	0	3	Full coverage
CPU usage	85%	12%	7× reduction

Key Optimisations

1. Composite indexes on join columns eliminate full-table scans
2. Materialized view pre-aggregates order_items so the main query skips the SUM join
3. Query rewriting removes unnecessary GROUP BY columns from the outer query
4. Table partitioning by order_date keeps the WHERE filter sargable