ANALYZE for statistics collection in PostgreSQL

Introduction

ANALYZE helps the database learn about the data. It collects information to make queries faster and smarter.

After adding or changing many rows in a table.

Before running complex queries to get better performance.

When the database seems slow or chooses a bad plan.

After loading a large amount of data into a table.

Syntax

PostgreSQL

ANALYZE [VERBOSE] [table_name];

You can run ANALYZE on the whole database or just one table.

VERBOSE shows details about what ANALYZE is doing.

Examples

Collect statistics for all tables in the current database.

PostgreSQL

ANALYZE;

Collect statistics for all tables and show detailed progress.

PostgreSQL

ANALYZE VERBOSE;

Collect statistics only for the 'customers' table.

PostgreSQL

ANALYZE customers;

Collect statistics for the 'orders' table and show details.

PostgreSQL

ANALYZE VERBOSE orders;

Sample Program

This creates a table, adds some rows, then runs ANALYZE with VERBOSE to collect statistics and show progress.

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
INSERT INTO products (name, price) VALUES ('Pen', 1.20), ('Notebook', 2.50), ('Eraser', 0.80);
ANALYZE VERBOSE products;

Important Notes

ANALYZE does not lock the table, so other users can still read and write while it runs.

Regularly running ANALYZE helps keep query speed good as data changes.

PostgreSQL also runs ANALYZE automatically sometimes, but manual runs can help after big changes.

Summary

ANALYZE collects data about tables to help the database plan queries better.

You can run it on the whole database or specific tables.

Use VERBOSE to see what ANALYZE is doing step-by-step.