SQL Query Optimization Tips: Advanced Techniques for Better Performance

1. Understanding Execution Plans

Execution plans are roadmaps that show how the database engine processes your queries. Understanding them is crucial for optimization.

-- Example of EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT c.customer_name,
       COUNT(o.order_id) as total_orders
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_name;

2. Advanced Indexing Strategies

Proper indexing is crucial for query performance, but it's important to understand the trade-offs and choose the right type of index.

-- B-tree index (default)
CREATE INDEX idx_customers_email 
ON customers(email);

-- Partial index
CREATE INDEX idx_orders_recent 
ON orders(order_date) 
WHERE order_date > 
    CURRENT_DATE - INTERVAL '30 days';

-- Covering index
CREATE INDEX idx_products_complete 
ON products(product_id, name, price, 
    category);

-- Multi-column index
CREATE INDEX idx_orders_composite 
ON orders(customer_id, order_date, 
    status);

3. Query Rewriting Techniques

-- Instead of correlated subquery
SELECT o.order_id,
       (SELECT COUNT(*) 
        FROM order_items oi 
        WHERE oi.order_id = o.order_id) 
           as item_count
FROM orders o;

-- Use JOIN instead
SELECT o.order_id, 
       COUNT(oi.item_id) as item_count
FROM orders o
LEFT JOIN order_items oi 
    ON o.order_id = oi.order_id
GROUP BY o.order_id;

WITH customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT *
    FROM customer_orders
    WHERE total_spent > 10000
)
SELECT 
    c.customer_name,
    hvc.order_count,
    hvc.total_spent
FROM customers c
JOIN high_value_customers hvc 
    ON c.customer_id = hvc.customer_id
ORDER BY hvc.total_spent DESC;

4. Performance Monitoring and Maintenance

5. Common Anti-Patterns to Avoid

6. Advanced Optimization Tips

-- Table partitioning by date
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    /* other columns */
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 
    PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') 
    TO ('2024-01-01');

CREATE TABLE orders_2024 
    PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') 
    TO ('2025-01-01');

7. Performance Testing Techniques

-- Using explain analyze with buffers
EXPLAIN (ANALYZE, BUFFERS) 
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as orders,
    SUM(o.total_amount) as revenue
FROM 
    customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
WHERE 
    o.order_date >= '2024-01-01'
GROUP BY 
    c.customer_id,
    c.customer_name
HAVING 
    COUNT(o.order_id) > 10;

Common Scenario Optimizations

-- Instead of OFFSET
SELECT *
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 10000; -- Slow with large offset

-- Use keyset pagination
SELECT *
FROM products
WHERE product_id > @last_seen_id
ORDER BY product_id
LIMIT 20;

-- Use window functions instead of subqueries
SELECT 
    department_id,
    employee_name,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) as dept_avg_salary
FROM employees;

Recommended Tools and Resources