Query Optimization: Indexes, Execution Plans, and Faster SQL

A query that returns in 40 ms on a 10,000-row dev table can take 40 seconds on 50 million production rows. The SQL text is identical. The difference is the execution plan, the sequence of scans, joins, and sorts the database engine chooses. SQL query optimization means reading that plan, finding the expensive node, and giving the planner a cheaper path through indexes, better statistics, or a rewritten query.

This guide uses PostgreSQL syntax. The same principles apply to MySQL, SQL Server, and Oracle, though command names differ.

Quick Takeaways

  • Read the plan before changing anything. Use EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows to actual rows.
  • Index the predicate, not the table. Match composite index column order to equality filters first, then range filters, then sort columns.
  • Keep predicates SARGable. Wrapping an indexed column in a function or cast blocks index use.
  • Fix statistics before fixing queries. A bad row estimate causes bad join choices. ANALYZE often solves it.
Symptom Likely Cause First Fix
Seq Scan on a large table with a selective filter Missing or unusable index Add a B-tree index on the filter column
Estimated rows ≪ actual rows Stale statistics or correlated columns Run ANALYZE; add extended statistics
Sort spilling to disk Insufficient work_mem or no index for ORDER BY Add an index matching the sort order
Nested Loop with huge loop count Wrong join order from bad estimates Fix statistics; index the inner join key
Slow deep pagination OFFSET scanning discarded rows Switch to keyset pagination

How the Query Planner Decides

Relational engines use a cost-based optimizer. The planner enumerates candidate plans, estimates each one’s cost from table statistics, and runs the cheapest. Cost is measured in abstract units that weigh sequential page reads (seq_page_cost), random page reads (random_page_cost), and CPU work per tuple.

The planner depends on three inputs:

  1. Statistics in pg_stats: row counts, distinct values, most common values, histograms.
  2. Available access paths: indexes, partitions, materialized views.
  3. Cost settings: random_page_cost, work_mem, effective_cache_size.

If the statistics are wrong, the plan is wrong. Most “mysterious” slow queries trace back to this.

Reading Execution Plans

EXPLAIN vs. EXPLAIN ANALYZE

EXPLAIN shows the estimated plan without running the query. EXPLAIN ANALYZE runs the query and reports actual timings and row counts. Always add BUFFERS to see cache hits versus disk reads.

-- ANALYZE executes the query. Wrap DML in a transaction and roll back.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_id, o.total_amount
FROM   orders o
WHERE  o.customer_id = 48213
  AND  o.created_at >= DATE '2026-01-01'
ORDER  BY o.created_at DESC
LIMIT  20;
ROLLBACK;

Here is illustrative output for an unindexed table:

Limit  (cost=412873.10..412873.15 rows=20 width=16)
       (actual time=1843.2..1843.3 rows=20 loops=1)
  ->  Sort  (cost=412873.10..412880.42 rows=2930 width=16)
            (actual time=1843.2..1843.2 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders o  (cost=0.00..412790.00 rows=2930 width=16)
                                  (actual time=0.9..1838.5 rows=3104 loops=1)
              Filter: ((customer_id = 48213) AND (created_at >= '2026-01-01'))
              Rows Removed by Filter: 24996896
              Buffers: shared read=187432

Three details matter here:

  • Rows Removed by Filter: 24,996,896 means the engine read 25 million rows to keep 3,104.
  • Buffers: shared read=187432 confirms heavy disk I/O.
  • The estimate (2,930 rows) is close to actual (3,104 rows), so statistics are fine. The problem is the access path.

Key Plan Metrics

Metric What It Tells You Red Flag
actual time Time to first row..last row, per loop Large gap between a node and its children
rows vs. actual rows Estimation accuracy Ratio above 10x in either direction
loops Times the node executed High loops × high per-loop time
Rows Removed by Filter Wasted reads Far larger than rows returned
Sort Method: external merge Sort spilled to disk Increase work_mem or add an index
shared read Pages fetched from disk Large values on repeated queries

Scan Types Compared

Scan Type Best When Cost Profile Weakness
Seq Scan Query returns a large share of the table Linear in table size Wasteful for selective filters
Index Scan Highly selective filter Random I/O per row Slow if many rows match
Index Only Scan All needed columns live in the index Skips heap fetches Needs a recently vacuumed visibility map
Bitmap Heap Scan Medium selectivity or combined indexes Batches heap reads in page order Loses index ordering

Join Algorithms Compared

Algorithm Best When Memory Sensitivity
Nested Loop Small outer set, indexed inner key Low Explodes if outer estimate is too low
Hash Join Large unsorted inputs, equality joins Needs work_mem for hash table Spills to disk if table exceeds memory
Merge Join Both inputs already sorted Low Sorting cost if no index supplies order

Indexing Strategies That Change Plans

An index is a sorted structure that lets the engine skip most of the table. It also adds write overhead and storage, so each index must justify itself.

Composite Index Column Order

For the query above, a single-column index on customer_id helps, but the ORDER BY created_at DESC LIMIT 20 still needs a sort. A composite index removes it.

-- Equality column first, then the range/sort column.
-- CONCURRENTLY avoids blocking writes during the build.
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

The ordering rule: equality predicates first, then range or sort columns. The engine seeks to customer_id = 48213, then reads entries already sorted by created_at. It stops after 20 rows. The sort node disappears.

Covering Indexes with INCLUDE

If the query selects total_amount, the engine still visits the heap for each row. Adding it as a payload column enables an Index Only Scan.

-- INCLUDE stores total_amount in the leaf pages without sorting on it.
CREATE INDEX CONCURRENTLY idx_orders_cust_created_cov
    ON orders (customer_id, created_at DESC)
    INCLUDE (total_amount);

-- Keep the visibility map fresh so index-only scans skip the heap.
VACUUM (ANALYZE) orders;

Partial and Expression Indexes

A partial index covers only the rows you query. It stays small and fast.

-- Only unshipped orders are queried by the fulfillment dashboard.
CREATE INDEX CONCURRENTLY idx_orders_pending
    ON orders (created_at)
    WHERE status = 'pending';

-- Expression index for case-insensitive email lookups.
CREATE INDEX CONCURRENTLY idx_users_email_lower
    ON users (LOWER(email));

Index Types Compared

Index Type Best For Supports Trade-off
B-tree Equality, ranges, sorting =, <, >, BETWEEN, ORDER BY Default choice; large on wide keys
Hash Pure equality lookups = only No range or sort support
GIN Arrays, JSONB, full-text search @>, ?, @@ Slow writes, large size
GiST Geometry, ranges, nearest-neighbor Overlap, distance operators Lossy; rechecks heap
BRIN Huge append-only tables with natural order Range scans on correlated columns Coarse; useless on unordered data

Write SARGable Predicates

A predicate is SARGable (Search ARGument ABLE) when the engine can use an index to evaluate it. Applying a function to the indexed column breaks this.

Non-SARGable (index ignored) SARGable Rewrite
WHERE DATE(created_at) = '2026-03-01' WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02'
WHERE EXTRACT(YEAR FROM created_at) = 2026 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE total_amount + 10 > 100 WHERE total_amount > 90
WHERE customer_id::text = '48213' WHERE customer_id = 48213
WHERE name LIKE '%smith' Use a trigram GIN index (pg_trgm) or reverse-string index

Query Rewrite Patterns

Replace NOT IN with NOT EXISTS

NOT IN returns no rows if the subquery contains a single NULL, and it often produces poor plans. NOT EXISTS is null-safe and usually becomes an efficient anti-join.

-- Slow and NULL-unsafe
SELECT c.customer_id
FROM   customers c
WHERE  c.customer_id NOT IN (SELECT o.customer_id FROM orders o);

-- Fast and null-safe
SELECT c.customer_id
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Replace OFFSET with Keyset Pagination

OFFSET 100000 LIMIT 20 forces the engine to produce and discard 100,000 rows. Keyset pagination seeks directly to the last-seen position.

-- Page N: pass the last (created_at, order_id) from the previous page.
SELECT order_id, created_at, total_amount
FROM   orders
WHERE  customer_id = 48213
  AND  (created_at, order_id) < (TIMESTAMP '2026-06-14 09:30:00', 9918234)
ORDER  BY created_at DESC, order_id DESC
LIMIT  20;

This requires an index on (customer_id, created_at DESC, order_id DESC).

Replace Correlated Subqueries with Window Functions

-- Correlated subquery: executes once per outer row
SELECT o.*
FROM   orders o
WHERE  o.created_at = (
    SELECT MAX(o2.created_at) FROM orders o2
    WHERE  o2.customer_id = o.customer_id
);

-- Window function: single pass
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY created_at DESC) AS rn
    FROM   orders o
) ranked
WHERE rn = 1;

Other High-Value Rewrites

Anti-Pattern Problem Better Approach
SELECT * Blocks index-only scans; wastes bandwidth List only required columns
OR across different columns Often forces a seq scan UNION ALL of two indexed queries
DISTINCT to hide join duplicates Adds a sort or hash over the full result Fix the join or use EXISTS
Implicit type casts in joins Disables index on the cast side Match column types in the schema
Functions in WHERE on indexed columns Non-SARGable Expression index or rewrite

Fix Statistics and Maintenance

When estimated and actual rows diverge by orders of magnitude, correct the statistics.

-- Refresh table statistics
ANALYZE orders;

-- Increase histogram resolution for a skewed column (default is 100)
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

-- Teach the planner about correlated columns
CREATE STATISTICS stx_orders_region_status (dependencies, ndistinct)
    ON region, status FROM orders;
ANALYZE orders;

Find the queries worth optimizing first with pg_stat_statements:

-- Requires: shared_preload_libraries = 'pg_stat_statements'
SELECT  LEFT(query, 80)                       AS query_snippet,
        calls,
        ROUND(total_exec_time::numeric, 0)    AS total_ms,
        ROUND(mean_exec_time::numeric, 2)     AS mean_ms,
        rows
FROM    pg_stat_statements
ORDER   BY total_exec_time DESC
LIMIT   10;

Sort by total_exec_time, not mean_exec_time. A 5 ms query called 20 million times costs more than a 30-second report run once a day.

Automate Plan Inspection with Python

This script runs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), walks the plan tree, and flags sequential scans on large tables plus bad row estimates.

import psycopg2

def get_plan(conn, sql, params=None):
    """Run EXPLAIN ANALYZE and return the parsed plan dict."""
    with conn.cursor() as cur:
        # ANALYZE executes the statement; use a read-only transaction for safety.
        cur.execute("SET TRANSACTION READ ONLY")
        cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql, params)
        return cur.fetchone()[0][0]  # JSON column is auto-parsed by psycopg2

def audit_node(node, findings, min_rows=100_000, ratio_limit=10):
    """Recursively flag expensive nodes."""
    planned = max(node["Plan Rows"], 1)
    actual = max(node["Actual Rows"], 1)          # per-loop actual rows
    ratio = max(planned / actual, actual / planned)

    # Flag seq scans that discard most of what they read
    removed = node.get("Rows Removed by Filter", 0) * node["Actual Loops"]
    if node["Node Type"] == "Seq Scan" and removed >= min_rows:
        findings.append(
            f"Seq Scan on {node['Relation Name']} discarded {removed:,} rows "
            f"-> consider an index on: {node.get('Filter', 'n/a')}"
        )

    # Flag stale or misleading statistics
    if ratio >= ratio_limit:
        findings.append(
            f"{node['Node Type']}: estimated {node['Plan Rows']:,} rows, "
            f"actual {node['Actual Rows']:,} -> run ANALYZE"
        )

    for child in node.get("Plans", []):
        audit_node(child, findings, min_rows, ratio_limit)

def audit_query(dsn, sql, params=None):
    conn = psycopg2.connect(dsn)
    try:
        plan = get_plan(conn, sql, params)
        findings = []
        audit_node(plan["Plan"], findings)
        print(f"Execution time: {plan['Execution Time']:.1f} ms")
        print("\n".join(findings) or "No obvious issues found.")
    finally:
        conn.rollback()
        conn.close()

if __name__ == "__main__":
    audit_query(
        "dbname=shop user=analyst",
        "SELECT order_id FROM orders WHERE customer_id = %s",
        (48213,),
    )

Real-World Use Cases

Case 1: Slow Customer Order History API

Problem: An e-commerce endpoint takes 1.8 s to return a customer’s latest 20 orders from a 25-million-row table.
Diagnosis: The plan shows a Seq Scan with a top-N sort and millions of rows removed by filter.
Fix: Create (customer_id, created_at DESC) INCLUDE (total_amount). The plan becomes an Index Only Scan that reads about 20 index entries. Latency typically falls from seconds to single-digit milliseconds.

Case 2: Nightly Reporting Query With Bad Join Order

Problem: A dashboard query joining events, sessions, and users runs for 12 minutes.
Diagnosis: EXPLAIN ANALYZE shows a Nested Loop where the planner estimated 50 rows but 4 million arrived. The filter columns device_type and os_family are highly correlated.
Fix: Add CREATE STATISTICS ... (dependencies) on both columns and re-run ANALYZE. The planner switches to a Hash Join and the query drops to under a minute.

Case 3: Time-Series Sensor Table

Problem: Range queries on a 2-billion-row sensor_readings table are slow, and a B-tree on reading_time is 80 GB.
Fix: Because rows are appended in time order, a BRIN index on reading_time is a small fraction of that size and still prunes block ranges effectively. Combine it with monthly range partitioning so the planner skips old partitions entirely.

Optimization Workflow Checklist

Step Action Tool
1 Identify top queries by total time pg_stat_statements
2 Capture the actual plan EXPLAIN (ANALYZE, BUFFERS)
3 Compare estimated vs. actual rows Plan output
4 Fix statistics if estimates are off ANALYZE, CREATE STATISTICS
5 Add or adjust indexes CREATE INDEX CONCURRENTLY
6 Rewrite non-SARGable or anti-pattern SQL Manual refactor
7 Re-run the plan and compare timings EXPLAIN (ANALYZE, BUFFERS)
8 Monitor index usage and drop dead indexes pg_stat_user_indexes

FAQ

Why is my query slow even though the column has an index?

The planner ignores an index when the predicate is not SARGable (for example, WHERE LOWER(email) = ... without an expression index), when the filter matches a large share of the table, or when statistics are stale. Run EXPLAIN ANALYZE to see the chosen scan type, then check for function calls on the column and run ANALYZE on the table.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN displays the planner’s estimated plan and costs without running the query. EXPLAIN ANALYZE executes the query and adds actual row counts, loop counts, and timings per node. Use ANALYZE to find estimation errors, but wrap INSERT, UPDATE, and DELETE statements in a transaction and roll back.

How many indexes should a table have?

There is no fixed number. Every index slows INSERT, UPDATE, and DELETE and consumes storage. Create indexes for your frequent, selective query patterns, prefer composite indexes over several single-column ones, and periodically drop unused indexes found via pg_stat_user_indexes where idx_scan = 0.

Does column order in a composite index matter?

Yes. The engine can use an index efficiently only through its leftmost columns. Put equality-filtered columns first, then range or sort columns. An index on (customer_id, created_at) serves queries filtering on customer_id alone, but not queries filtering only on created_at.

Hot this week

How Uber, Lyft, and Airbnb Use Data for Dynamic Pricing

See how Uber, Lyft, and Airbnb turn demand data into dynamic prices, with Python code for surge and booking models. Build your own pricing engine.

Pivoting and Unpivoting Data in SQL

Learn how to pivot and unpivot data in SQL with PIVOT, UNPIVOT, CASE, and UNNEST. Copy-paste code for SQL Server, PostgreSQL, and Snowflake. Start now.

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Topics

How Uber, Lyft, and Airbnb Use Data for Dynamic Pricing

See how Uber, Lyft, and Airbnb turn demand data into dynamic prices, with Python code for surge and booking models. Build your own pricing engine.

Pivoting and Unpivoting Data in SQL

Learn how to pivot and unpivot data in SQL with PIVOT, UNPIVOT, CASE, and UNNEST. Copy-paste code for SQL Server, PostgreSQL, and Snowflake. Start now.

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

Related Articles

Popular Categories