Analyze the table. Columns, database in PostgreSQL

ANALYZE command is used to collects the statistics about a database, table, or table’s columns for the query planner. The updated stats data used by the query planner to use the efficient and more appropriate execution plans for the Postgres SQL queries. 

Check the analyze statistics date in PostgreSQL

testdb=# SELECT schemaname, relname, last_autoanalyze, last_analyze FROM pg_stat_all_tables where schemaname='public';
 schemaname |    relname    |         last_autoanalyze         | last_analyze
------------+---------------+----------------------------------+--------------
 public     | film_category | 2023-03-27 21:46:33.060717+05:30 |
 public     | actor         | 2023-03-27 21:46:33.010094+05:30 |
 public     | inventory     | 2023-03-27 21:46:33.074486+05:30 |
 public     | rental        | 2023-03-27 21:46:33.213631+05:30 |
 public     | country       | 2023-03-27 21:46:33.062717+05:30 |
 public     | address       | 2023-03-27 21:46:33.021552+05:30 |
(6 rows)

Analyze the database in PostgreSQL

Analyze all the object present in the database in one go. use analyze the database option. It will update all the objects stats.

--Switch to the database
\c databasename
--Type analyze command then press enter, it will analyze whole database objects
analyze;

Example:
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=#
testdb=# analyze verbose;
INFO:  analyzing "public.test"
INFO:  "test": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
INFO:  analyzing "pg_catalog.pg_type"
INFO:  "pg_type": scanned 16 of 16 pages, containing 661 live rows and 0 dead rows; 661 rows in sample, 661 estimated total rows
INFO:  analyzing "public.actor"
INFO:  "actor": scanned 2 of 2 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows
ANALYZE

Analyze the table in PostgreSQL:

Update the stats of the table in PostgreSQL. Verbose is used with analyze command to give the info regarding the table stats.

analyze table_name'

Example:
testdb=# analyze verbose actor;
INFO:  analyzing "public.actor"
INFO:  "actor": scanned 2 of 2 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows
ANALYZE
testdb=#

Analyze the table columns in PostgreSQL Database:

Analyze the table actor and update stats of column (first_name, last_name)

analyze table_name(columname1, columnname2 ..)

Example:
testdb=# analyze verbose actor(first_name,last_name);
INFO:  analyzing "public.actor"
INFO:  "actor": scanned 2 of 2 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows
ANALYZE
testdb=#