Optimize Queries That Make Senior Developers Jealous

There’s a special moment in every developer’s career when they look at someone else’s SQL query and think, “Damn, that’s beautiful.” Not just functional, not just correct, but genuinely elegant—the kind of query that retrieves exactly what’s needed in milliseconds while your version churns through the database for seconds.

SQL query optimization separates junior developers from senior engineers. Anyone can write SQL that works. Few can write SQL that works brilliantly under pressure, scales gracefully with data growth, and makes DBAs smile instead of cry.

This isn’t about memorizing syntax or following generic best practices blindly. It’s about understanding how databases think, recognizing performance bottlenecks before they become problems, and writing queries that demonstrate mastery of your craft.

Let’s explore the advanced techniques that transform mediocre queries into performance masterpieces—the kind that make senior developers take notice and junior developers wonder how you did it.

Understanding the Query Optimizer’s Mind

Before optimizing queries, you need to understand your adversary and ally: the query optimizer. This is the database component that takes your SQL and decides how to actually execute it.

The Optimizer’s Job

When you submit a query, the optimizer analyzes multiple execution strategies, estimates their costs, and selects what it believes is the fastest approach. It considers available indexes, table statistics, join orders, and dozens of other factors to generate an execution plan.

The problem? The optimizer makes decisions based on statistics, not reality. When statistics are outdated, or when the optimizer lacks information about your data distribution, it makes poor choices. Your job is to write queries that guide the optimizer toward optimal decisions.

Reading Execution Plans

Execution plans reveal the truth about query performance. They show exactly how the database processes your query, not how you think it processes it.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2025-01-01';

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2025-01-01';

Look for these red flags in execution plans:

• Table scans on large tables (reading every row)
• High-cost operations consuming most of the query budget
• Implicit conversions causing index misses
• Excessive sorts or hash joins on large datasets
• Nested loops on tables with millions of rows

Understanding execution plans transforms query optimization from guesswork into science. You see exactly where time is spent and what needs fixing.

The Cardinal Sin: SELECT *

Let’s start with the most common mistake that immediately identifies amateur code.

Why SELECT * Destroys Performance

-- Amateur code
SELECT * FROM orders
WHERE customer_id = 12345;

-- Professional code
SELECT order_id, order_date, total, status
FROM orders
WHERE customer_id = 12345;

When you use SELECT *, you’re telling the database: “Give me everything, even though I probably only need three columns.” This creates multiple problems:

The database retrieves columns you don’t use, wasting I/O bandwidth. Network traffic increases unnecessarily. Memory buffers fill with useless data. Covering indexes can’t be utilized because you requested columns not in the index.

More importantly, SELECT * makes your code fragile. When someone adds a BLOB column to the table, your query suddenly retrieves megabytes of data per row instead of kilobytes. Your application slows to a crawl, and you’re trying to figure out why code that worked yesterday fails today.

The Covering Index Opportunity

Specifying exact columns enables covering indexes—indexes containing all columns needed by the query. The database can satisfy the entire query from the index without touching the main table.

-- This query can be covered by an index on (customer_id, order_date, total)
SELECT order_date, total
FROM orders
WHERE customer_id = 12345
ORDER BY order_date DESC;

When the index covers the query, performance improvements can be 10x or more because disk access drops dramatically.

Indexing Strategies That Actually Work

Indexes are databases’ most powerful optimization tool, yet most developers use them poorly. Understanding index strategy elevates your SQL game immediately.

Compound Indexes and Column Order

The order of columns in compound indexes matters enormously. The index can only be used efficiently from left to right.

-- Create index with frequently filtered columns first
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date, status);

-- This query uses the index efficiently
SELECT * FROM orders
WHERE customer_id = 12345
AND order_date >= '2025-01-01';

-- This query can't use the index efficiently (missing customer_id)
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
AND status = 'shipped';

Place the most selective columns first—columns that eliminate the most rows. If customer_id filters to 100 rows but status only filters to half the table, put customer_id first.

Include Columns for Covering

Modern databases support included columns in indexes—columns stored in the index but not part of the search structure.

-- SQL Server syntax
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total, status);

This index efficiently filters by customer_id and order_date, while also containing total and status for covering. It’s perfect for queries that filter on the first two columns and display the latter two.

Filtered Indexes for Specific Workloads

Filtered indexes only include rows meeting specific criteria, making them smaller and faster.

-- Index only pending orders (typically a small subset)
CREATE INDEX idx_pending_orders
ON orders (customer_id, order_date)
WHERE status = 'pending';

This index accelerates queries specifically looking for pending orders while remaining much smaller than an index on all orders.

When Not to Index

Over-indexing hurts performance because:

• Every INSERT, UPDATE, and DELETE must update all indexes
• Indexes consume disk space
• Too many indexes confuse the optimizer

Don’t index:

• Columns with very low cardinality (like boolean flags with even distribution)
• Columns never used in WHERE, JOIN, or ORDER BY clauses
• Small tables (under a few thousand rows)

Join Optimization: The Make-or-Break Skill

Poorly optimized joins cause more performance disasters than any other SQL feature. Mastering join optimization is non-negotiable for advanced developers.

Join Order Matters

The order you write joins isn’t necessarily the order they execute, but it influences optimizer decisions.

-- Inefficient - starts with large table
SELECT o.*, p.*
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2025-01-01';

-- Better - filters first, then joins
SELECT o.*, p.*
FROM orders o
WHERE o.order_date >= '2025-01-01'
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id;

The key principle: reduce dataset sizes as early as possible. Filter before joining whenever possible.

Join Types and Their Performance

Different join types have vastly different performance characteristics:

INNER JOIN – Fastest because it only returns matches. Use whenever you don’t need unmatched rows.

LEFT JOIN – More expensive because it must preserve all left-side rows. Don’t use unless you specifically need NULL values for unmatched rows.

-- Inefficient - using LEFT JOIN unnecessarily
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NOT NULL;

-- Efficient - INNER JOIN does the same thing
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Derived Table Optimization

When joining to aggregated data, use derived tables or CTEs to aggregate first, then join to smaller result sets.

-- Inefficient - joins before aggregating
SELECT c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Efficient - aggregates first, then joins
SELECT c.name, COALESCE(oc.order_count, 0) as order_count
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id
) oc ON c.id = oc.customer_id;

The second version aggregates orders into a small result set before joining, dramatically reducing the join’s cost.

WHERE Clause Optimization: Filter Like a Pro

How you write WHERE clauses determines whether indexes get used or ignored.

Sargable Predicates

“Sargable” (Search ARGument ABLE) predicates allow index usage. Non-sargable predicates force table scans.

-- Non-sargable - function on column prevents index use
SELECT * FROM orders
WHERE YEAR(order_date) = 2025;

-- Sargable - index on order_date can be used
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
AND order_date < '2026-01-01';

-- Non-sargable - calculation on column
SELECT * FROM products
WHERE price * 1.1 > 100;

-- Sargable - isolate the column
SELECT * FROM products
WHERE price > 100 / 1.1;

The rule: never apply functions or calculations to indexed columns in WHERE clauses. Instead, manipulate the comparison value.

OR vs IN vs UNION

OR clauses across different columns prevent efficient index usage. Consider alternatives.

-- Inefficient - OR across columns
SELECT * FROM orders
WHERE customer_id = 12345
OR status = 'pending';

-- Better - use UNION if appropriate
SELECT * FROM orders WHERE customer_id = 12345
UNION
SELECT * FROM orders WHERE status = 'pending';

-- Best - if looking for specific values on same column
SELECT * FROM orders
WHERE customer_id IN (12345, 67890, 11111);

IN clauses work efficiently with indexes when checking multiple values for the same column. OR across different columns often requires multiple index scans or table scans.

EXISTS vs IN: The Subtle Difference

For subqueries checking existence, EXISTS often outperforms IN, especially with large datasets.

-- Less efficient - IN
SELECT * FROM customers
WHERE id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_date >= '2025-01-01'
);

-- More efficient - EXISTS
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
    AND o.order_date >= '2025-01-01'
);

EXISTS stops searching as soon as it finds a match. IN evaluates the entire subquery and checks membership. For large subqueries with many matches, EXISTS wins significantly.

Advanced Techniques: The Secret Weapons

These techniques separate experts from practitioners.

Common Table Expressions (CTEs) for Clarity and Performance

CTEs organize complex queries and sometimes improve performance by materializing intermediate results.

-- Clear, maintainable, and potentially faster
WITH high_value_customers AS (
    SELECT customer_id, SUM(total) as lifetime_value
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
    HAVING SUM(total) > 10000
),
recent_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY customer_id
)
SELECT 
    c.name,
    hvc.lifetime_value,
    ro.order_count
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id
LEFT JOIN recent_orders ro ON c.id = ro.customer_id;

CTEs make complex logic readable and maintainable. Some databases optimize CTEs by materializing them once and reusing results.

Window Functions Instead of Self-Joins

Window functions often replace expensive self-joins with single-pass operations.

-- Inefficient - self-join to get previous order
SELECT 
    o1.order_id,
    o1.order_date,
    o2.order_date as previous_order_date
FROM orders o1
LEFT JOIN orders o2 ON o1.customer_id = o2.customer_id
    AND o2.order_date < o1.order_date
    AND o2.order_date = (
        SELECT MAX(order_date)
        FROM orders o3
        WHERE o3.customer_id = o1.customer_id
        AND o3.order_date < o1.order_date
    );

-- Efficient - window function
SELECT 
    order_id,
    order_date,
    LAG(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as previous_order_date
FROM orders;

The window function version reads the table once and processes everything in a single pass—dramatically faster than correlated subqueries.

Partitioning for Massive Tables

Table partitioning enables the database to ignore irrelevant data automatically.

-- Create partitioned table (PostgreSQL syntax)
CREATE TABLE orders (
    order_id BIGINT,
    order_date DATE,
    customer_id INTEGER,
    total DECIMAL(10,2)
) 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');

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

When you query for orders in 2025, the database automatically ignores the 2023 and 2024 partitions—a technique called partition pruning. For tables with hundreds of millions of rows, this provides order-of-magnitude performance improvements.

Batch Processing Instead of Loops

Never execute queries in loops if you can batch operations.

-- Terrible - loop in application code
for each customer_id in customer_list:
    execute("UPDATE customers SET last_login = NOW() WHERE id = ?", customer_id)

-- Excellent - single batch update
UPDATE customers
SET last_login = NOW()
WHERE id IN (1, 2, 3, 4, 5, ...);

-- Even better - use temporary table for large batches
CREATE TEMP TABLE customer_updates (customer_id INT);
INSERT INTO customer_updates VALUES (1), (2), (3), (4), (5)...;

UPDATE customers c
SET last_login = NOW()
FROM customer_updates cu
WHERE c.id = cu.customer_id;

Batch operations reduce network round-trips and transaction overhead by orders of magnitude.

Real-World Optimization Example

Let’s optimize an actual problematic query step by step.

Original Query (Slow)

SELECT 
    c.*,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) as order_count,
    (SELECT SUM(total) FROM orders o WHERE o.customer_id = c.id) as lifetime_value
FROM customers c
WHERE c.created_at >= '2024-01-01'
ORDER BY lifetime_value DESC
LIMIT 100;

Problems:

• Correlated subqueries execute for every customer row
• SELECT * retrieves unnecessary columns
• Sorting by derived column requires computing all values first

Optimized Version

WITH customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total) as lifetime_value
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.id,
    c.name,
    c.email,
    c.created_at,
    COALESCE(cs.order_count, 0) as order_count,
    COALESCE(cs.lifetime_value, 0) as lifetime_value
FROM customers c
LEFT JOIN customer_stats cs ON c.id = cs.customer_id
WHERE c.created_at >= '2024-01-01'
ORDER BY cs.lifetime_value DESC NULLS LAST
LIMIT 100;

-- Add these indexes
CREATE INDEX idx_customers_created ON customers(created_at);
CREATE INDEX idx_orders_customer ON orders(customer_id);

Improvements:

• Aggregates orders once instead of per customer
• Specifies exact columns needed
• Enables index usage for filtering and joins
• Processes in single pass instead of N+1 queries

Performance Gain: 50x to 100x faster depending on data volume.

Monitoring and Continuous Improvement

Query optimization isn’t one-and-done. It requires ongoing monitoring and refinement.

Query Performance Monitoring

Implement query logging to identify slow queries automatically:

-- PostgreSQL - log slow queries
-- Add to postgresql.conf
log_min_duration_statement = 1000  -- Log queries taking >1 second

-- SQL Server - use Query Store
ALTER DATABASE YourDB SET QUERY_STORE = ON;

Regular Index Maintenance

Indexes degrade over time as data changes. Schedule regular maintenance:

-- PostgreSQL
REINDEX TABLE orders;
ANALYZE orders;

-- SQL Server
ALTER INDEX ALL ON orders REBUILD;
UPDATE STATISTICS orders;

Statistics must stay current for the optimizer to make good decisions. Outdated statistics cause the optimizer to choose poor execution plans.

Load Testing

Test queries under realistic load conditions. A query that works with 1,000 rows might collapse with 1,000,000 rows or 100 concurrent users.

Use tools like Apache JMeter, K6, or database-specific tools to simulate production load. Find breaking points before users do.

The Mindset of Query Optimization

Technical skills matter, but mindset determines success.

Measure Before Optimizing

Never optimize without measuring first. Your intuition about what’s slow is often wrong. Let execution plans and query timing guide you.

Optimize the Right Queries

Don’t waste time optimizing queries that run once a day and complete in 2 seconds. Focus on:

• Frequently executed queries
• User-facing queries affecting experience
• Queries consuming significant resources
• Queries blocking other operations

Use the 80/20 rule: 20% of your queries likely consume 80% of database resources. Optimise those first.

Document Your Decisions

When you write a complex query optimisation, document why:

-- Using LEFT JOIN instead of NOT EXISTS here because customer table is small
-- and we need to display customers without orders.
-- Benchmarked: LEFT JOIN approach is 2x faster with current data distribution.
-- Re-evaluate if customer table grows beyond 100K rows.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;

Future developers (including you) will thank you for explaining non-obvious optimizations.

Balance Optimization with Maintainability

Sometimes, the most optimised query is hard to maintain. Strike a balance:

-- Slightly less optimal but much more maintainable
SELECT 
    customer_id,
    COUNT(*) as order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;

-- vs more optimal but harder to maintain
SELECT 
    customer_id,
    COUNT(*) as order_count
FROM orders
WHERE order_date >= DATE_TRUNC('day', NOW() - INTERVAL '30 days')
GROUP BY customer_id;

If both versions perform adequately, choose the clearer one.

Tools of the Trade

Professional developers use these tools to optimise queries:

Database-Specific Tools:

• SQL Server: SQL Server Management Studio, Query Store, Database Engine Tuning Advisor
• PostgreSQL: EXPLAIN ANALYZE, pg_stat_statements, pgAdmin
• MySQL: EXPLAIN, slow query log, MySQL Workbench
• Oracle: SQL Developer, AWR reports, SQL Tuning Advisor

Third-Party Tools:

• SolarWinds Database Performance Analyzer: Multi-database monitoring
• Redgate SQL Monitor: SQL Server performance
• Datadog: Cloud database monitoring
• New Relic: Application and database performance

AI-Powered Optimisation:

Modern tools use AI to suggest optimisations automatically. These tools analyse execution plans, table statistics, and query patterns to recommend indexes and query rewrites. While not perfect, they catch issues human developers miss and accelerate the optimisation process.

Conclusion: The Path to Mastery

Query optimisation is a journey, not a destination. Senior developers didn’t master it overnight—they learned through thousands of queries, countless mistakes, and endless curiosity about how databases work.

The techniques in this guide provide a solid foundation, but true mastery comes from practice. Take queries from your projects and optimise them. Study execution plans until reading them becomes second nature. Benchmark your changes to verify improvements.

Most importantly, develop an optimisation mindset. Question every query. Ask whether there’s a better way. Learn from queries written by experts. Over time, writing optimised SQL becomes instinctive.

When colleagues look at your queries and think “Damn, that’s beautiful,” you’ll know you’ve arrived. Until then, keep optimising, keep learning, and keep pushing the boundaries of what’s possible with SQL.

The database doesn’t care about your job title or years of experience. It only cares about the quality of your queries. Write queries that deserve respect, and the respect will follow.