0Pricing
PostgreSQL Performance & Query Optimization · レッスン

推定値と実際の行数の検証

EXPLAIN ANALYZEで計画上と実際のカーディナリティを比較し、統計情報の修正が反映されたことを確認します。

「推定値と実際の行数の検証」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応の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時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「推定値と実際の行数の検証」で何を学びますか？

EXPLAIN ANALYZEで計画上と実際のカーディナリティを比較し、統計情報の修正が反映されたことを確認します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応の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とN-Distinctの補正
  4. 推定値と実際の行数の検証
← PostgreSQL Performance & Query Optimizationに戻る