SQL Query Optimization: Reading Plans and Fixing the Usual Suspects

When an endpoint gets slow, the database is the usual suspect, and usually rightly so. Adding hardware or caching everything is rarely the right first step. Most slow queries share a handful of root causes, and the database will tell you exactly which one applies if you ask. The examples below use PostgreSQL, but the ideas carry over to MySQL, SQL Server, and others.

Step 1: find the queries that matter

Don't optimize at random. Find the queries that consume the most total time, which is frequency × duration:

-- requires the pg_stat_statements extension
SELECT query,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

A 5ms query called 2 million times an hour often matters more than a 3-second report that runs once a day.

Step 2: read the plan

EXPLAIN shows the plan the optimizer intends to use. EXPLAIN ANALYZE actually runs the query and reports what happened:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4821
  AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=0.43..58.21 rows=20) (actual time=0.041..0.118 rows=20 loops=1)
  ->  Index Scan Backward using orders_customer_created_idx on orders o
        Index Cond: (customer_id = 4821)
        Filter: (status = 'paid')
        Rows Removed by Filter: 3
        Buffers: shared hit=24
Planning Time: 0.2 ms
Execution Time: 0.15 ms

What to look for:

• Node types. Seq Scan on a large table where you expected an index. Nested Loop with a huge outer side. Sort spilling to disk (external merge).
• Estimated vs. actual rows. If the planner expected 10 rows and got 500,000, it probably chose the wrong join strategy. Bad estimates cause many bad plans.
• Rows Removed by Filter. A large number means the index narrowed the search too little and the database threw most of the work away.
• Buffers. shared read means pages came from disk, and shared hit means they came from cache. High reads on a hot query indicate an I/O problem.
• loops. The actual time on an inner node is per loop. Multiply by loops to get the real cost.

Suspect 1: the N+1 query

The most common ORM-related problem. You load a list, then query once per item:

orders = Order.objects.filter(status="paid")[:100]   # 1 query
for o in orders:
    print(o.customer.name)                           # +100 queries

Each query is fast on its own, but 101 network round trips add up quickly. Fix it by fetching related data in bulk:

orders = (Order.objects
          .filter(status="paid")
          .select_related("customer")[:100])         # 1 query with a JOIN

Or in SQL, with a single IN lookup instead of a loop:

SELECT id, name FROM customers WHERE id = ANY($1::bigint[]);

How to detect it: log the number of queries per request in development, and fail tests when an endpoint exceeds a budget.

Suspect 2: non-sargable predicates

A predicate is sargable ("search argument-able") when the database can use an index to satisfy it. Wrapping the indexed column in a function or expression usually breaks that:

-- Can't use an index on created_at
WHERE DATE(created_at) = '2026-09-01'
WHERE created_at + INTERVAL '1 day' > now()
WHERE LOWER(email) = '[email protected]'
WHERE amount_cents / 100 > 50

Rewrite them so the bare column stands alone on one side:

WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'
WHERE created_at > now() - INTERVAL '1 day'
WHERE amount_cents > 5000

When you truly need the function, index the expression:

CREATE INDEX users_email_lower_idx ON users (LOWER(email));

Other sargability problems:

• Leading wildcards (LIKE '%smith') can't use a B-tree index. Consider trigram indexes or full-text search.
• Implicit type casts, such as comparing a varchar column to an integer parameter, can silently disable an index.
• OR across different columns often falls back to a scan. A UNION ALL of two indexed queries can be much faster.

Suspect 3: stale or misleading statistics

The planner chooses plans based on statistics about your data. When those are wrong, it makes bad choices.

• Refresh statistics after bulk loads with ANALYZE table_name;. Autovacuum does this eventually, but "eventually" may come after your nightly job has already run slowly.
• Correlated columns. The planner assumes columns are independent. If city = 'Bangkok' and country = 'TH' always occur together, it underestimates the rows. Extended statistics fix that:

CREATE STATISTICS addr_stats (dependencies) ON city, country FROM addresses;
ANALYZE addresses;

• Skewed columns. Raise the statistics target for columns with uneven distributions: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

Suspect 4: deep OFFSET pagination

SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 200000;

To return page 10,001, the database still reads and discards 200,000 rows. The deeper the page, the slower the query.

Use keyset (cursor) pagination instead, and resume from the last row you saw:

SELECT * FROM events
WHERE id > $last_seen_id
ORDER BY id
LIMIT 20;

With an index on the sort key, every page costs the same. For multi-column sort orders, compare row tuples: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC.

Suspect 5: fetching more than you need

• SELECT * pulls wide columns (JSON blobs, text bodies) that the endpoint never uses, and it prevents index-only scans. List the columns you need.
• Counting everything. SELECT COUNT(*) on a large table for a "1,234,567 results" label is expensive. Consider an estimate, or cap the count ("1,000+").
• Computing in the app what the DB could aggregate. Loading 100,000 rows to sum them in application code wastes network and memory. Use SUM/GROUP BY in the database.
• Missing LIMIT on queries that only need the first match. Use EXISTS instead of COUNT(*) > 0.

Suspect 6: long transactions and lock waits

Sometimes the query is fast but waits. Check for blocked sessions:

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND state <> 'idle';

Keep transactions short, never hold one open across a network call to another service, and do bulk updates in batches so you don't lock millions of rows at once.

An optimization routine

1. Rank queries by total time with pg_stat_statements.
2. Run EXPLAIN (ANALYZE, BUFFERS) on the top offender, using realistic parameters.
3. Compare estimated vs. actual rows. Fix statistics first if they're far apart.
4. Check for sargability and missing or mismatched indexes.
5. Change one thing, re-measure, and keep the change only if the numbers improve.
6. Add a regression check, such as a query-count budget or a slow-query alert.

Most slow queries turn out to be one of these few problems, and the execution plan usually shows which one.