Why SQL Queries Are Slow (And How to Fix Them)

How to actually diagnose a slow query

Before changing anything, find out what the database is actually doing. Guessing wastes time and often makes things worse — adding an index that never gets used, for instance, just slows down every future write. The one command that tells you the truth is EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

The output shows every operation the optimizer chose, how many rows it estimated versus how many it actually touched, and how long each step took. If you see Seq Scan (or Table Scan) on a table with millions of rows, or a huge gap between estimated and actual row counts, you've found your bottleneck before making a single change. We cover this in full depth in EXPLAIN Query Plan – Beginner to Advanced.

Cause 1: Missing or unused indexes

This is the single most common reason a query is slow. Without an index on orders.status, the database has no way to jump directly to the pending rows — it has to read every row in the table and check the condition one by one. That's a full table scan, and its cost grows linearly with table size: twice the rows, roughly twice the time.

CREATE INDEX idx_orders_status ON orders (status);

With the index in place, the database can seek directly to the block of rows where status = 'pending' instead of reading the whole table. We go much deeper on how indexes work, B-trees, and composite indexes in Indexes Explained With Real Examples, and on exactly when the optimizer chooses to use one in Index Scan vs Table Scan.

Cause 2: Non-sargable WHERE clauses

An index existing isn't enough — the query has to be written so the optimizer can actually use it. A predicate is sargable ("Search ARGument ABLE") when the database can evaluate it directly against the stored, indexed values. Wrapping the column in a function breaks that:

-- Non-sargable: index on order_date can't be used
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- Sargable: rewritten as a range, index is used
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

The first version has to compute YEAR(order_date) for every single row before it can compare it to 2026, which forces a full scan regardless of any index on order_date. The second version expresses the same condition as a plain range comparison on the raw column, so the optimizer can use a standard index seek. Leading wildcards (LIKE '%term'), implicit type casts, and OR across different columns cause the same problem.

Cause 3: SELECT * and over-fetching

SELECT * costs more than it looks like it should, for two separate reasons. First, it moves more bytes — through disk I/O, the buffer cache, and the network — than a query that names only the columns it needs. Second, and often more damaging, it can silently disqualify a covering index (an index that contains every column the query needs). If an index covers status, created_at, and total, but you SELECT *, the database has to leave the index and make an extra trip back to the full row for every column that isn't in it.

Cause 4: Bad join order and join explosion

With more than two tables, the order tables are joined in changes the size of every intermediate result — and a bad order can multiply row counts before a single filter is applied. A join that fans out one row into many (a classic case: joining a customer to every one of their orders, then joining that to every line item) can turn a query that should touch a few hundred rows into one that materializes millions.

The optimizer usually gets join order right when statistics are accurate, but it can only choose from the plan you gave it — putting a highly selective filter in a subquery or CTE that runs first, before the big join, is often the fix when the optimizer can't reorder around a complex query shape on its own.

Cause 5: Stale statistics

The optimizer never looks at your actual live data mid-query — every decision is based on statistics gathered earlier (row counts, distinct value counts, data distribution) via ANALYZE, UPDATE STATISTICS, or an automatic background job. If a table grew from 10,000 rows to 5 million since statistics were last refreshed, every cost estimate built on it is wrong, and the optimizer can pick a plan that made sense for the old, smaller table but is badly wrong for the current one.

Cause 6: Running a query per row (N+1)

This one isn't about the SQL itself — it's about how application code calls it. Fetching a list of 200 orders, then looping through them in application code and running a separate SELECT per order to get its line items, means 201 round trips to the database instead of one or two. Each round trip pays network latency on top of the query cost, and that latency, multiplied by hundreds or thousands of rows, is usually the actual bottleneck — not the individual queries themselves.

-- Instead of one query per order in a loop, fetch everything in one round trip
SELECT o.id AS order_id, li.sku, li.qty
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.customer_id = 4471;

Before/after: one query, six fixes

Here's a real-shaped example on a 2.4 million row orders table. The query looks for a customer's pending orders from this year, newest first. The "before" version has no index on status, wraps order_date in a function, and selects every column; the "after" version fixes all three.

SELECT * FROM orders
WHERE status = 'pending'
  AND YEAR(order_date) = 2026
  AND customer_id = 4471
ORDER BY order_date DESC;

Seq Scan on orders  (rows=2,401,880)
  Filter: status = 'pending'
    AND YEAR(order_date) = 2026
    AND customer_id = 4471
Planning Time: 0.4 ms

SELECT id, total, order_date FROM orders
WHERE customer_id = 4471
  AND status = 'pending'
  AND order_date >= '2026-01-01'
ORDER BY order_date DESC;

Index Scan using idx_orders_cust_status_date
  (rows=6)
  Index Cond: customer_id = 4471
    AND status = 'pending'
    AND order_date >= '2026-01-01'
Planning Time: 0.3 ms

~216x faster same result set, same table, three fixes: sargable date range, no SELECT *, composite index on (customer_id, status, order_date)