SQL query optimization

Start with the plan that ran

A slow SQL statement gives you a symptom. A PostgreSQL execution plan shows the operations that produced it. The examples here use PostgreSQL 18 because EXPLAIN output and optimizer behavior are database-specific.

Plain EXPLAIN does not execute the statement. It displays the plan chosen by the planner and its estimates:

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42;

EXPLAIN ANALYZE executes the statement and adds measured row counts and timing. PostgreSQL cost units are arbitrary planner units, while actual time is measured in milliseconds. Comparing cost=810 with actual time=920 ms is meaningless because the units differ.

For a read-only query that is safe to execute, collect the measured plan and buffer activity:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42;

ANALYZE is not a harmless display option. The statement runs. An UPDATE, DELETE, INSERT, or MERGE performs its usual side effects even though PostgreSQL discards rows that would have been sent to the client.

Wrong "EXPLAIN ANALYZE simulates the query."

Right It executes the query and reports measurements from that execution.

Read an EXPLAIN plan as a tree

The indentation shows parent and child nodes. Child nodes produce rows for the parent above them:

Nested Loop
  ->  Seq Scan on customers
  ->  Index Scan using orders_customer_id_idx on orders

The sequential scan produces customer rows. For each outer customer row, this nested loop runs its inner orders index scan. Start at the deepest nodes to see where rows come from, then follow them toward the root.

A node line commonly includes these fields:

• cost=a..b: estimated startup cost and estimated total cost if the node runs to completion
• rows=n: estimated rows emitted by the node
• width=n: estimated average bytes per emitted row
• actual time=a..b: measured time to first row and to completion for that node
• actual rows=n: measured rows emitted per execution
• loops=n: number of times the node executed

PostgreSQL averages actual time and rows per execution when loops exceeds one. Include the loop count when assessing total work. This inner scan emits about 50,000 rows in total:

Index Scan using orders_customer_id_idx on orders
  (actual time=0.010..0.015 rows=1.00 loops=50000)

The inner scan is cheap once, yet 50,000 executions can dominate the query.

Estimated rows versus actual rows

The planner needs row-count estimates to compare possible plans. In PostgreSQL, ANALYZE collects approximate statistics from a sample. Those statistics help estimate what fraction of a table matches each predicate.

Look for the first large divergence between estimated and actual rows. Suppose a scan shows this:

Seq Scan on customers
  (cost=0.00..500.00 rows=100 width=24)
  (actual time=0.020..80.000 rows=50000.00 loops=1)
  Filter: (status = 'active')

The planner expected 100 rows and received 50,000. A later nested loop may be expensive because it was costed for a much smaller outer input. The join node is where the cost becomes visible, while the scan is where the estimate first went wrong.

Check whether the table statistics represent the current data, then measure again:

ANALYZE customers;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active';

A scan is not a verdict

A sequential scan reads the table directly. An index scan uses an index to locate candidates and then may fetch table rows. PostgreSQL can prefer a sequential scan when much of a table is needed or when the table is tiny. The presence of Seq Scan alone does not prove a bad plan.

Wrong "Every slow query needs an index, and every sequential scan is bad."

Right Use measured rows, loops, filters, and buffer activity to find excess work. Then decide whether the cause is an indexable predicate, weak estimates, or a query that must process many rows.

A useful first pass is narrow: reproduce with representative parameter values, capture EXPLAIN (ANALYZE, BUFFERS) when execution is safe, find the earliest row-estimate error, and count repeated work. PostgreSQL's measured execution time excludes sending result rows over the network, so a fast server-side plan does not rule out response-transfer time outside the plan.

Why the join algorithm changed

PostgreSQL 18 can represent joins with nested-loop, hash, and merge nodes. The planner compares possible plans using estimated input sizes and costs. A join name is a description of the selected operation, not a performance grade.

Nested loop

A nested loop takes one row from its outer child and runs its inner child for that row. The inner side can use values from the current outer row:

Nested Loop
  ->  Bitmap Heap Scan on customers c
  ->  Index Scan using orders_customer_id_idx on orders o
        Index Cond: (customer_id = c.id)

This shape can do little work when the outer input is small and the inner lookup is selective. It can do far more work when the outer estimate is 100 rows but the execution produces 50,000. In PostgreSQL plans, actual rows and times are averages per execution when loops is greater than one.

Wrong "Nested loops are slow because they always scan both complete tables."

Right The inner child runs once per outer row, but it may be an indexed lookup or a materialized input. The plan tells you which operation repeats.

Hash join

A PostgreSQL hash join puts rows from one input into a hash table, scans the other input, and probes the hash table for matches:

Hash Join
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on customers c

EXPLAIN ANALYZE reports the hash table's buckets, batches, and peak memory. PostgreSQL documentation states that more than one batch involves disk space. Record that evidence before changing memory settings or rewriting the join.

Merge join

A merge join requires both inputs to be sorted on the join keys. PostgreSQL may obtain that order from index scans or from explicit sort nodes:

Merge Join
  Merge Cond: (o.customer_id = c.id)
  ->  Index Scan using orders_customer_id_idx on orders o
  ->  Sort
        Sort Key: c.id
        ->  Seq Scan on customers c

The surrounding nodes matter. A merge join with two already ordered inputs has a different cost shape from one that first sorts both sides.

Sargable predicates expose an index search

Microsoft defines a SARGable predicate as one for which SQL Server can use an index seek. The general diagnostic question also applies when reading PostgreSQL plans: can the indexed value be searched directly, or must an expression be evaluated for rows first?

This predicate applies a function to every candidate created_at value:

SELECT id
FROM orders
WHERE date(created_at) = DATE '2026-07-01';

For a timestamp without time zone column, an equivalent half-open range exposes the original column to a B-tree search:

SELECT id
FROM orders
WHERE created_at >= TIMESTAMP '2026-07-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-07-02 00:00:00';

The half-open upper bound includes every value on July 1 without guessing the timestamp's precision. Type and time-zone semantics must stay equivalent to the original query.

PostgreSQL also supports indexes on expressions. If the application intentionally searches by lower(email), the index can store that expression:

CREATE INDEX users_lower_email_idx ON users (lower(email));

SELECT id
FROM users
WHERE lower(email) = 'dev@example.com';

Expression indexes trade faster matching reads for extra index maintenance during inserts and non-HOT updates. A rewrite and an expression index are different design choices. Confirm the chosen plan with EXPLAIN after either change.

What Index Only Scan promises in PostgreSQL

An ordinary PostgreSQL index scan can read both the index and the table heap. An index-only scan is physically possible when the index type supports it and every column needed by the query is stored in the index. Payload columns can be added with INCLUDE:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id)
INCLUDE (created_at);

SELECT created_at
FROM orders
WHERE customer_id = 42;

The node name does not promise zero heap visits. PostgreSQL must still check whether each row is visible to the query's MVCC snapshot. It checks the heap page's all-visible bit in the visibility map. When that bit is unset, it visits the heap entry for visibility.

Wrong "Index Only Scan means the heap was never touched."

Right It means the query can obtain its values from the index. Heap visibility checks can remain, especially on recently modified pages.

Adding payload columns also makes the index larger and duplicates table data. PostgreSQL warns that wide payload columns can bloat an index and can even exceed the index type's tuple-size limit. Inspect heap fetches and the workload before widening an index.

Triage the first bad multiplication

Read from the scan nodes upward and keep four quantities separate: estimated rows, actual rows, loops, and buffer reads. A filter that removes many rows after a broad scan may point to a predicate that the access method could not use. A small per-loop inner scan with a huge loop count points to repeated work. A large estimate error before either one points back to statistics or a planner assumption.

After one bounded change, rerun the same query with representative parameter values:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Compare the first estimate divergence, total repeated work, buffer activity, and execution time. A changed node name without reduced measured work is not evidence of an improvement.

Cardinality errors travel up the tree

The PostgreSQL 18 planner needs estimates of rows emitted by scans, joins, and groups. Those cardinalities affect the cost assigned to competing access paths and join algorithms. A poor estimate low in the tree can make a reasonable plan choice look disastrous at execution time.

Suppose a filtered scan reports this:

Seq Scan on customers
  (cost=0.00..850.00 rows=10 width=16)
  (actual time=0.020..20.000 rows=15000.00 loops=1)
  Filter: ((country = 'CY') AND (postal_prefix = '10'))

The estimate is low by a factor of 1,500. Debug this scan before blaming the join above it. PostgreSQL stores table and index size estimates in pg_class; ANALYZE collects approximate selectivity statistics in pg_statistic, exposed through the readable pg_stats view.

Inspect the relevant single-column statistics instead of treating ANALYZE as a ritual:

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'customers'
  AND attname IN ('country', 'postal_prefix');

ANALYZE uses sampling, so freshly collected statistics remain approximate. PostgreSQL allows a higher statistics target for a column with an irregular distribution. The documentation notes that this can improve estimates at the cost of more statistics storage and more analysis time:

ALTER TABLE customers
  ALTER COLUMN postal_prefix SET STATISTICS 500;

ANALYZE customers;

Do this when the observed distribution and estimate error justify it. A larger target is not a generic query-speed switch.

Correlated predicates need cross-column evidence

Ordinary PostgreSQL statistics describe individual columns. The planner normally assumes that separate conditions are independent. If country and postal_prefix are correlated, multiplying their separate selectivities can severely underestimate their combined result.

PostgreSQL extended statistics can collect evidence across selected column groups. For equality comparisons against constants, dependency statistics can adjust the estimate:

CREATE STATISTICS customers_country_postal_stats (dependencies)
ON country, postal_prefix
FROM customers;

ANALYZE customers;

Multivariate most-common-value statistics are another supported choice for combinations that occur with unusual frequencies:

CREATE STATISTICS customers_country_postal_mcv (mcv)
ON country, postal_prefix
FROM customers;

ANALYZE customers;

PostgreSQL does not create every possible multivariate statistics object automatically because the number of column combinations is large. Create one for columns used together when a measured estimate error shows that the relationship matters.

Dependency statistics have a boundary in PostgreSQL 18. They apply to simple equality conditions that compare columns with constant values and to IN lists of constants. They do not improve range, LIKE, column-to-column, or column-to-expression estimates.

Wrong "Running ANALYZE teaches the planner every relationship between every column."

Right Ordinary statistics remain per column. Cross-column relationships require selected extended statistics, and each extended-statistics kind has documented limits.

Separate plan symptoms from causes

A nested loop with 100,000 inner executions may be the result of an outer input underestimated as 100 rows. A hash node with Batches: 16 reports disk involvement. A merge join may have sort work below it because its inputs were not already ordered. These facts describe execution. They do not establish a universal ranking among the three joins.

For repeated nodes, PostgreSQL reports actual row count and time as averages per execution. Multiply by loops to recover total node work:

Index Scan using orders_customer_id_idx on orders
  (actual time=0.008..0.012 rows=2.00 loops=100000)

This node emits about 200,000 rows across all executions. Reading rows=2 without loops=100000 hides the multiplication.

The same care applies to index-only scans. The query can obtain all referenced values from the index, but PostgreSQL may still visit heap entries when a page lacks an all-visible visibility-map bit. A write-heavy table can therefore show an Index Only Scan with substantial heap fetches. Adding more INCLUDE columns cannot solve visibility checks, and it enlarges the index.

A bounded slow-query investigation

First capture the exact statement shape and representative parameters. PostgreSQL can choose different plans for different table sizes, data distributions, and parameter values, so a toy data set is weak evidence.

When execution is safe, measure rather than infer:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Then walk from leaves to root:

1. Find the earliest large gap between estimated and actual rows.
2. For repeated nodes, multiply per-loop rows and time by loops.
3. Inspect index conditions, filters, rows removed, buffer reads, sort method, and hash batches where PostgreSQL reports them.
4. Make one bounded change to the predicate, statistics, index, or query shape that the evidence supports.
5. Rerun the same representative case and compare server execution time and work performed.

Every code change in that list should be concrete. For example, if a function hides a plain indexed timestamp and the column is timestamp without time zone, preserve the day semantics with a half-open range:

-- Before
SELECT id FROM orders
WHERE date(created_at) = DATE '2026-07-01';

-- After
SELECT id FROM orders
WHERE created_at >= TIMESTAMP '2026-07-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-07-02 00:00:00';

Wrong "Force the join that was faster in one test."

Right Correct the observed estimate or access problem, then verify the resulting plan and measured work. PostgreSQL may choose a different valid algorithm after the inputs are estimated more accurately.

EXPLAIN ANALYZE omits network transmission of result rows, and its measurement overhead can be significant on some systems. A plan is evidence about server execution under the tested conditions, not a complete latency trace.