ANALYZE for statistics collection in PostgreSQL - Step-by-Step Execution

Concept Flow - ANALYZE for statistics collection

Start ANALYZE command

↓

Scan table rows

↓

Collect column statistics

↓

Update statistics metadata

↓

Statistics ready for query planner

↓

END

ANALYZE scans table data to collect statistics, then updates metadata used by the query planner to optimize queries.

Execution Sample

PostgreSQL

ANALYZE employees;
-- Collects statistics on 'employees' table
-- Helps planner choose best query plan

This command scans the 'employees' table and updates statistics for query optimization.

Execution Table

Step	Action	Details	Result
1	Start ANALYZE	Command issued on 'employees' table	Begin scanning rows
2	Scan rows	Read all rows or sample	Gather data distribution info
3	Collect stats	Calculate column stats (e.g. null fraction, distinct values)	Statistics computed
4	Update metadata	Write stats to system catalogs	Statistics stored for planner
5	Finish	ANALYZE completes	Query planner uses new stats

💡 All steps complete; statistics updated for query optimization

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
rows_scanned	0	All or sample rows read	Same	Same	Same
column_stats	None	Partial data	Calculated stats ready	Stored in metadata	Stored
metadata_updated	No	No	No	Yes	Yes

Key Moments - 2 Insights

Why does ANALYZE sometimes scan only a sample of rows instead of all?

To save time, ANALYZE often samples rows (see execution_table step 2). Sampling still gives good stats without full scan.

What happens if statistics are outdated?

The query planner may choose inefficient plans because it relies on old stats (execution_table step 5). Running ANALYZE updates stats to improve planning.

Visual Quiz - 3 Questions

Look at the execution_table, at which step are statistics actually calculated?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check the 'Collect stats' action in execution_table step 3

According to variable_tracker, when is metadata_updated set to 'Yes'?

A After Step 4

B After Step 2

C After Step 3

D At Start

💡 Hint

Look at the 'metadata_updated' row in variable_tracker after Step 4

If ANALYZE scanned no rows, what would happen to 'rows_scanned' in variable_tracker?

A It would show all rows read

B It would remain 0

C It would show partial data

D It would be null

💡 Hint

Refer to 'rows_scanned' variable in variable_tracker at Start and After Step 2

Concept Snapshot

ANALYZE command scans table data or samples it
Collects statistics like null fraction, distinct values
Updates system metadata with stats
Helps query planner choose efficient plans
Run periodically to keep stats fresh

Full Transcript

The ANALYZE command in PostgreSQL scans a table's data or a sample of it to collect statistics about the columns. These statistics include information such as how many null values exist and how many distinct values are present. After collecting this data, ANALYZE updates the system metadata where the query planner reads these statistics. This helps the planner make better decisions about how to execute queries efficiently. The process starts when the command is issued, then rows are scanned, statistics are calculated, metadata is updated, and finally the command finishes. Sampling is used to save time while still getting useful statistics. If statistics are outdated, the planner may choose poor query plans, so running ANALYZE regularly is important.