SQL Optimization Guide: EXPLAIN Plans, Indexing, Query Tuning, Partitioning & Database Scaling

SQL performance can make or break an application. A single slow query can cascade into timeouts, lock contention, and degraded user experience. This guide covers 13 essential SQL optimization topics — from reading EXPLAIN plans and designing indexes to partitioning, connection pooling, and database scaling strategies. Every section includes a practical code example you can adapt to PostgreSQL, MySQL, or your database of choice.

1. EXPLAIN & Query Plans

Every optimization starts with understanding how the database executes your query. EXPLAIN shows the query plan chosen by the optimizer, including join strategies, index usage, and cost estimates. EXPLAIN ANALYZE actually runs the query and reports real execution times alongside estimates.

Key things to look for: Seq Scan on large tables (missing index), high cost estimates, large differences between estimated and actual rows (stale statistics), nested loops on large result sets, and Sort operations without supporting indexes.

In PostgreSQL, EXPLAIN output contains multiple node types: Seq Scan (full table scan), Index Scan, Bitmap Heap Scan, Nested Loop, Hash Join, and Merge Join. Each node shows estimated cost (startup..total), estimated rows, and width. EXPLAIN ANALYZE adds actual time and rows, helping detect estimation errors.

-- Run EXPLAIN ANALYZE to see actual execution
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;

-- Key output fields to examine:
-- Seq Scan vs Index Scan (is an index being used?)
-- actual time=0.02..12.45 (startup..total time)
-- rows=1000 (estimated) vs actual rows=847
-- Sort Method: quicksort Memory: 128kB

2. Index Types & Strategies

Indexes are the single most impactful optimization tool. Different index types serve different query patterns. B-tree indexes handle equality and range queries. Hash indexes are optimized for equality-only lookups. GIN indexes support full-text search and array containment. GiST indexes handle geometric and range type queries. Partial indexes reduce index size by only indexing rows that match a condition. Covering indexes include all columns needed by a query, enabling index-only scans.

Choosing the right index type is critical. B-tree covers 90% of use cases. Use GIN indexes for JSONB columns. Use GiST indexes for geospatial data. Partial indexes significantly reduce index size and maintenance overhead by only indexing hot data. Remember that every index slows down INSERT/UPDATE operations — only create indexes that your queries actually need.

-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users (email);

-- Partial index (only active users)
CREATE INDEX idx_active_users ON users (email)
  WHERE status = 'active';

-- GIN index for full-text search
CREATE INDEX idx_posts_search ON posts
  USING gin(to_tsvector('english', title || ' ' || body));

-- Covering index (includes columns for index-only scan)
CREATE INDEX idx_orders_covering ON orders (user_id)
  INCLUDE (total_amount, created_at);

-- Hash index (equality only, slightly faster)
CREATE INDEX idx_sessions_token ON sessions
  USING hash (session_token);

3. Query Optimization

Writing efficient SQL requires understanding how the optimizer processes WHERE clauses, JOINs, and subqueries. Avoid functions on indexed columns in WHERE clauses as they prevent index usage. Prefer EXISTS over IN for correlated subqueries. Use CTEs for readability but be aware that in some databases they act as optimization fences.

JOIN order matters — the optimizer usually reorders joins, but hints or explicit ordering may be needed for complex queries. Always filter early to reduce intermediate result set sizes.

Another common pitfall is implicit type casting. When the column type in a WHERE clause does not match the comparison value (e.g., comparing a string column to an integer), the database may apply a cast function to the entire column, invalidating the index. Always ensure comparison value types match the column type.

-- BAD: function on indexed column prevents index use
SELECT * FROM users
WHERE LOWER(email) = 'user@example.com';

-- GOOD: use expression index or store normalized
CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

-- BAD: SELECT * fetches unnecessary columns
SELECT * FROM orders WHERE user_id = 42;

-- GOOD: select only needed columns
SELECT id, total_amount, created_at
FROM orders WHERE user_id = 42;

-- Use EXISTS instead of IN for correlated subquery
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

4. Index Design Patterns

Effective index design requires understanding your query workload. Composite indexes must follow the left-prefix rule: the index on (a, b, c) can satisfy queries on (a), (a, b), or (a, b, c), but not (b, c) alone. Covering indexes include all SELECT columns, eliminating table lookups entirely. Index-only scans are the fastest possible access path.

The rule of thumb for composite index design (ESR rule): put Equality columns first (exact matches), then Sort columns (ORDER BY), then Range columns (range scans). This ordering lets the index satisfy as many query operations as possible without extra sorting or table lookups. Use the pg_stat_user_indexes view to monitor index usage and drop unused indexes.

-- Composite index: equality first, range second
-- Query: WHERE status = 'active' AND created_at > ?
CREATE INDEX idx_status_created
  ON orders (status, created_at);

-- This index supports ORDER BY as well
-- WHERE status = 'active' ORDER BY created_at DESC
CREATE INDEX idx_status_created_desc
  ON orders (status, created_at DESC);

-- Covering index for index-only scans
-- Query: SELECT email, name FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering
  ON users (status) INCLUDE (email, name);

-- Verify index-only scan in EXPLAIN output:
-- "Index Only Scan using idx_users_status_covering"

5. N+1 Query Problem

The N+1 problem occurs when code fetches a list of N records, then executes one additional query per record to load related data. This results in N+1 total queries instead of 1 or 2. It is the most common ORM-related performance issue and can turn a 5ms page load into a 5-second one.

Detection: Enable query logging and look for repeated identical queries with different parameters. Fix with eager loading (JOIN FETCH), batch queries (WHERE id IN (...)), or dataloader patterns.

In GraphQL applications, the N+1 problem is especially severe because resolvers execute independently per field. Facebook's DataLoader pattern is the standard solution: it collects all load requests within a single event loop tick, then satisfies them all with one batch query. DataLoader implementations exist for Node.js, Python, Ruby, and Java.

-- N+1 Problem: 1 query for users + N queries for orders
-- Query 1: SELECT * FROM users LIMIT 50;
-- Query 2..51: SELECT * FROM orders WHERE user_id = ?;

-- FIX 1: JOIN (eager loading)
SELECT u.*, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (SELECT id FROM users LIMIT 50);

-- FIX 2: Batch query (two queries total)
-- Step 1: SELECT * FROM users LIMIT 50;
-- Step 2: collect user_ids, then:
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5, 7, 8, 12, 15, 20);

-- FIX 3: Django ORM example
-- BAD:  User.objects.all()[:50]  (triggers N+1)
-- GOOD: User.objects.prefetch_related('orders')[:50]

6. Pagination Strategies

OFFSET/LIMIT pagination is simple but scales poorly — the database must scan and discard all rows before the offset. For page 1000 with 20 rows per page, the database reads 20,000 rows and discards 19,980. Keyset (cursor) pagination uses a WHERE clause on an indexed column to skip directly to the next page, maintaining constant performance.

The limitation of cursor pagination is that it does not support "jump to page N" — only "next/previous" navigation. For UIs that need page numbers, use a hybrid approach: use OFFSET for the first few pages (performance is acceptable), and for deep pages, guide users to use search or filters to narrow the result set. API pagination should almost always use cursor-based approach.

-- BAD: OFFSET pagination (slow for deep pages)
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 10000;  -- scans 10020 rows!

-- GOOD: Keyset / cursor pagination
SELECT * FROM products
WHERE id > 50240  -- last seen ID from previous page
ORDER BY id
LIMIT 20;  -- always scans exactly 20 rows

-- For multi-column sorting:
SELECT * FROM products
WHERE (created_at, id) > ('2025-06-15', 9823)
ORDER BY created_at, id
LIMIT 20;

-- Encode cursor as base64 for API responses
-- cursor: eyJjcmVhdGVkX2F0IjoiMjAyNS0wNi0xNSIsImlkIjo5ODIzfQ==

7. Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row. They replace many patterns that previously required self-joins or correlated subqueries. Common window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, and AVG with OVER clauses.

The PARTITION BY clause in window functions groups rows (similar to GROUP BY but without collapsing rows), ORDER BY defines ordering within the window. The frame clause (ROWS BETWEEN / RANGE BETWEEN) controls which rows are included in the calculation. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, useful for running totals, but moving averages require explicit frame specification.

-- ROW_NUMBER: assign sequential numbers
SELECT name, department, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- LAG / LEAD: access previous / next row values
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_sales;

-- Running total with SUM window function
SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM transactions;

8. Common Table Expressions

CTEs (WITH clauses) improve query readability by breaking complex logic into named steps. Recursive CTEs can traverse hierarchical data like org charts or category trees. In PostgreSQL 12+, non-recursive CTEs are inlined by the optimizer (no longer optimization fences). Use MATERIALIZED hint when you want to force materialization for performance.

The core structure of recursive CTEs is: base query UNION ALL recursive query. The recursive query references the CTE itself, each iteration producing new rows until no new rows are generated. Always add a depth limit (WHERE depth < N) or use CYCLE detection to prevent infinite loops. Recursive CTEs are the standard approach for tree structures, graph traversal, and hierarchical data.

-- Recursive CTE: traverse org chart hierarchy
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive step: find direct reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
  WHERE t.depth < 10  -- prevent infinite loops
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Materialized CTE (PostgreSQL 12+)
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, SUM(amount) AS total
  FROM orders GROUP BY user_id
)
SELECT * FROM expensive_calc WHERE total > 1000;

9. Partitioning

Table partitioning splits a large table into smaller physical pieces while maintaining a single logical table. Range partitioning divides by date ranges or numeric ranges. List partitioning divides by discrete values (region, status). Hash partitioning distributes rows evenly across partitions. The optimizer prunes partitions not needed by a query, dramatically reducing I/O.

Best candidates for partitioning: tables with hundreds of millions of rows, time-series data with date-based queries, and tables where old data is regularly archived or deleted.

Partition maintenance tips: Set up a cron job to auto-create future partitions for time-series data. Use the pg_partman extension to automate partition management. Old partitions can be DETACHed and moved to cold storage or DROPped directly, which is orders of magnitude faster than DELETEing millions of rows. PostgreSQL 14+ supports parallel queries on partitioned tables for further performance gains.

-- Range partitioning by date (PostgreSQL)
CREATE TABLE events (
  id         BIGSERIAL,
  event_type TEXT,
  payload    JSONB,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Queries automatically prune irrelevant partitions
SELECT * FROM events
WHERE created_at >= '2025-02-01'
  AND created_at < '2025-03-01';
-- Only scans events_2025_02 partition

10. Connection Pooling

Database connections are expensive resources. Each PostgreSQL connection consumes about 10MB of memory and a server-side process. Connection pooling reuses a fixed set of connections across application requests. Popular poolers include PgBouncer (external, lightweight) and HikariCP (JVM, embedded). The optimal pool size formula is: connections = (core_count * 2) + effective_spindle_count.

A common mistake is setting the pool too large. A database with 16 cores performs best with around 33-35 connections total, not hundreds. Too many connections cause excessive context switching.

PgBouncer supports three pool modes: session (connection bound to entire session, best compatibility), transaction (connection returned to pool after transaction ends, recommended for most applications), and statement (returned after each statement, does not support transactions). For microservice architectures, deploy a PgBouncer instance in front of each service, plus a server-side aggregation layer.

-- PgBouncer configuration (pgbouncer.ini)
-- [databases]
-- mydb = host=localhost port=5432 dbname=mydb
--
-- [pgbouncer]
-- listen_port = 6432
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 20
-- min_pool_size = 5

-- Pool sizing formula:
-- connections = (core_count * 2) + spindle_count
-- 4-core SSD server: (4 * 2) + 1 = 9 connections

-- HikariCP (Java / Spring Boot)
-- spring.datasource.hikari.maximum-pool-size=10
-- spring.datasource.hikari.minimum-idle=5
-- spring.datasource.hikari.idle-timeout=300000
-- spring.datasource.hikari.connection-timeout=20000
-- spring.datasource.hikari.max-lifetime=1200000

11. Locking & Concurrency

PostgreSQL uses MVCC (Multi-Version Concurrency Control) — readers never block writers and writers never block readers. Each transaction sees a snapshot of the database. Row-level locks are acquired for UPDATE/DELETE. Understanding lock modes and deadlock prevention is critical for high-concurrency applications.

Pessimistic locking (SELECT FOR UPDATE) prevents concurrent modifications but reduces throughput. Optimistic locking (version columns) allows concurrent reads and detects conflicts at write time. Always access tables in a consistent order to prevent deadlocks.

Key principles for deadlock prevention: all transactions access tables and rows in the same order. Keep transactions as short as possible. Avoid user interaction or external API calls within transactions. Set reasonable lock_timeout and statement_timeout values. Monitor lock contention using the pg_locks view and log_lock_waits parameter. When deadlocks occur, PostgreSQL automatically aborts one transaction — the application layer needs retry logic.

-- Pessimistic locking: SELECT FOR UPDATE
BEGIN;
SELECT * FROM inventory
WHERE product_id = 42
FOR UPDATE;  -- locks this row

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42;
COMMIT;

-- Optimistic locking: version column
UPDATE products
SET price = 29.99, version = version + 1
WHERE id = 42 AND version = 5;
-- If 0 rows updated -> conflict detected, retry

-- Advisory locks for application-level locking
SELECT pg_advisory_lock(hashtext('process-orders'));
-- ... do exclusive work ...
SELECT pg_advisory_unlock(hashtext('process-orders'));

12. Slow Query Analysis

Finding and fixing slow queries requires systematic monitoring. PostgreSQL pg_stat_statements tracks execution statistics for all queries. MySQL slow_query_log captures queries exceeding a time threshold. Both provide total execution time, call count, and average duration — focus on queries with the highest total time, not just the slowest individual execution.

Optimization workflow: First sort by total execution time to identify high-impact queries. Then run EXPLAIN ANALYZE on each query to understand the execution plan. Check for missing indexes, stale statistics (run ANALYZE to update), and whether the query can be rewritten. Verify improvements after fixes and continue monitoring. The auto_explain extension automatically logs execution plans for slow queries, which is invaluable for diagnosing intermittent performance issues.

-- Enable pg_stat_statements (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Find top 10 slowest queries by total time
SELECT query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- MySQL: enable slow query log
-- SET GLOBAL slow_query_log = 'ON';
-- SET GLOBAL long_query_time = 1;  -- seconds
-- SET GLOBAL log_queries_not_using_indexes = 'ON';

13. Database Scaling

When a single server cannot handle the load, scaling strategies include vertical scaling (bigger server), read replicas (distribute read traffic), and sharding (distribute write traffic across multiple databases). Read replicas are the simplest scaling approach — route all SELECT queries to replicas and writes to the primary. Sharding adds significant application complexity and should be a last resort.

Before sharding, exhaust other options: optimize queries, add indexes, implement caching (Redis/Memcached), use read replicas, partition tables, and archive old data. Most applications never need sharding.

Read replica implementation notes: Configure streaming replication, route queries at the application level by type (writes to primary, reads to replica). Note that replicas have replication lag (typically milliseconds), and reads requiring strict consistency must still go to the primary. ProxySQL (MySQL) or Pgpool-II (PostgreSQL) can handle read/write splitting automatically. Sharding strategies include range-based sharding by ID, hash-based sharding, and tenant-based sharding (most common for multi-tenant SaaS).

-- Read replica routing (application level)
-- Primary: INSERT, UPDATE, DELETE
-- Replica: SELECT queries

-- PostgreSQL streaming replication setup:
-- primary postgresql.conf:
-- wal_level = replica
-- max_wal_senders = 10

-- replica recovery.conf:
-- primary_conninfo = 'host=primary port=5432'
-- standby_mode = on

-- Application-level read/write splitting
-- const db = {
--   primary: new Pool({ host: 'primary-db' }),
--   replica: new Pool({ host: 'replica-db' }),
-- };
-- const read = (sql) => db.replica.query(sql);
-- const write = (sql) => db.primary.query(sql);

Conclusion: SQL Optimization Checklist

SQL optimization is not a one-time task but an ongoing process. Here is a practical optimization checklist, ordered by priority:

1. Monitor first — Enable pg_stat_statements or slow_query_log, identify queries with highest total execution time
2. Analyze query plans — Run EXPLAIN ANALYZE on high-impact queries, check for sequential scans and row estimate deviations
3. Add missing indexes — Create composite and covering indexes based on query patterns, following the ESR rule
4. Fix N+1 queries — Enable query detection tools in development, replace with eager loading or batch queries
5. Optimize pagination — Migrate OFFSET pagination to cursor-based pagination, especially for API endpoints
6. Tune connection pool — Set reasonable pool size using the formula, deploy PgBouncer or HikariCP
7. Consider partitioning — Evaluate partitioning strategies for tables exceeding 100 million rows
8. Scale last — Read replicas first, then caching, and consider sharding only as a last resort

Frequently Asked Questions