Stop Guessing: Reading Postgres Query Plans Like a Pro

Coming from MongoDB, you probably treated indexes as a checkbox: add one on the field you filter by and move on. PostgreSQL rewards a deeper understanding because its cost-based planner decides whether to actually use your index, and sometimes it correctly refuses. The difference between a query that scans ten rows and one that scans ten million is usually a single missing or misapplied index, but you cannot fix what you cannot see. EXPLAIN and EXPLAIN ANALYZE are your window into the planner's reasoning. They show you which access method Postgres chose, how many rows it expected versus actually found, and where time was spent. Once you can read these plans fluently, performance tuning stops being trial and error and becomes a methodical conversation with the database about what it knows and what it assumes.

How B-Tree Indexes Work

The default Postgres index is a B-tree, a balanced tree structure that keeps keys sorted so the database can find any value with a handful of page reads instead of scanning the whole table. Because the keys stay ordered, a B-tree accelerates equality lookups, range queries like timestamps greater than a date, and even ORDER BY clauses that match the index order. This is the same logic behind MongoDB indexes, but Postgres exposes far more control. An index on a single column helps queries filtering that column, while the leaf nodes point back to the physical row location in the heap. Understanding that an index is just a sorted copy of selected columns, maintained on every write, explains both why reads get faster and why piling on indexes slows your inserts and updates measurably over time.

Composite Indexes And Column Order

A composite index covers multiple columns, and the order you list them matters enormously. Postgres can use a multi-column index efficiently only when your query filters on a leading prefix of those columns. An index on tenant_id then created_at serves queries filtering by tenant_id alone, or by tenant_id and created_at together, but it cannot efficiently serve a query filtering only by created_at. The rule of thumb is to put equality-filtered columns first and range-filtered columns last, since once the planner hits a range condition it can no longer use later columns for seeking. Designing composite indexes around your actual query patterns, rather than guessing, prevents the common mistake of creating several single-column indexes when one well-ordered composite index would satisfy the workload and consume less write overhead.

Reading Sequential Versus Index Scans

When you run EXPLAIN, the most important line is the access method. A Seq Scan means Postgres read every row in the table, which is perfectly fine for small tables or queries that touch most rows. An Index Scan means it walked the B-tree to find matching rows, then fetched each one from the heap. An Index Only Scan is even better: all needed columns live in the index, so the heap is skipped entirely. A Bitmap Heap Scan sits between these, building a bitmap of matching pages before reading them in physical order, which suits queries returning a moderate number of scattered rows. Seeing a Seq Scan where you expected an Index Scan is the classic signal that your index is missing, unusable for this predicate, or being ignored because the planner thinks scanning is cheaper.

Interpreting Cost And Rows

Every plan node carries a cost estimate written as two numbers: the startup cost before the first row appears and the total cost to return all rows. These are arbitrary units, not milliseconds, but they let the planner compare alternative strategies. More useful is the rows estimate, the planner's guess at how many rows each node will emit. When you run EXPLAIN ANALYZE, you also get actual rows and actual time, letting you compare prediction against reality. A large gap between estimated and actual rows is the single biggest red flag in plan reading, because the planner makes every downstream decision based on those estimates. If it expects five rows but finds five hundred thousand, it may have chosen a nested loop join that becomes catastrophically slow at the real cardinality you observe.

Why The Planner Ignores Indexes

It frustrates newcomers when Postgres has a perfectly good index and refuses to use it, but the planner is often right. If a query returns a large fraction of the table, a sequential scan reading pages in order is genuinely faster than thousands of random index lookups jumping around the heap. The planner also skips indexes when a function wraps the column, such as filtering on lower of email when no matching expression index exists, because the stored index keys no longer match. Implicit type casts cause the same problem when you compare a column to a value of a different type. Outdated statistics are another culprit: if the table changed dramatically since the last analyze, the planner works from stale row estimates. Recognizing these patterns tells you whether to rewrite the query, add an expression index, or refresh statistics.

Statistics And The ANALYZE Command

The planner does not inspect your data at query time; it relies on precomputed statistics about column distributions, stored in the system catalogs. Autovacuum normally refreshes these in the background, but after a bulk import or a large migration the numbers can lag badly, leading to wildly wrong row estimates and poor plans. Running ANALYZE on a table recomputes the histograms, most-common-values lists, and distinct-value counts the planner depends on. For skewed columns where a few values dominate, you can raise the statistics target to capture finer detail. When a query suddenly performs worse than yesterday with no code change, stale statistics after a data shift is one of the first things to check, and a manual ANALYZE often restores the good plan immediately without any schema changes whatsoever.

Partial And Expression Indexes

Postgres lets you index a subset of rows or a computed value, two features Mongo developers rarely have. A partial index includes a WHERE clause, so an index on orders where status equals pending stores only active orders, staying tiny and fast even as completed orders pile up by the millions. This shrinks the index, speeds maintenance, and perfectly matches queries that always filter on that same condition. An expression index stores the result of a function, like lower of email, so case-insensitive lookups can finally use an index instead of scanning. These targeted indexes let you spend write overhead only where it pays off. Reaching for them instead of broad single-column indexes is a hallmark of someone who has internalized how the planner actually chooses access paths under real workloads.

A Practical Tuning Workflow

Put it together into a repeatable loop. Start by capturing the slow query and running EXPLAIN ANALYZE with the BUFFERS option to see disk and cache activity. Look first at the node consuming the most actual time, then check whether its row estimate matches reality. If estimates are off, run ANALYZE and re-check. If a Seq Scan dominates a selective filter, design an index matching the predicate, mindful of column order and whether a partial or expression index fits better. Add the index, re-run the plan, and confirm the access method changed and total time dropped. Always measure on representative data volumes, because a plan that looks fine on a thousand rows can invert completely on a million. This disciplined cycle of observe, hypothesize, change, and re-measure beats scattering indexes everywhere and hoping for improvement.