How Query Optimization Works

The same SQL query can be executed in dozens of genuinely different ways that all produce the identical result — scan the whole table or use an index, join two tables by looping or by hashing, filter before or after joining. A query optimizer (also called the planner) is the part of the database that picks one of those ways before the query actually runs. It's not following a fixed rulebook — it's estimating the cost of several candidate plans and picking the cheapest one it can find, which is exactly why the same query can get a different, sometimes worse, plan after nothing about the query itself changed.

The building blocks: how a single table gets read

For each table a query touches, the planner picks an access method:

• Sequential scan — read every row, check each one against the filter. Always available, and genuinely the right choice when a filter matches a large fraction of the table (reading everything sequentially can beat jumping around via an index once you're going to end up touching most of the table anyway).
• Index scan — use an index to jump straight to the rows that match a filter, then fetch each matching row from the table. Wins when the filter is selective — it narrows the result down to a small fraction of the table — and there's an index whose leading column(s) actually match the filter, covered in detail in SQL Index Advisor's equality-then-range column-ordering rule.
• Index-only scan — when every column the query needs is already present in the index itself, the planner can skip touching the underlying table row entirely. The closest thing to a free lunch in this list, and part of why a "covering" index (one that includes every column a specific query needs) is a real, deliberate tuning technique.

How the planner even knows which one is faster

Costs aren't guessed at query time from nothing — PostgreSQL's ANALYZE (run automatically by autovacuum as a table changes, or by hand) samples each table and records real statistics per column: how many distinct values exist, the most common values and how often each occurs, a histogram approximating the overall distribution, and what fraction of the column is NULL. The planner combines these to estimate selectivity — what fraction of rows a given WHERE condition will actually match — and from selectivity, estimates the number of rows (cardinality) each step of a candidate plan will produce. Every cost comparison the planner makes — sequential vs. index scan, which join algorithm, which join order — ultimately rests on these estimates being close to reality.

Which means the plan is only as good as the statistics behind it. Stale statistics — a table that grew or changed shape faster than autovacuum re-analyzed it — lead the planner to estimate cardinality wrong, sometimes badly enough to pick a genuinely bad plan for otherwise-reasonable SQL. This is exactly the gap EXPLAIN ANALYZE exposes: it shows both the planner's estimated row count at each step and the actual row count once the query really ran, side by side — a large, consistent gap between the two is one of the most reliable signs something's wrong, whether that's stale statistics, a predicate the planner can't estimate well, or a missing index entirely. SQL Explain Helper reads exactly this output and finds the step actually costing the most time.

Joining two tables: three algorithms, picked by the same cost logic

Once more than one table is involved, the planner also chooses how to join them:

• Nested loop — for each row on one side, look up matches on the other side. Cheap and often fastest when the inner side has an index on the join column, so each lookup is fast; without one, it degrades into rescanning the entire inner table once per outer row.
• Hash join — build an in-memory hash table from one side's join column, then scan the other side and probe the hash table for matches. A strong default for joining two large tables with no useful index, since it touches each side only once.
• Merge join — sort both sides by the join column, then walk both sorted lists in lockstep. Particularly attractive when one side is already sorted that way — from an index that provides the ordering for free, avoiding an explicit sort step.

None of these is universally "the fast one" — which the planner picks depends on table sizes, whether a useful index exists, and how much memory is available for a hash table or a sort, all fed by the same statistics that drive single-table scan choices. A missing index on a join column doesn't just slow down an index scan that could have happened — it can push the planner toward a fundamentally different, more expensive join algorithm for the whole query, which is why SQL Index Advisor specifically flags a query's JOIN ON columns as index candidates, not only its WHERE filters.

Join order, and why it explodes with more tables

For a query joining two tables there's essentially one join to plan. For five tables, the number of possible orders to join them in — and access methods and join algorithms to combine with each order — grows combinatorially. PostgreSQL's planner handles small numbers of tables by genuinely trying a near-exhaustive search across the reasonable join orders (favoring pairs that actually have a join condition connecting them) and costing each one out. Past a configurable threshold, an exhaustive search would itself take too long to be worth it, so the planner switches to a genetic algorithm that heuristically searches for a good — not provably optimal — join order instead. Either way, the join order actually chosen at runtime is frequently not the order the joins were written in the query, which is exactly why SQL JOIN Explained notes that writing order is for human readability, not a hint the planner has to follow.

Why the same query sometimes gets a worse plan

• Stale statistics — a table's shape changed (grew, or its data distribution shifted) faster than autovacuum re-analyzed it, so the planner's cardinality estimates no longer reflect reality.
• A non-sargable predicate — wrapping an indexed column in a function (WHERE LOWER(email) = ...) or applying an implicit type cast prevents the planner from using a plain index on that column at all, forcing a sequential scan regardless of how selective the condition actually is. Covered in more depth, with the full list of patterns that break this, in SQL Index Advisor.
• No index exists yet on a column that's become a filter or join key — the honest, most common cause, and the one worth checking first.
• A correlated statistics gap — the planner generally assumes columns are independent unless told otherwise (Postgres supports explicit extended statistics for exactly this case); two correlated filter conditions can make the real result far smaller than the planner's estimate, which assumed near-independence.

Try it yourself

SQL Explain Helper reads a real, already-executed EXPLAIN ANALYZE plan and ranks the actual bottleneck step by real time, not estimate. SQL Index Advisor works the other direction — reading a query's WHERE, JOIN, ORDER BY, and GROUP BY clauses to suggest a candidate index before you've run it against real data at all. Used together, one predicts and the other verifies. Both run entirely in your browser.