In PostgreSQL, the ANALYZE command collects the statistics about a database, table, or table’s columns for the query planner. Afterward, the query planner utilizes that data to yield efficient/appropriate execution plans for the Postgres queries.

This post will present a detailed working mechanism and usage of the ANALYZE command through practical examples. So, let’s start.

Key Points

Some key points regarding the ANALYZE command are listed below that will help you to understand the working of the ANALYZE command in a better way:

• Postgres' ANALYZE command deals with the table or column contents; however, it does not read or update indexes.
• While ANALYZE runs, other queries may access the table because the ANALYZE command does not block the table.

When to Use ANALYZE Command?

ANALYZE is preferable in the following scenarios:

• When a table's contents have changed significantly, run the ANALYZE command. For instance, adding, updating, or deleting a few percent of records in a particular table.
• In order to generate the optimal query plan, run the ANALYZE command before or after adding an index to a particular table.

How to Read the ANALYZE Command Output?

By using the VERBOSE option along with the ANALYZE command, you can emit progress messages indicating which table is currently being processed. Additionally, it assists us in printing the table’s stats.

How to Analyze All Databases Using the ANALYZE Command in Postgres?

If the ANALYZE command gets executed successfully, it will return “ANALYZE”. Let’s learn how to use the ANALYZE command to get the statistics of all the databases. Firstly open the SQL SHELL and run the below command:

ANALYZE;

How to Analyze a Specific Database Using the ANALYZE Command in Postgres?

In Postgres, databases, tables, and columns are analyzed hierarchically using the ANALYZE command. So, to get the stats of some specific database, firstly, we must connect to that database, and then we can execute the ANALYZE command.

Example: How to Get Stats of Databases Using ANALYZE Command?

This example shows how to get stats of the databases using the ANALYZE command in Postgres:

Step #1: List Databases

Let’s execute the \l command to get the list of databases:

\l;

Step #2: Connect to Database

Suppose we want to get the stats of the “example” database. To do that, firstly, we will establish a connection with the selected database using the “\c” command:

\c example;

Step #3: Get Stats of Specific Database

We have successfully established a connection with the targeted database, i.e., “example”. Now, we can get the stats of the “example” database using the ANALYZE command:

ANALYZE;

How to Analyze a Table Using ANALYZE Command in Postgres?

To get the stats of a specific table, use the ANALYZE Command as follows:

ANALYZE tab_name;

Example: How to Get Table’s Stats Using ANALYZE?

Let’s execute the below command to see the tables available in the example database:

\d

Suppose we want to get the stats of the article_details table. You can accomplish this by executing the ANALYZE command as follows:

ANALYZE article_details;

How to Analyze Columns Using ANALYZE Command in Postgres?

Follow the below syntax to learn how to get the stats of columns in Postgres:

ANALYZE tab_name (col_1, col_2, …, col_n);

Here, tab_name represents the targeting table while col_1, col_2, … col_n are the columns whose data needs to be collected/analyzed.

Example: How to Get Stats of Columns Using ANALYZE Command?

In this example, we will get the stats of two columns: article_title and published_date, using the ANALYZE command as follows:

ANALYZE article_details(article_title, published_date);

This way, you can get the statistics of any database, table, or column using the ANALYZE command.

What is VERBOSE, and How to Use it With the ANALYZE Command?

By default, ANALYZE command does not display any processing on the screen. However, if the VERBOSE option is used with the ANALYZE command, then the output will be in a more detailed format.

Example: How to Use VERBOSE Option With ANALYZE Command?

In this example, we will get the detailed statistics of the article_details table using the ANALYZE command:

ANALYZE VERBOSE article_details;

The VERBOSE option retrieves the detailed statistics of the targeted table:

• The output indicates that there are twelve rows in the article_details table. All twelve rows are live, and there are zero dead rows in the selected table.

How to Use VACUUM With ANALYZE Command?

In Postgres, the VACUUM command is used to reclaim the useless space by removing the dead/old records. The VACUUM and ANALYZE commands can be used combinedly to analyze the tables while vacuuming:

VACUUM VERBOSE ANALYZE;

This is how the VACUUM command works with the ANALYZE command.

Conclusion

PostgreSQL offers a convenient command named ANALYZE that collects the statistics about a database, table, or table’s columns for the query planner. The collected data can then be used by the query planner to yield efficient/appropriate execution plans for the Postgres queries. This post considered multiple use cases of the ANALYZE command through practical examples.