Skip to content
Backend

SQL Query Optimization: From Slow Queries to Fast Results

Practical techniques for identifying and fixing slow SQL queries using EXPLAIN plans, indexing strategies, and query restructuring patterns.

4 min read
SQL EXPLAIN output showing query execution plan with sequential and index scans

Every slow application has a slow query hiding somewhere. The application code might be well-optimized, the server might have plenty of resources, but one poorly-written SQL query doing a full table scan on a million rows will bottleneck the entire system. The query worked fine with 1,000 rows in development. It collapses at production scale.

Finding and fixing these queries is one of the highest-leverage skills a backend engineer can have. The pattern is almost always the same: read the EXPLAIN plan, add the right index, restructure the query.

Reading EXPLAIN Plans

EXPLAIN ANALYZE is the single most important tool for SQL performance. It shows how the database actually executes your query — not how you think it executes.

sqlsql
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2020-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

Key things to look for in the output:

  • Seq Scan — Full table scan. Fine for small tables, disastrous for large ones.
  • Index Scan / Index Only Scan — Using an index. This is what you want.
  • Nested Loop — Joining by iterating rows. Efficient for small result sets.
  • Hash Join — Building a hash table for the join. Better for large data sets.
  • Sort — Explicit sorting step. May spill to disk if work_mem is too small.
  • Actual time — Real execution time per node. The biggest number is your bottleneck.
sqlsql
-- ❌ Seq Scan on a million-row table — every row is read
-- Seq Scan on orders (cost=0.00..35421.00 rows=1000000)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 990000
-- Planning Time: 0.2ms
-- Execution Time: 2340ms
 
-- ✅ After adding an index — only relevant rows are read
-- Index Scan using idx_orders_status on orders (cost=0.42..825.00 rows=10000)
--   Index Cond: (status = 'pending')
-- Planning Time: 0.3ms
-- Execution Time: 12ms

Indexing Strategies

An index is a sorted data structure that lets the database find rows without scanning the entire table. The right index turns a 2-second query into a 10ms one. The wrong index wastes disk space and slows down writes.

sqlsql
-- Single column index — for exact match and range queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at);
 
-- Composite index — column order matters
-- This index serves: WHERE status = X AND created_at > Y
-- It does NOT efficiently serve: WHERE created_at > Y (alone)
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);
 
-- Partial index — indexes only rows matching a condition
-- Much smaller than a full index when most data is filtered out
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Index Selection Rules

sqlsql
-- ❌ Index on a low-cardinality column — not useful
-- A boolean column has 2 values. The index doesn't help.
CREATE INDEX idx_users_active ON users (is_active);
 
-- ✅ Partial index on the rare case — small and effective
CREATE INDEX idx_users_inactive
  ON users (email)
  WHERE is_active = false;
-- Only indexes the 2% of users who are inactive

Composite index column order follows the "equality first, range second" rule:

sqlsql
-- Query: WHERE status = 'shipped' AND created_at > '2020-06-01'
-- ❌ Range column first — index partially used
CREATE INDEX idx_wrong ON orders (created_at, status);
 
-- ✅ Equality column first — index fully utilized
CREATE INDEX idx_right ON orders (status, created_at);

Query Restructuring

Sometimes the fix is not an index but a different query. Common patterns that cause performance problems have well-known alternatives.

Avoid SELECT *

sqlsql
-- ❌ Fetches all 30 columns — most are unused
SELECT * FROM users WHERE department = 'engineering';
 
-- ✅ Only fetch what you need — enables index-only scans
SELECT id, name, email FROM users WHERE department = 'engineering';

When the query only needs columns that are in the index, PostgreSQL can satisfy the query entirely from the index without touching the table — an "index-only scan."

Subquery vs JOIN

sqlsql
-- ❌ Correlated subquery — runs once per row in the outer query
SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
 
-- ✅ JOIN with aggregation — single pass
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

EXISTS vs IN for Large Sets

sqlsql
-- ❌ IN with subquery — materializes entire result set
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
 
-- ✅ EXISTS — stops at first match per row
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

N+1 Query Detection

The N+1 problem is the most common performance bug in applications using ORMs. One query loads N parent rows, then N separate queries load each child.

tstypescript
// ❌ N+1 — 1 query for users + N queries for orders
const users = await db.query('SELECT * FROM users LIMIT 100');
for (const user of users) {
  user.orders = await db.query(
    'SELECT * FROM orders WHERE user_id = $1',
    [user.id]
  );
}
// Total: 101 queries
 
// ✅ Single query with JOIN
const usersWithOrders = await db.query(`
  SELECT u.*, json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id
  LIMIT 100
`);
// Total: 1 query

In ORMs, use eager loading to prevent N+1:

tstypescript
// ❌ Prisma — lazy loading triggers N+1
const users = await prisma.user.findMany();
// Accessing users[0].orders triggers another query
 
// ✅ Prisma — include prevents N+1
const users = await prisma.user.findMany({
  include: { orders: true },
});

Pagination Done Right

Offset-based pagination degrades linearly. Page 1000 scans and discards 999 pages of results.

sqlsql
-- ❌ Offset pagination — gets slower with higher pages
-- Page 1000 scans 100,000 rows to return 100
SELECT * FROM orders ORDER BY created_at DESC
OFFSET 99900 LIMIT 100;
 
-- ✅ Cursor-based pagination — constant performance
-- Uses the last seen value as the starting point
SELECT * FROM orders
WHERE created_at < '2020-12-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 100;

Cursor-based pagination requires an indexed, unique sort key. For non-unique columns, combine with the primary key:

sqlsql
-- Cursor for non-unique sort column
SELECT * FROM orders
WHERE (created_at, id) < ('2020-12-15T10:30:00Z', 'abc-123')
ORDER BY created_at DESC, id DESC
LIMIT 100;

Monitoring Slow Queries

Do not wait for users to report slowness. Proactively monitor query performance.

sqlsql
-- PostgreSQL: enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '200';
-- Logs every query taking longer than 200ms
 
-- Find the heaviest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

The pg_stat_statements extension is essential. It tracks execution statistics for every query pattern, so you can find the queries consuming the most total database time — even if individual executions are fast.

Key Takeaways

  1. Run EXPLAIN ANALYZE first — never guess why a query is slow when the database will tell you
  2. Index equality columns before range columns — composite index column order directly impacts usability
  3. Partial indexes save space and improve performance — index only the rows you actually query
  4. Eliminate N+1 queries — use JOINs or eager loading instead of per-row subqueries
  5. Use cursor-based pagination — offset pagination degrades at scale, cursors stay constant
  6. Monitor with pg_stat_statements — find the queries consuming the most total time before they become problems
Wilfredo Rujel

Wilfredo Rujel

Full Stack Software Engineer

Share this postX

Related Posts