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.

Practice

1. What is the main purpose of the ANALYZE command in PostgreSQL?

easy

A. To create indexes on tables

B. To delete old data from tables

C. To backup the database

D. To collect statistics about tables for query planning

Solution

1. Step 1: Understand ANALYZE function

   The ANALYZE command collects statistics about the contents of tables.

2. Step 2: Purpose of statistics

   These statistics help the database decide the best way to run queries efficiently.

3. Final Answer:

   To collect statistics about tables for query planning -> Option D

4. Quick Check:

   ANALYZE = collect statistics [OK]

Hint: ANALYZE gathers table stats to improve query plans [OK]

Common Mistakes:

• Confusing ANALYZE with data deletion
• Thinking ANALYZE creates indexes
• Assuming ANALYZE backs up data

2. Which of the following is the correct syntax to run ANALYZE on a specific table named employees with detailed output?

easy

A. ANALYZE VERBOSE employees;

B. ANALYZE employees VERBOSE;

C. ANALYZE TABLE employees VERBOSE;

D. ANALYZE VERBOSE ON employees;

Solution

1. Step 1: Recall ANALYZE syntax

   The correct syntax is ANALYZE [VERBOSE] table_name; with VERBOSE before the table name.

2. Step 2: Check each option

   ANALYZE VERBOSE employees; matches the correct syntax exactly. Others have incorrect order or extra keywords.

3. Final Answer:

   ANALYZE VERBOSE employees; -> Option A

4. Quick Check:

   ANALYZE VERBOSE table_name; = ANALYZE VERBOSE employees; [OK]

Hint: VERBOSE goes right after ANALYZE before table name [OK]

Common Mistakes:

• Placing VERBOSE after table name
• Adding TABLE keyword (not used)
• Using ON keyword incorrectly

3. Given the following commands run in PostgreSQL:

ANALYZE VERBOSE employees;
ANALYZE sales;

What will be the output behavior?

medium

A. No output for either table

B. Detailed output for employees table, no output for sales table

C. Detailed output for both tables

D. Error because VERBOSE cannot be used with ANALYZE

Solution

1. Step 1: Understand VERBOSE effect

   Using VERBOSE with ANALYZE shows detailed progress messages for that command.

2. Step 2: Analyze commands separately

   The first command shows detailed output for employees. The second command runs normally without verbose output for sales.

3. Final Answer:

   Detailed output for employees table, no output for sales table -> Option B

4. Quick Check:

   VERBOSE shows details only when used [OK]

Hint: VERBOSE shows details only for that ANALYZE command [OK]

Common Mistakes:

• Expecting output for all ANALYZE commands
• Thinking VERBOSE causes errors
• Assuming no output means failure

4. You run ANALYZE VERBOSE mytable; but get an error: ERROR: relation "mytable" does not exist. What is the most likely cause?

medium

A. The table name is misspelled or does not exist

B. ANALYZE cannot be run with VERBOSE

C. You need to run ANALYZE on the whole database first

D. The database is in read-only mode

Solution

1. Step 1: Understand the error message

   The error says the relation (table) "mytable" does not exist, meaning PostgreSQL cannot find it.

2. Step 2: Identify common causes

   This usually happens if the table name is misspelled or the table was not created.

3. Final Answer:

   The table name is misspelled or does not exist -> Option A

4. Quick Check:

   Relation not found = wrong or missing table name [OK]

Hint: Check table name spelling if relation not found error appears [OK]

Common Mistakes:

• Thinking VERBOSE causes the error
• Assuming ANALYZE must run on whole database first
• Ignoring error details about relation

5. You want to improve query performance on a large table orders that changes frequently. Which approach using ANALYZE is best?

hard

A. Run ANALYZE VERBOSE orders; only once after creating the table

B. Run ANALYZE; on the whole database once a year

C. Run ANALYZE orders; regularly and use VERBOSE to monitor progress

D. Avoid running ANALYZE because it locks the table

Solution

1. Step 1: Consider table size and update frequency

   Large, frequently changing tables benefit from regular statistics updates to keep query plans accurate.

2. Step 2: Use ANALYZE regularly with VERBOSE

   Running ANALYZE orders; regularly updates stats. Adding VERBOSE helps monitor progress during analysis.

3. Step 3: Evaluate other options

   Running ANALYZE once a year is too infrequent. Running only once after creation misses ongoing changes. ANALYZE does not lock tables for long.

4. Final Answer:

   Run ANALYZE orders; regularly and use VERBOSE to monitor progress -> Option C

5. Quick Check:

   Regular ANALYZE keeps stats fresh for big tables [OK]

Hint: Regular ANALYZE keeps stats fresh; VERBOSE shows progress [OK]

Common Mistakes:

• Running ANALYZE too rarely
• Thinking ANALYZE locks tables extensively
• Ignoring VERBOSE usefulness for monitoring