ANALYZE – PostgreSQL Database Maintenance

ANALYZE collects statistics about the database, i.e. contents of the tables and updates all statistics to pg_statistic system catalog.
Resulting Query planner uses pg_statistic system catalog to find efficient query execution plan.
ANALYZE acquiring only READ LOCK on target table, so that PostgreSQL can run parallel on same table.

Syntax:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Parameters:

Plain ANALYZE: Examines all tables in database.

ANALYZE table_name: Examines only provided table. In case of provides list of columns than ANALYZE only examines provided columns.

VERBOSE: Provides progress messages of the ANALYZE.

AutoVacuum daemon is take care of automatic analyzing of tables when they are first loaded to database, and throughout regular change in operations. When AutoVacuum is not configured than its recommended activity to perform ANALYZE as database maintenance task.

To simulate environment, Consider following examples on ANALYZE:

Ex 1 : Plain ANALYZE on Postgres default database.

Plain ANALYZE

Plain ANALYZE

 

Ex 2 : ANALYZE with VERBOSE on Postgres default database.

ANALYZE with VERBOSE

ANALYZE with VERBOSE

 

Ex 3 : ANALYZE specific table with VERBOSE on Postgres default database.

ANALYZE specific table with VERBOSE

ANALYZE specific table with VERBOSE

Note: Table demo is created for only to demonstrate above ANALYZE execution, No rows inserted into it. Thats why message tab shows 0 live rows and 0 dead rows.

Cheers!!

Leave a Reply