Reading PostgreSQL EXPLAIN ANALYZE: How We Find the Missing Index Behind a Slow Node.js Endpoint

Key takeaways

EXPLAIN ANALYZE executes the statement it explains, so our team runs it on INSERT, UPDATE or DELETE only inside BEGIN and ROLLBACK.

We start from pg_stat_statements sorted by total_exec_time, not from the query we suspect, because a fast query that runs on every request often costs more in total than a slow query that runs twice a day.

Rows Removed by Filter is the clearest missing-index signal in a PostgreSQL plan: the database read those rows only to throw them away.

In a composite B-tree index we put equality columns first and the sort or range column last, so one index serves both the WHERE clause and ORDER BY with LIMIT.

We build production indexes with CREATE INDEX CONCURRENTLY, which does not block writes but cannot run inside a transaction block.

Why do we read query plans instead of guessing indexes?

At Devya we read the query plan before adding any PostgreSQL index, because indexes added by guesswork speed up one query and slow down every write. A familiar pattern on Node.js projects is an endpoint that was fast in development and gets slower every week in production while its code never changes: the query is the same, the table has grown. These notes describe how our team reads PostgreSQL query plans as application developers, using node-postgres and Drizzle ORM on PostgreSQL 17. The same steps work with Prisma, TypeORM or raw SQL.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN prints the plan the PostgreSQL planner chose, with cost estimates, without running the query. EXPLAIN ANALYZE runs the query and adds actual time, actual row counts and loop counts to every plan node. A query plan is the tree of steps, such as scans, joins and sorts, that PostgreSQL executes to produce a result.

Plan costs are arbitrary planner units, not milliseconds, so we only compare costs between plans for the same query. The BUFFERS option shows how many 8 kB pages each node found in shared memory (shared hit) and how many it fetched from the operating system or disk (read). PostgreSQL 18 includes buffer counts in EXPLAIN ANALYZE by default; on PostgreSQL 17 and earlier we pass BUFFERS explicitly.

Because ANALYZE really executes the statement, EXPLAIN ANALYZE on an UPDATE changes rows and fires triggers. Our explain helper opens a transaction with BEGIN, runs EXPLAIN (ANALYZE, BUFFERS) with the query text and its parameters, and always finishes with ROLLBACK. To explain the exact SQL an ORM generates, we call toSQL() on a Drizzle query, which returns the SQL text and its parameters; with Prisma, the query log event exposes the same two values.

How do we find which query to EXPLAIN?

We find the query with pg_stat_statements, a PostgreSQL extension that records execution statistics for every normalized statement. It must be listed in shared_preload_libraries and enabled with CREATE EXTENSION pg_stat_statements, and most managed PostgreSQL services offer it. We select calls, total_exec_time, mean_exec_time and the query text, ordered by total_exec_time descending, because total time is mean time multiplied by calls.

pg_stat_statements stores queries with $1-style placeholders, so we take real parameter values from application logs before running EXPLAIN ANALYZE. Values matter: on skewed data, a tenant with ten rows and a tenant with a million rows can get completely different plans for the same SQL. For plans that only go wrong under production load, the auto_explain module shipped with PostgreSQL logs the plan of every statement slower than auto_explain.log_min_duration.

Which lines in a PostgreSQL query plan actually matter?

We read a plan from the innermost node outwards, because child nodes run first and feed rows to their parents. A typical slow plan for a query like WHERE tenant_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 20 is a Limit over a Sort over a Seq Scan on orders, where the Seq Scan reports a large Rows Removed by Filter count: PostgreSQL read the whole table, discarded almost every row, sorted the rest and returned 20.

Rows Removed by Filter much larger than actual rows: the scan reads rows only to reject them, which usually means a missing or unusable index.

Estimated rows far from actual rows: a gap of 10x or more means the planner chose its plan with wrong numbers. We run ANALYZE on the table, and add CREATE STATISTICS when the filtered columns are correlated.

loops greater than 1: actual rows and time are reported per loop, so we multiply by loops. A cheap-looking node inside a Nested Loop that runs thousands of times is often the real cost.

Sort Method external merge with Disk: the sort spilled past work_mem. An index that already returns rows in order removes the sort entirely.

High shared read compared with shared hit: the data was not in PostgreSQL's cache, so the query is disk-bound and slower on a cold cache than in repeated tests.

How do we choose between a composite, partial and covering index?

We choose the index from the shape of the query: equality columns first, then the range or sort column, and a partial predicate or INCLUDE columns only when the plan shows extra work remaining. For the orders query above, the composite index is CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx ON orders (tenant_id, status, created_at). With it, the plan becomes a Limit over an Index Scan Backward: the Sort node disappears and PostgreSQL reads only the 20 rows the LIMIT needs. A B-tree index can be scanned backwards, so created_at DESC in the index definition is only needed when the query mixes sort directions.

Composite B-tree index: serves queries that filter on a leading prefix of its columns. An index on (tenant_id, status, created_at) helps a query on tenant_id alone but not one on status alone; the B-tree skip scan added in PostgreSQL 18 narrows that gap only when the leading column has few distinct values.

Partial index, for example ON orders (tenant_id, created_at) WHERE status = 'pending': smaller and cheaper to maintain when the hot query reads a small subset of rows. The planner uses it only when it can prove the query's WHERE clause implies the index predicate, so we write the predicate as a literal in SQL rather than a parameter.

Covering index with INCLUDE, for example ON orders (tenant_id, created_at) INCLUDE (total_cents): available since PostgreSQL 11, it lets an Index Only Scan answer the query without visiting the table, as long as autovacuum keeps the visibility map current.

Expression index, for example ON users (lower(email)): required whenever the query wraps a column in a function, because an index on email cannot serve lower(email) = $1.

Why does PostgreSQL ignore an index?

PostgreSQL ignores an index when the planner estimates a sequential scan is cheaper, or when the query is written in a way the index cannot serve. The causes our team meets most often are development data that is too small (on a few hundred rows a Seq Scan really is cheaper, so we seed realistic volumes with generate_series and run ANALYZE), filters that match a large share of the table, columns wrapped in a function or cast such as created_at::date = $1 (we rewrite it as a range on created_at), a missing leading index column, LIKE patterns that start with a wildcard (a GIN index with pg_trgm serves those), and statistics that are stale after a bulk load.

To check whether an index is usable at all, we run SET enable_seqscan = off in a single psql session and explain again. That setting is a diagnostic only and never belongs in production configuration.

How do we add an index to a production table safely?

We build indexes on live tables with CREATE INDEX CONCURRENTLY, which lets inserts, updates and deletes continue during the build; a plain CREATE INDEX blocks writes until it finishes. The concurrent build takes longer and cannot run inside a transaction block, so migration tools that wrap each migration in a transaction need that disabled for the index migration.

A failed concurrent build leaves an INVALID index that still slows down writes. We find these in pg_index where indisvalid is false, drop them with DROP INDEX CONCURRENTLY and retry. After every new index we run the same EXPLAIN ANALYZE again to confirm the plan changed, and we review pg_stat_user_indexes for indexes whose idx_scan stays at 0, checking read replicas, which keep their own statistics, before dropping anything. Unique indexes and indexes that back constraints stay regardless.

FAQ

Q: Is it safe to run EXPLAIN ANALYZE in production?

A: For a SELECT, yes, but it runs the full query, so a slow query costs its full time again. For INSERT, UPDATE and DELETE, run EXPLAIN ANALYZE inside BEGIN and ROLLBACK, because ANALYZE performs the write and fires its triggers.

Q: What is a good cost number in a PostgreSQL plan?

A: There is no good absolute cost. Costs are planner units relative to seq_page_cost, which defaults to 1.0, and are only meaningful when comparing plans for the same query; actual time from EXPLAIN ANALYZE shows real performance.

Q: Why is a query fast in psql but slow from a Node.js app?

A: The app may send parameter values that hit a much larger slice of skewed data, or a named prepared statement may have switched to a generic plan after five executions. The time may also be spent outside PostgreSQL, in pool waits, network round trips or an N+1 query pattern, so compare mean_exec_time in pg_stat_statements with the timing measured in the app.

Q: How many indexes should a PostgreSQL table have?

A: As few as serve the queries the application really runs. Each index adds work to every INSERT, to updates of indexed columns and to vacuum, and competes for cache memory, so indexes whose idx_scan stays at 0 are candidates to remove.