How to Optimize a Slow SQL Query — A Practical Framework

There's a specific kind of frustration that comes with writing a SQL query you know is correct, hitting Run, and watching it sit there for 30 seconds, two minutes, or long enough that you start wondering whether you accidentally queried the entire warehouse.

The frustrating part is that slow SQL is rarely caused by one dramatic mistake. More often, the query is doing far more work than the final result actually requires: scanning columns you don't need, joining millions of rows before filtering them, sorting huge intermediate datasets, or forcing the database to calculate the same thing repeatedly.

The good news: SQL query optimization is a learnable process. You don't need to memorize 100 database-specific tricks. You need to learn how to look at a slow query and systematically ask:

What work is the database doing that it doesn't need to do?

This is the framework I use to troubleshoot slow SQL queries — and the same reasoning you should be able to explain in a SQL technical interview when someone asks, "How would you optimize this if the table were 100x larger?"

Who This Guide Is For

This isn't a guide to learning SELECT, WHERE, or JOIN.

This is for you if:

• You can write SQL that returns the correct answer, but some of your queries are painfully slow
• You're working with larger production tables and query performance is starting to matter
• You're preparing for senior SQL interviews where optimization questions come up as follow-ups
• You've heard advice like "add an index" or "use a CTE" but don't actually know when those changes help

The exact optimization techniques vary across PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, Redshift, and other systems.

But the reasoning framework transfers surprisingly well.

The Honest Diagnosis: Why SQL Queries Become Slow

Most slow SQL queries fall into a handful of buckets.

1. You're reading too much data. You need five columns from the last 30 days but you're scanning 40 columns across five years.

2. You're reducing the data too late. Millions of rows are being joined, sorted, or aggregated before your filters finally remove most of them.

3. Your joins are exploding the row count. A many-to-many join quietly turns 2 million rows into 50 million intermediate rows.

4. The database can't efficiently find the rows you need. This is where indexes, partitions, clustering, and table design start to matter.

5. You're doing expensive work you don't need. DISTINCT, ORDER BY, repeated calculations, unnecessary window functions, and nested transformations all have a cost.

The biggest mistake is trying random rewrites before figuring out which one you're dealing with.

Step 1: Start With the Execution Plan

If a query is genuinely slow, don't start rewriting it blindly.

Start by asking the database what it's doing.

In PostgreSQL, that usually means:

EXPLAIN
SELECT ...

And when you're ready to inspect the actual execution:

EXPLAIN ANALYZE
SELECT ...

The execution plan shows you how the database intends to retrieve and process your data.

Depending on the database, you'll see things like:

• Sequential scans
• Index scans
• Join strategies
• Sort operations
• Estimated row counts
• Actual row counts
• Execution time
• Data shuffling between stages

You don't need to become a database engine engineer to use an execution plan.

At first, you're looking for one thing:

Where does the query suddenly become expensive?

Maybe a table scan is reading 80 million rows.

Maybe a join creates 40 million intermediate records.

Maybe a sort is taking most of the execution time.

That's your starting point.

The habit to build is simple:

Diagnose first. Optimize second.

Step 2: Stop Selecting Data You Don't Need

The easiest optimization is often the one everyone ignores.

This:

SELECT *
FROM orders
WHERE order_date >= '2026-01-01';

asks the database to return every column.

If the table has 50 columns but your analysis only needs four, that's unnecessary work.

Write:

SELECT
    customer_id,
    order_date,
    product_id,
    revenue
FROM orders
WHERE order_date >= '2026-01-01';

instead.

This matters even more in columnar warehouses like BigQuery and Snowflake, where the amount of data scanned can directly affect both performance and cost.

The mental model:

Don't ask the database to read data you're going to throw away anyway.

That applies to rows too.

If you're analyzing the last 30 days, don't scan five years unless there's a reason.

Step 3: Filter Earlier

One of the most common optimization mistakes is allowing a query to process a huge dataset and only filtering near the end.

Consider:

WITH customer_orders AS (
    SELECT
        c.customer_id,
        o.order_id,
        o.order_date,
        o.revenue
    FROM customers c
    JOIN orders o
        ON c.customer_id = o.customer_id
)

SELECT *
FROM customer_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

You only care about the last 30 days.

But you've written the query in a way that conceptually joins the customer and order datasets before narrowing the order data.

A cleaner approach is:

WITH recent_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        revenue
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)

SELECT
    c.customer_id,
    r.order_id,
    r.order_date,
    r.revenue
FROM customers c
JOIN recent_orders r
    ON c.customer_id = r.customer_id;

Now you're making the intended reduction obvious.

Modern query optimizers can sometimes push predicates down automatically, so rewriting a query this way doesn't guarantee a performance difference in every engine.

But the principle still matters:

Reduce the dataset before expensive operations whenever possible.

Especially before:

• Large joins
• GROUP BY
• DISTINCT
• Window functions
• ORDER BY

If you start with 100 million rows and can safely reduce that to 2 million before a join, you've changed the problem completely.

Step 4: Check Whether Your Join Is Multiplying Rows

This one causes more performance problems than people realize.

Imagine these tables:

customers
1 row per customer

orders
multiple rows per customer

order_items
multiple rows per order

Joining them is perfectly valid.

But now imagine you also join another table with multiple rows per customer.

You've potentially created a many-to-many relationship.

The query may still run.

It may even return numbers that look reasonable.

But the database could be processing an enormous intermediate dataset.

Before joining two large tables, always ask:

What is the grain of each table?

In plain English:

What does one row represent?

For example:

• customers → one row per customer
• orders → one row per order
• order_items → one row per product per order
• sessions → one row per user session
• events → one row per user event

Once you understand the grain, ask what the join does to it.

A useful diagnostic is checking row counts before and after the join.

If:

Table A = 2 million rows
Table B = 5 million rows

and your join suddenly produces:

47 million rows

before eventually returning 100,000 rows, that's where I'd investigate first.

Step 5: Aggregate Before Joining When It Makes Sense

Sometimes the easiest way to reduce join cost is to shrink one side first.

Suppose you have a huge order_items table:

SELECT
    c.customer_id,
    SUM(oi.quantity * oi.price) AS revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
GROUP BY c.customer_id;

If order_items contains hundreds of millions of rows, you may not need to carry every line item through every stage.

You could aggregate to order level first:

WITH order_revenue AS (
    SELECT
        order_id,
        SUM(quantity * price) AS revenue
    FROM order_items
    GROUP BY order_id
)

SELECT
    c.customer_id,
    SUM(r.revenue) AS revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_revenue r
    ON o.order_id = r.order_id
GROUP BY c.customer_id;

You've changed the grain of order_items from:

one row per product per order

to:

one row per order

before the next join.

That can dramatically reduce the amount of data moving through the rest of the query.

The rule isn't "always aggregate first."

The rule is:

Don't carry detail through the query if the final answer doesn't need that detail.

Step 6: Understand When Indexes Actually Help

"Add an index" is probably the most overused SQL optimization advice on the internet.

Indexes can absolutely make queries dramatically faster.

But only when the access pattern makes sense.

Suppose you repeatedly run:

SELECT
    customer_id,
    email,
    signup_date
FROM customers
WHERE email = 'customer@example.com';

If customers contains millions of records and email is appropriately indexed, the database may be able to find that customer without scanning the entire table.

That's useful.

Now consider:

SELECT
    customer_id,
    email,
    signup_date
FROM customers
WHERE country IS NOT NULL;

If almost every record matches, an index may provide little benefit because the database needs a huge portion of the table anyway.

Indexes also aren't free.

They:

• consume storage
• must be maintained during writes
• can slow inserts and updates
• add complexity if you create too many

So don't think:

Slow query → index.

Think:

Is the database repeatedly searching or joining on a selective column where faster lookup would materially reduce the work?

Then inspect the execution plan and verify.

Step 7: Watch Functions in Your Filters

A subtle performance problem shows up when you transform the column you're trying to filter.

For example:

SELECT
    order_id,
    customer_id,
    order_date
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

You've asked the database to evaluate a function against order_date.

Depending on your database, table design, indexes, partitions, and optimizer, that can make it harder to take advantage of efficient access paths.

A cleaner filter is often:

SELECT
    order_id,
    customer_id,
    order_date
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

Same business question.

Different way of expressing the filter.

You'll see similar problems with things like:

LOWER(email)

CAST(timestamp_col AS DATE)

DATE(created_at)

inside filtering conditions.

This doesn't mean functions are bad.

It means you should understand whether transforming the filtered column prevents the database from efficiently eliminating data.

Step 8: Stop Using DISTINCT as a Band-Aid

You write the join.

The output suddenly has duplicates.

You panic.

Then:

SELECT DISTINCT ...

Problem solved.

Except sometimes it isn't.

DISTINCT forces the database to identify and remove duplicate rows, which can involve significant sorting, hashing, or data movement on large datasets.

More importantly, the duplicate rows may be telling you something important:

Your join logic might be wrong.

Maybe you expected:

1 customer → 1 account

but the data is actually:

1 customer → many accounts

Or maybe you're joining at the wrong grain.

Before adding DISTINCT, ask:

Why do duplicates exist?

If duplicates are logically expected and you genuinely need unique output, use it.

If you're using it because you don't understand what the join produced, fix the join first.

Step 9: Be Careful With ORDER BY

Sorting is expensive.

Especially across millions or billions of rows.

This query:

SELECT
    customer_id,
    revenue
FROM customer_revenue
ORDER BY revenue DESC;

asks the database to sort the entire result.

If the business question is:

Who are the top 10 customers by revenue?

then write:

SELECT
    customer_id,
    revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10;

And more importantly, don't add ORDER BY to intermediate queries just because sorted output feels easier to look at.

SQL tables don't have meaningful inherent ordering.

If the final consumer doesn't need the data sorted, don't sort it.

Step 10: Use Partitions Properly

If you're working in an analytical warehouse, this can be one of the biggest optimization wins.

Imagine an events table containing:

2021
2022
2023
2024
2025
2026

and your analysis needs:

the last 7 days

Scanning the entire table is wasteful.

If the dataset is partitioned by event_date, you want the database to eliminate irrelevant partitions before processing the query.

Conceptually:

SELECT
    user_id,
    event_name,
    event_date
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '7 days';

instead of writing filters in a way that prevents efficient partition pruning.

This is especially important in usage-based warehouses because inefficient SQL can hurt you twice:

Your query runs slower and costs more money.

That's why good analytics engineers think about query cost as well as correctness.

The Query Optimization Framework I Actually Use

When someone hands me a slow SQL query, I don't immediately rewrite it.

I work through this order.

1. Define the output grain

Ask:

What should one row represent?

If you can't answer that clearly, stop.

Your optimization problem may actually be a data-modeling problem.

2. Check how much data is being scanned

Look at:

• Rows
• Columns
• Date ranges
• Partitions
• Number of tables

If you're reading 2 TB to return 3,000 rows, that's a clue.

3. Inspect the execution plan

Find the expensive stage.

Don't optimize the prettiest piece of SQL.

Optimize the expensive piece.

4. Check the joins

Ask:

• What is the grain on both sides?
• Is this one-to-one?
• One-to-many?
• Many-to-many?
• Did the row count explode?

5. Reduce the dataset

Look for opportunities to:

• filter earlier
• select fewer columns
• aggregate before joining
• avoid unnecessary detail
• prune partitions

6. Remove unnecessary expensive operations

Look for:

DISTINCT

ORDER BY

large window functions, repeated transformations, and nested logic that doesn't contribute to the final answer.

7. Evaluate indexing or physical table design

If you're repeatedly querying the same access pattern, now look at:

• indexes
• partitioning
• clustering
• sort keys
• distribution strategy
• materialized views

depending on the database you're using.

8. Run it again

And compare.

Optimization isn't:

"This query looks cleaner now."

Optimization is:

Before: 28 seconds
After: 4 seconds

or:

Before: 1.8 TB scanned
After: 120 GB scanned

You need evidence that your change actually helped.

Before and After: A Slow Query Example

Let's say a product manager asks:

Which customers generated more than $1,000 in revenue during the last 30 days?

You write:

SELECT DISTINCT
    c.customer_id,
    c.email,
    SUM(oi.quantity * oi.price) OVER (
        PARTITION BY c.customer_id
    ) AS total_revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY total_revenue DESC;

It works.

But look at what's happening.

You're:

• joining every qualifying order item
• calculating a window function across customer partitions
• generating repeated customer rows
• using DISTINCT to remove them
• sorting everything afterward

You only need one row per customer.

So start there.

SELECT
    c.customer_id,
    c.email,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY
    c.customer_id,
    c.email
HAVING SUM(oi.quantity * oi.price) > 1000
ORDER BY total_revenue DESC;

The second query matches the intended output grain directly:

one row per customer

No window function.

No duplicate customer rows.

No DISTINCT cleanup afterward.

This is an important optimization lesson:

The simplest SQL structure that matches the required grain is often the best place to start.

The Top Seven SQL Optimization Mistakes

After reviewing enough production SQL and interview solutions, the same mistakes show up repeatedly.

Optimizing before measuring. You spend 30 minutes replacing CTEs with subqueries when the expensive part was a join scanning 500 million rows.

Using SELECT *. Fine for quick exploration. Less fine as a default habit in production analytics.

Filtering too late. You're carrying rows through joins and aggregations that you eventually throw away.

Ignoring table grain. This is how accidental many-to-many joins happen.

Using DISTINCT to fix duplicates. Sometimes correct. Frequently a sign that the underlying join deserves another look.

Assuming shorter SQL is faster SQL. A five-line query can be horribly expensive. A 30-line query can be extremely efficient. Query length is not a performance metric.

Assuming the database will optimize everything for you. Modern optimizers are incredibly good. They are not magic. Poor data modeling, unnecessary scans, bad joins, and avoidable transformations can still hurt.

How SQL Optimization Shows Up in Technical Interviews

You don't always get a question that literally says:

Optimize this slow SQL query.

More often, you solve the original problem and then the interviewer asks:

"What if this table contained 2 billion rows?"

That's the optimization question.

A strong answer isn't:

"I'd add an index."

A stronger answer sounds more like:

"First I'd inspect the execution plan to identify the bottleneck. I'd check whether we're scanning unnecessary columns or partitions, verify that the joins aren't creating a much larger intermediate dataset, and reduce the data before expensive aggregations where possible. If this lookup pattern is frequent, I'd then evaluate whether the relevant filtering or join columns should be indexed or whether the table's partitioning strategy should change."

That's senior-level reasoning.

You aren't guessing one optimization.

You're showing that you know how to diagnose the system.

The Quiet Difference Between Intermediate and Senior SQL

Intermediate SQL developers tend to ask:

Does the query work?

Senior SQL developers ask:

Does it work, is it correct at the intended grain, and what will happen when this dataset becomes 100x larger?

That's the shift.

At small scale, almost anything works.

A bad join against 10,000 rows is annoying.

A bad join against 10 billion rows is an incident.

The SQL syntax didn't change.

The consequences did.

That is why query optimization becomes increasingly important as you move into senior analyst, analytics engineering, data science, and data engineering roles.

SQL Query Optimization Checklist

Before calling a slow query "optimized," check these:

• Are you selecting only the columns you actually need?
• Are you filtering the smallest possible date range?
• Are partition filters being used correctly?
• Do you know the grain of every table involved?
• Did any join unexpectedly multiply the number of rows?
• Can you aggregate a large table before joining it?
• Are you using DISTINCT because you need it or because the join produced duplicates?
• Is an ORDER BY actually required?
• Are expensive window functions necessary?
• Are you repeatedly applying functions to filter columns?
• Have you inspected the execution plan?
• Would an index or different physical table design help this recurring access pattern?
• Did you measure performance before and after the change?

If you can't answer those questions yet, you're probably not done optimizing.

What To Practice

The best way to get better at SQL optimization isn't memorizing a list of performance tips.

Take real business problems and solve them twice.

First, get the correct answer.

Then ask:

How would I rewrite this if the dataset were 100x larger?

Practice on schemas involving:

• Product events
• User activity
• Marketing campaigns
• Subscription revenue
• Customer transactions
• SaaS metrics
• Finance data

Those datasets force you to think about grain, aggregation, joins, filtering, and scale in ways that toy Employees and Departments tables don't.

And if you're preparing for interviews, don't stop after submitting a correct query.

Explain out loud:

• why you chose that join
• what the output grain is
• what could make the query slow
• what you'd inspect first at production scale
• how you'd validate whether your optimization worked

That's the muscle senior SQL interviews are actually testing.

The Mindset Shift

The biggest SQL optimization reframe is this:

Stop looking only at the SQL you're writing.

Start imagining the work the database has to perform underneath it.

Every query is asking the engine to do something:

Read this data
Filter these rows
Match these records
Build this intermediate dataset
Aggregate these values
Sort this output
Return the result

Your job is to make sure it isn't doing unnecessary work along the way.

So the next time a query is slow, don't immediately start rearranging CTEs or Googling whether EXISTS is faster than IN.

Start with the fundamentals.

Define the grain. Inspect the plan. Reduce the data. Check the joins. Remove unnecessary work. Measure again.

That's SQL optimization.

And once you start thinking this way, you don't just write queries that work.

You write queries that are built to keep working when the data gets bigger.