Split View: SQL 실행계획 완전 가이드: 옵티마이저는 어떻게 계획을 고르는가

The Complete Guide to SQL Execution Plans: How the Optimizer Chooses a Plan

• Introduction
• 1. From One Statement to a Plan
• 2. Statistics — How the Planner Sees the World
• 3. Selectivity Estimation and the Independence Assumption
• 4. The Cost Model — What the cost Number Is Built From
• 5. Comparing Paths — Why This Scan, Why This Join
• 6. Join-Order Search and GEQO
• 7. Correcting a Bad Estimate
• 8. When Plans Get Unstable — Prepared Statements and Generic Plans
• Quiz: Test Your Understanding
• Closing
• Sources
• Further Reading

Introduction

Most writing about execution plans teaches you how to read EXPLAIN output. This blog's own How to Read EXPLAIN ANALYZE is exactly that piece — it covers which order to read the nodes in, how loops multiplies through a plan tree, and what BUFFERS tells you about where the data actually came from.

This piece looks from the opposite side. Not the person reading the output, but the planner that produced it in the first place. What information does the planner actually have, what does it compute from that information, and when the computed result turns out to be wrong, what can we correct on our own end? This is usually exactly the point where someone who can already read a plan competently still gets stuck when it comes to tuning one. If you don't know why a particular plan came out the way it did, you also don't know what to change to get a different one — you end up guessing at hints and session settings instead of fixing the actual cause underneath.

The reference engine is PostgreSQL 18, and every default value quoted here was confirmed against the PostgreSQL 18 documentation. MySQL's optimizer differs in both its cost model and its statistics structures, so this piece deliberately does not mix the two together. MySQL execution plans deserve, and would need, an entirely separate piece of their own.

1. From One Statement to a Plan

A single line of SQL passes through four stages before it becomes a result.

1. Parser — checks the grammar and builds a parse tree. At this stage, PostgreSQL checks little more than whether the tables you referenced actually exist.
2. Rewriter — expands views out into their real underlying definitions, and applies any rules. Query a view, and this is the stage where it turns back into the original query against the base tables.
3. Planner/optimizer — builds the possible execution paths and calculates a cost for each one, then picks the cheapest. This is the subject of this piece.
4. Executor — walks the chosen plan tree and actually reads the data.

One important fact belongs right here at the start. The planner never looks at your data. What the planner looks at is a summary of the data — statistics. When those statistics drift away from reality, even the most sophisticated cost model produces a wrong answer, because a good model fed a bad input still returns a bad output. The overwhelming majority of execution-plan problems are not flaws in the cost model at all; they are a gap between the statistics and the actual state of the table.

2. Statistics — How the Planner Sees the World

ANALYZE pulls a sample from a table, builds statistics from it, and stores those statistics in pg_statistic. The human-readable form of the same data is the pg_stats view.

SELECT attname, null_frac, n_distinct,
       most_common_vals, most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'created_at');

Each column in that view means something specific:

• null_frac — the fraction of rows where the value is NULL. This is where the selectivity of an IS NULL condition comes from.
• n_distinct — the number of distinct values. A positive number is the count itself; a negative number is a ratio relative to the row count. -1 means every value in the column is unique.
• most_common_vals / most_common_freqs — the list of most common values and how frequently each one occurs. This pair is decisive for any column whose value distribution is skewed rather than uniform.
• histogram_bounds — the boundaries that divide the remaining values, excluding the most common ones, into buckets of roughly equal frequency. This is where the selectivity of a range condition comes from.
• correlation — the correlation coefficient between a column's logical value order and its physical row order on disk. The closer this is to 1, the more an index scan's random access pattern collapses into something close to sequential access, which drives the cost down sharply. It also doubles as the metric that determines whether a BRIN index will actually be effective.

The sample size is controlled by default_statistics_target. Per the PostgreSQL 18 documentation, the default value is 100; a larger value produces a longer most-common-values list and more histogram buckets, which makes estimates more accurate but also lengthens both ANALYZE time and planning time. It can also be tuned per column instead of globally.

-- Increase the sample only for a column with a badly skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

ALTER TABLE ... SET STATISTICS takes only a SHARE UPDATE EXCLUSIVE lock, so it blocks neither reads nor writes — a useful contrast with the many other forms of ALTER TABLE that take a full ACCESS EXCLUSIVE lock instead.

Statistics get refreshed by autovacuum's analyze task. Per the PostgreSQL 18 documentation, the analyze threshold is computed as autovacuum_analyze_threshold (default 50 tuples) plus autovacuum_analyze_scale_factor (default 0.1, i.e., 10% of the table) multiplied by the table's row count. On a table with 100 million rows, that means 10 million rows have to change before an analyze fires. This is exactly why statistics go stale on very large tables, and lowering the scale factor on a per-table basis is the standard fix.

3. Selectivity Estimation and the Independence Assumption

The value the planner computes from statistics is selectivity: what fraction of all rows a condition lets through. Multiply that fraction by the table's row count and you get the estimated row count that flows into every cost calculation downstream.

The selectivity of a single condition is comparatively accurate. If status = 'PAID' shows up in the most-common-values list, the planner uses that value's recorded frequency directly; if it doesn't appear there, the planner distributes the remaining probability evenly across whatever values are left.

The trouble starts once there are two or more conditions. By default, the planner assumes the conditions are statistically independent of each other and simply multiplies their individual selectivities together. The instant that assumption breaks down, the estimate collapses along with it.

-- City and postal code are, in practice, functionally dependent on each other
SELECT * FROM addresses
WHERE city = '서울특별시' AND postal_code = '06236';

If city has a selectivity of 0.2 and postal_code has a selectivity of 0.0001, the planner multiplies them and computes 0.00002. But in reality, once the postal code is fixed, the city is automatically determined along with it — the two are not independent at all — so the true selectivity is close to 0.0001 on its own. The planner ends up expecting a fifth as many rows as will actually come back, a five-fold underestimate, and that error doesn't stay contained where it started. It rides upward through every join above it, compounding like a snowball as it climbs.

This is the single most common reason execution plans go wrong. On the surface it looks like "the planner picked a Nested Loop, and it turned out slow because there were more rows than expected," but the real root cause was never the choice of join method at all — it was a row-count estimate that had already gone bad several steps further down the tree. Section 7 covers how to correct it.

4. The Cost Model — What the cost Number Is Built From

The cost value that EXPLAIN shows you is not a unit of time. It is a relative, abstract unit, pegged so that one sequential page read costs exactly 1.0. The planner assembles this number out of five cost constants, all of which default to the values documented for PostgreSQL 18.

The single most practically important number here is random_page_cost's default of 4.0. That value encodes an assumption inherited from the era of spinning disks: that a random access costs four times what a sequential access costs. On SSDs and NVMe storage, that ratio is nowhere near as large. Leave the default in place on modern storage, and the planner will rate index scans as more expensive than they really are, and lean toward sequential scans far more often than it should. Lowering it to somewhere between 1.1 and 2.0 on SSD-backed storage is a widely used adjustment, but always compare plans before and after across a representative set of queries before you change it — this single value influences the entire plan space, and getting it wrong in one direction can quietly break other, unrelated queries that were previously fine.

effective_cache_size deserves the same scrutiny. Its default is 4GB, and — this part is easy to misunderstand — the value never actually allocates any memory. It only changes the planner's assumption about how much data, including whatever the operating system itself is caching, is likely to already be sitting in memory. If it's left set far smaller than the server's real memory, the planner assumes an index scan will hit disk constantly and leans toward a sequential scan instead, even on a machine with plenty of RAM to spare.

The costs around parallel execution work the same way. parallel_setup_cost defaults to 1000, and parallel_tuple_cost defaults to 0.1. Because launching a worker carries a fixed cost of 1000 before it does anything useful at all, small queries never even become candidates for a parallel plan in the first place. The minimum table size for a parallel sequential scan is governed by min_parallel_table_scan_size, which defaults to 8MB; on the index side, the equivalent is min_parallel_index_scan_size, defaulting to 512kB.

5. Comparing Paths — Why This Scan, Why This Join

For each table, the planner builds every access path it can, and then combines those paths together into join paths for the query as a whole. At each step, it keeps the cheapest path, plus any other path that carries a genuinely useful property — "returns rows already sorted in the order I need," for instance — even when that alternate path costs a little more on its own.

For scan paths, the deciding factor is the return ratio: what fraction of the table's rows the condition actually returns. An index scan reads the index and then visits the matching table rows at effectively random locations. As the return ratio climbs, the number of random accesses climbs with it, and past a certain point it becomes cheaper to just read the entire table once, in physical order, instead. That's why a Seq Scan is very often the correct answer, not a planner failure — on a small table, or on a query that returns most of the table's rows anyway, forcing an index only makes things slower.

In the middle ground sits the Bitmap Heap Scan. It reads the whole index first, collects the matching block numbers into an in-memory bitmap, and then reads the table in block-number order rather than in index order. It's a compromise that turns what would have been random access into something close to sequential access, and it shows up constantly whenever the return ratio sits somewhere in between — too high for a plain index scan to stay cheap, too low for a full sequential scan to make sense.

For join paths, the planner chooses among three strategies.

• Nested Loop — probes the inner side once for every single row on the outer side. This is the best possible choice when the outer result is small and the inner side has a good index to probe with. It becomes the worst possible choice when the outer row-count estimate is wrong, because the cost of being wrong gets multiplied by every single outer-loop iteration. This is the single most common culprit behind execution-plan incidents.
• Hash Join — builds an in-memory hash table from one side and scans the other side against it. It only works for equality joins, but when the hash table fits inside work_mem, it is extremely fast. When it doesn't fit, it spills to disk in batches instead.
• Merge Join — sorts both sides and merges them together in lockstep. This is favorable when an input is already sorted, typically because it came from an index scan; otherwise, the plan has to pay the cost of an explicit sort first.

work_mem defaults to 4MB. The critical detail is that this limit applies per sort or hash operation, not per connection. A single query that contains several sorts and hashes can use that many multiples of work_mem at once, all within one connection. Hash-related operations get some additional headroom on top of that: they can use up to work_mem multiplied by hash_mem_multiplier, which defaults to 2.0.

6. Join-Order Search and GEQO

There are only two possible orders for joining two tables, but that number explodes combinatorially once you get to ten. The planner searches the space of join orders using dynamic programming, and once the table count climbs high enough, that exhaustive search itself becomes too expensive to run.

PostgreSQL manages this with three levers, all documented with the following defaults.

• from_collapse_limit — defaults to 8. This governs whether a subquery gets flattened up into the parent query. If flattening it would push the number of FROM items past this value, PostgreSQL leaves it as a separate subquery instead.
• join_collapse_limit — defaults to the same value as from_collapse_limit. This governs whether explicit JOIN syntax gets flattened into a single flat list of tables that the planner is free to reorder. Set this to 1, and the planner stops reordering joins altogether, executing them in exactly the order you wrote.
• geqo_threshold — defaults to 12. Once the number of FROM items reaches this value, the planner abandons exhaustive search entirely and switches to a genetic algorithm (GEQO) to find a join order instead.

Because GEQO is a probabilistic search, the same query can produce a different plan on every single run. If a reporting query joining more than a dozen tables takes 3 seconds one day and 40 seconds the next, with nothing else about the system having changed, GEQO is a reasonable first suspect. The way to confirm it is to turn geqo off temporarily and compare both the planning time and the execution time with it off.

-- Experiment within this session only
SET geqo = off;
SET join_collapse_limit = 20;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET geqo;
RESET join_collapse_limit;

If planning time explodes to several seconds with GEQO off, that confirms the join really is too large for exhaustive search to handle, and the right fix at that point is to break the query apart or materialize an intermediate result, rather than trying to force GEQO into behaving more predictably than it can.

7. Correcting a Bad Estimate

PostgreSQL solves the independence-assumption problem from Section 3 with extended statistics. CREATE STATISTICS builds statistics on a combination of columns, separately from the per-column statistics ANALYZE collects automatically. The documentation names three kinds it supports:

• dependencies — records functional dependencies between columns. This is what prevents the severe underestimate that happens when several conditions turn out to be effectively redundant with each other, the way city and postal code are.
• ndistinct — records the number of distinct values across a combination of columns together. This corrects estimates for a GROUP BY that spans multiple columns, which would otherwise be estimated as though each column varied completely independently of the others.
• mcv — records a most-common-values list for a combination of columns. This accurately captures cases where one particular combination of values shows up far more often than the individual column frequencies alone would suggest.

-- Tell the planner about the dependency between city and postal code
CREATE STATISTICS stat_addr_city_postal (dependencies, mcv)
  ON city, postal_code FROM addresses;

-- The statistics stay empty until ANALYZE runs
ANALYZE addresses;

CREATE STATISTICS takes only a SHARE UPDATE EXCLUSIVE lock, so running it in production blocks neither reads nor writes. But it's empty the instant it's created, so ANALYZE has to run afterward without fail before it does anything useful at all. Every example in the documentation itself pairs CREATE STATISTICS with an ANALYZE right after it, for exactly this reason.

Bad estimates on expressions are just as common. A condition like date_trunc('month', a) = ... is something the planner cannot estimate at all from ordinary column statistics — there is simply no statistic that describes the distribution of a computed value like that. Expression statistics solve this the same way extended statistics do.

CREATE STATISTICS stat_events_month
  ON date_trunc('month', occurred_at) FROM events;
ANALYZE events;

The correction process, laid out in order, looks like this. First, use EXPLAIN ANALYZE to find the node with the largest gap between the estimated row count and the actual one. If that node's condition is a single column, widen the sample with SET STATISTICS. If it's a multi-column condition, build extended statistics with CREATE STATISTICS. If it's an expression, build expression statistics or an expression index. Only once all three of these have been tried and the problem still remains do you finally start restructuring the query itself.

8. When Plans Get Unstable — Prepared Statements and Generic Plans

A query that was fast yesterday suddenly turns slow today, and running the exact same SQL by hand through EXPLAIN shows it fast again. The prime suspect for this specific symptom is the generic plan of a prepared statement.

A prepared statement can build a plan without ever knowing the actual parameter values. A plan built fresh each time, with the real values already in hand, is called a custom plan. A plan built once without knowing the values, and then reused across many later executions, is called a generic plan. A generic plan saves the cost of re-planning on every execution, but on a column with a skewed value distribution, that same reused plan can turn out to be the worst possible plan for one particular value that shows up later.

This behavior is controlled by plan_cache_mode. The allowed values are auto (the default), force_custom_plan, and force_generic_plan. Under the default auto, PostgreSQL builds a handful of custom plans first, averages their cost, compares that average against the generic plan's cost, and decides automatically from there which kind to keep using going forward.

If you want to see the generic plan directly, without supplying any parameter values at all, PostgreSQL 16 and later offers the GENERIC_PLAN option for exactly this.

EXPLAIN (GENERIC_PLAN)
SELECT * FROM orders WHERE tenant_id = $1 AND status = $2;

 Bitmap Heap Scan on orders  (cost=25.41..1290.88 rows=512 width=124)
   Recheck Cond: (tenant_id = $1)
   Filter: (status = $2)
   ->  Bitmap Index Scan on idx_orders_tenant  (cost=0.00..25.28 rows=1024 width=0)
         Index Cond: (tenant_id = $1)

Once you've confirmed this is actually the symptom, there are two ways to respond. Either set plan_cache_mode = force_custom_plan for that session or application, so every execution always builds a plan around the real values, or strengthen the statistics on whatever column has the skewed distribution, so the generic plan itself gets good enough that the distinction stops mattering. The first option pays a re-planning cost on every single execution, which can itself become a real burden for short queries running thousands of times per second.

JIT compilation is a similar source of instability. jit defaults to on, and jit_above_cost defaults to 100000. Once the estimated cost of a query crosses that threshold, JIT compilation kicks in — but on a short query whose cost the planner happened to overestimate, the time spent compiling can end up larger than the time spent actually executing, which is a real reversal, not just a hypothetical one. Check the JIT section of EXPLAIN (ANALYZE) output to see the compilation time directly.

Quiz: Test Your Understanding

Nested Loop  (cost=0.86..3421.55 rows=12 width=88)
             (actual time=0.09..48210.33 rows=184032 loops=1)
  ->  Index Scan using idx_a on orders o
        (cost=0.43..118.20 rows=4 width=40)
        (actual time=0.03..92.11 rows=61344 loops=1)
        Index Cond: ((tenant_id = 42) AND (status = 'PAID'::text))
  ->  Index Scan using idx_b on order_items i
        (cost=0.43..825.31 rows=3 width=48)
        (actual time=0.30..0.78 rows=3 loops=61344)
        Index Cond: (order_id = o.id)

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Closing

Spend long enough tuning execution plans and the conclusion gets simple. The cost model is, for the most part, right — what's wrong is almost always the input. Most of the time, someone hands the planner bad statistics and then blames the planner for the plan it built from them. Twisting a plan into shape with a blunt lever like enable_nestloop = off is tempting precisely because the effect is immediate and visible, but it's a stopgap that collapses again the moment the data distribution shifts even slightly, because it never touched the actual cause underneath.

The order is always the same. Find the node with the largest gap between estimate and reality, work out whether that gap comes from too small a sample, an unmodeled correlation, or an un-estimable expression, and correct it with whichever statistics tool actually addresses that specific cause. Only whatever problem still remains after that gets handled through query structure or session parameters — never before.

You can run every piece of SQL from this piece yourself in the Postgres Playground.

Sources

• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (retrieved 2026-08-15)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (retrieved 2026-08-15)
• PostgreSQL 18, CREATE STATISTICS: https://www.postgresql.org/docs/18/sql-createstatistics.html (retrieved 2026-08-15)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (retrieved 2026-08-15)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (retrieved 2026-08-15)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (retrieved 2026-08-15)
• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (retrieved 2026-08-15)

Further Reading

• Previous: The Complete Guide to PostgreSQL Indexes — the index lifecycle
• Next: The Complete Guide to Transaction Isolation Levels — the operational contract behind isolation levels
• How to Read EXPLAIN ANALYZE — the order to read the output in
• The Volcano Model and Vectorized Execution — how the executor actually runs a plan
• Postgres Playground — pull your own execution plans directly
• DuckDB Playground — compare against an analytical engine's plans