EXPLAIN 비용 추정치와 행 수 읽기

Every node in an EXPLAIN plan shows a cost=startup..total pair. These are arbitrary planner units, not milliseconds. The planner uses them only to compare alternative plans and pick the cheapest one.

The two numbers tell different stories:

• Startup cost: work before the first row is returned (e.g. building a hash table)
• Total cost: work to return all rows

A node that must sort or aggregate everything has a high startup cost.

Run EXPLAIN on a simple query and read the cost on each line.

Each node shows rows=N, the planner's estimated number of rows it will emit. The planner derives this from table statistics gathered by ANALYZE.

Bad estimates lead to bad plans, so this number matters a lot.

The width=N field is the estimated average row size in bytes. Multiplied by rows it estimates memory and I/O needs, which influences whether sorts or hashes spill to disk.

EXPLAIN alone shows only estimates. Add ANALYZE to also run the query and see real timings and real row counts side by side.

Compare rows (estimate) with actual rows in EXPLAIN ANALYZE output. A large gap — say estimate 10 but actual 100000 — signals stale or insufficient statistics.

Estimates go stale when:

• Data changed a lot since the last ANALYZE
• Columns are correlated and the planner assumes independence
• The default statistics target is too low for skewed data

Run ANALYZE to recompute statistics for a table. Better stats produce better row estimates and therefore better plans.

For columns with skewed distributions, increase how many distinct values are sampled. A higher target means more accurate estimates at the cost of slightly slower ANALYZE.

Constants like seq_page_cost, random_page_cost, and cpu_tuple_cost scale how the planner weighs disk vs CPU. On SSDs, lowering random_page_cost makes index scans look cheaper.

You learned to read EXPLAIN's numbers:

• Cost is in arbitrary units; startup vs total cost differ
• rows and width are estimates from statistics
• Compare estimated vs actual rows to spot bad stats
• Fix drift with ANALYZE and a higher statistics target
• Cost constants tune disk-vs-CPU weighting