Topic: PostgreSQL

Do not add an index first: use EXPLAIN, statistics, and ANALYZE to see what PostgreSQL is guessing

PostgreSQL chooses query plans from estimated costs. Compare EXPLAIN estimates with real execution before deciding whether statistics, query design, or an index needs to change.

When a query gets slow, adding an index is an easy reflex. Seeing Seq Scan makes it worse, as if PostgreSQL skipped an obvious shortcut.

SQL says which result we want. It does not prescribe how to get it. The planner compares viable paths using table size, available indexes, settings, and collected statistics, then picks the path with the lowest estimated cost. A sequential scan can be right when a query needs most of a table. It can also be a symptom of a bad estimate. Those cases need different fixes.

This is not a list of plan-node names to memorize. It is a debugging order: see what PostgreSQL plans to do, measure what it did, then decide whether to change the query, refresh statistics, or add an index.

EXPLAIN shows a plan. EXPLAIN ANALYZE runs it.

Start with the version that does not execute the statement:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

It shows the chosen plan, estimated row counts, and estimated cost at each node. cost=... is not milliseconds or user-facing latency. It is a score in PostgreSQL’s planning model for comparing options.

Then add measured execution data only where running the query is acceptable:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

ANALYZE really executes the statement. A SELECT may still be expensive and read real data. An INSERT, UPDATE, or DELETE changes data. Test write statements only in a controlled environment, or use an explicit transaction with ROLLBACK for measurement.

Start with rows. Do not convict a sequential scan first.

When reading EXPLAIN (ANALYZE, BUFFERS), I start here:

• The node type, such as Seq Scan, Index Scan, Sort, or a join. It is a clue, not a verdict.
• Whether estimated rows and actual rows differ sharply. This is often the best first lead.
• loops. A node that returns little work once can become expensive when the parent runs it many times.
• Final Execution Time, plus the shared hit, read, and temp data in BUFFERS. These point toward CPU work, cache, disk reads, or a sort spilling to disk, but one run still depends on cache state.

Imagine PostgreSQL expects a filter to return 10 rows but it returns 100,000. It may choose a nested loop or index path suitable for a tiny result, then repeat far too much work. That does not prove statistics are wrong. Skew, correlated predicates, range conditions, and the query shape can also cause the mismatch. Find the first node with a large estimate error, then inspect the conditions it depends on.

ANALYZE updates statistics. Autovacuum handles routine maintenance.

ANALYZE orders; samples a table and records information the planner uses, including value distributions, distinct counts, and common values. It does not rewrite the table or repair a query automatically.

Autovacuum is background maintenance. PostgreSQL’s MVCC leaves old row versions after UPDATE and DELETE. Ordinary VACUUM makes that space reusable and helps manage transaction IDs over time. The automatic maintenance system also schedules auto-analyze from table changes and thresholds so the planner can receive newer statistics.

Two distinctions matter here.

• Autovacuum is not a reason to stop thinking. It measures change volume against thresholds. It cannot infer that one column’s distribution now makes a particular range query unreliable. After a bulk import, backfill, or broad reclassification, a manual ANALYZE can make fresh statistics an explicit post-operation step before a critical query runs.
• ANALYZE and VACUUM have different jobs. The first improves the planner’s information. The second mainly handles dead tuples and transaction ID maintenance. VACUUM FULL rewrites a table and takes a strong lock. It is not routine medicine for a slow query or a bad estimate.

To decide whether statistics are actually behind, inspect last_analyze, last_autoanalyze, and n_mod_since_analyze in pg_stat_user_tables in an authorized operational environment, then compare them with a representative plan. Do not globally retune autovacuum or reset statistics because one page is slow.

Correlated columns may need extended statistics

Regular column statistics treat predicates as fairly independent. Real data often is not. One country may almost always have one currency, or a status may occur in only a few categories. A query filtering both columns can make the planner multiply separate proportions and reach an implausible row estimate.

That is when CREATE STATISTICS is worth considering. It is not an index and does not speed a query directly. It tells the planner more about relationships in the data.

CREATE STATISTICS orders_region_status_mcv (mcv)
ON region, status
FROM orders;

ANALYZE orders;

• ndistinct describes the number of distinct values across a column group.
• dependencies describes functional dependencies. It mainly helps simple equality predicates and does not make an expression or pattern such as lower(email) LIKE 'a%' fully understood.
• mcv records common combinations of column values. It helps when a small number of combinations are frequent and the independence assumption fails.

First use the actual-row mismatch to prove the problem, then choose a type. Creating extended statistics without evidence is another maintenance item with no demonstrated benefit.

JSON makes plans comparable. It does not make them better.

Text plans are good for a person. A machine-readable format helps when an internal tool stores plans, compares deployments, or monitors a regression:

EXPLAIN (FORMAT JSON)
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

JSON gives software a stable structure for Node Type, estimated rows, and related plan data. With ANALYZE, it also includes actual rows, loops, and buffer data. JSON alone does not execute the query. With ANALYZE, the same execution risk still applies.

There is a practical safety boundary too. Plans can contain table names, columns, filters, or parameter values. Do not paste a production plan unredacted into a public visualizer or chat. Remove sensitive content first, or keep it in an authorized internal monitoring system.

A repeatable investigation order

1. Use EXPLAIN to see what the planner intends to do.
2. With representative parameters and an acceptable cost, run EXPLAIN (ANALYZE, BUFFERS).
3. Start at the earliest large row-estimate mismatch, rather than judging the outermost node first.
4. Check the predicate, data distribution, recent data changes, and statistics state.
5. Choose ANALYZE, extended statistics, a query rewrite, or an index change only when the evidence points there. Then measure again.

This takes a little longer than adding an index every time. It avoids a common false fix. The planner is not a black box. It is a cost estimate made from incomplete information. Put estimates next to actual work, and the next change becomes much clearer.

What I learned

• I can compare estimated rows with actual rows before deciding whether a problem looks like statistics, data relationships, or a mismatch between the query and its indexes.
• EXPLAIN does not run SQL. EXPLAIN ANALYZE does, so testing writes needs an explicit transaction boundary.
• ANALYZE refreshes information about data distribution for the planner. Autovacuum and ordinary VACUUM handle another part of routine maintenance. Neither replaces query design.
• Extended statistics are tools for an observed estimation error, not another default index.

External references

• PostgreSQL: Using EXPLAIN
• PostgreSQL: EXPLAIN
• PostgreSQL: Planner Statistics
• PostgreSQL: Routine Vacuuming
• pganalyze: The Basics of Postgres Query Planning
• Reddit: PostgreSQL users discuss investigating slow queries

Further learning

• PostgreSQL: Using EXPLAIN has official examples for scan, join, sort, and row estimates.
• PostgreSQL: Planner Statistics explains pg_stats and the exact limits of extended statistics.
• PostgreSQL: Routine Vacuuming separates VACUUM, autovacuum, and transaction ID maintenance.
• pganalyze: The Basics of Postgres Query Planning explains plan nodes, costs, and execution data with a gentler introduction.