0Pricing
PostgreSQL Performance & Query Optimization · 강의

실제 행 수와 추정치 검증

EXPLAIN ANALYZE에서 계획된 카디널리티와 실제 카디널리티를 비교하여 통계 수정이 적용되었는지 확인하는 방법을 배웁니다.

실제 행 수와 추정치 검증은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Validate Estimates?

When you fix bad statistics with CREATE STATISTICS or by raising default_statistics_target, you need proof that the planner now estimates cardinalities correctly.

The single best tool is EXPLAIN ANALYZE. It runs the query and reports, for every plan node, both:

  • the planner's estimated row count (rows=)
  • the actual row count observed at runtime (actual rows=)

If estimate and actual are close, your statistics fix landed. If they diverge by 10x or 100x, the planner is still flying blind.

Reading the Two Numbers

Plain EXPLAIN shows only estimates. To get actuals you must execute the query with ANALYZE.

Each node line looks like this:

  • rows=120 — the estimate
  • actual ... rows=11500 — what really happened

A ~100x gap on the orders scan is exactly the kind of misestimate that leads to a nested loop where a hash join would have been far cheaper.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'shipped'
  AND ship_country = 'DE';

The estimate / actual Ratio

The metric to watch is the estimation ratio per node:

  • ratio = actual_rows / estimated_rows

Interpretation:

  • ~1.0 — healthy, the planner sees the data correctly
  • > 10 or < 0.1 — a real misestimate worth investigating
  • > 100 — almost always the root cause of a bad plan

Always compare at the node where the filter or join actually applies, not just the top-level row count.

Always Multiply by loops

The most common reading mistake: actual rows is reported per loop, not as a total.

If a node shows actual ... rows=5 loops=2000, the true number of rows produced is 5 × 2000 = 10000.

Compare the planner's estimate against actual_rows × loops, never against the raw per-loop figure. Forgetting this makes a healthy inner-loop node look like a wild misestimate.

EXPLAIN ANALYZE
SELECT o.*, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'EU';

A Healthy Plan Looks Like This

After a good statistics fix, estimate and actual should line up on the driving nodes. Read this fragment carefully:

  • Seq Scan estimate rows=11200 vs actual rows=11500 → ratio 1.03, healthy
  • The aggregate on top also matches closely

This is the outcome you are validating for: the numbers in parentheses on the left agree with the numbers after actual on the right.

-- Reading EXPLAIN ANALYZE output:
-- Seq Scan on orders
--   (cost=0.00..2310.0 rows=11200 width=64)
--   (actual time=0.02..14.3 rows=11500 loops=1)
--   Filter: (status = 'shipped')

Correlated Columns: The Classic Trap

The planner assumes columns are independent and multiplies their selectivities. When columns are correlated, the combined estimate collapses far below reality.

Example: most rows where city = 'Berlin' also have country = 'DE'. The planner multiplies the two fractions and predicts a tiny result, but the actual count is large.

This is precisely where multivariate extended statistics repair the estimate.

EXPLAIN ANALYZE
SELECT *
FROM addresses
WHERE city = 'Berlin'
  AND country = 'DE';

Create and Refresh Extended Statistics

To teach the planner about correlation, create an extended statistics object with the dependencies kind, then analyze the table so the new statistics are populated.

Critically: CREATE STATISTICS alone does nothing until ANALYZE runs. Validation is meaningless if you skip the refresh.

CREATE STATISTICS addr_city_country (dependencies)
  ON city, country
  FROM addresses;

ANALYZE addresses;

The Before/After Discipline

Validation is a comparison, so capture two snapshots:

  • Before: run EXPLAIN ANALYZE and record the estimate vs actual on the filtered node (e.g. rows=40 vs actual rows=9000)
  • After: create + analyze the statistics, then re-run the identical query

The fix landed only if the estimate moved toward the actual (e.g. now rows=8700 vs actual rows=9000). A changed plan shape is a bonus, not the proof — the estimate convergence is the proof.

Inspect What the Planner Now Knows

You can confirm extended statistics were computed without re-running the query, by reading pg_stats_ext.

If the dependency degrees are populated (close to 1.0 for strongly correlated pairs), ANALYZE did its job and the planner has the data it needs.

SELECT statistics_name,
       attnames,
       dependencies
FROM pg_stats_ext
WHERE tablename = 'addresses';

Use BUFFERS and Format for Clarity

For serious validation, add options to the command:

  • BUFFERS — shows shared block hits/reads, exposing I/O caused by a misestimated scan
  • FORMAT JSON — gives machine-readable Plan Rows and Plan Actual Rows fields you can diff programmatically
  • SETTINGS — echoes non-default planner settings that may be skewing the test

JSON output is ideal when scripting regression checks across many queries.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON)
SELECT *
FROM addresses
WHERE city = 'Berlin'
  AND country = 'DE';

Don't Be Fooled by Rows Removed by Filter

When the estimate still looks off, check the Rows Removed by Filter line. A node can scan millions of rows yet return few, and the estimate you validate is about returned rows.

Also confirm you are validating a representative parameter value. A query that is healthy for country = 'DE' may misestimate badly for a rare value — per-value MCV skew is normal and may need a higher statistics target rather than extended statistics.

ALTER TABLE addresses
  ALTER COLUMN country SET STATISTICS 1000;

ANALYZE addresses;

Quick Check

Test your understanding of validating estimates against actual rows.

Recap

To confirm a statistics fix landed, validate estimates against actuals with discipline:

  • Run EXPLAIN ANALYZE and read rows= (estimate) against actual ... rows= per plan node.
  • Compute the ratio; treat >10x or <0.1x as a real misestimate, >100x as a likely root cause.
  • Always multiply actual rows by loops to get the true total.
  • For correlated columns, create extended statistics (dependencies/ndistinct/mcv) and then ANALYZE — creation alone does nothing.
  • Use a before/after snapshot: the proof is the estimate converging toward the actual, not merely a changed plan.
  • Reach for BUFFERS, FORMAT JSON, and pg_stats_ext to make validation rigorous and scriptable.

자주 묻는 질문

“실제 행 수와 추정치 검증” 강의는 무료인가요?

네 — “실제 행 수와 추정치 검증” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“실제 행 수와 추정치 검증”에서 뭘 배우나요?

EXPLAIN ANALYZE에서 계획된 카디널리티와 실제 카디널리티를 비교하여 통계 수정이 적용되었는지 확인하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 4번째 강의입니다.

“실제 행 수와 추정치 검증” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 플래너가 행 수를 추정하는 방식
  2. 상관된 열을 위한 다변량 통계
  3. MCV 및 고유값 수 보정
  4. 실제 행 수와 추정치 검증
← PostgreSQL Performance & Query Optimization(으)로 돌아가기