Postgres ANALYZE collects statistical information about a database or table or it can analyze the columns of a table as well. The statistical data provided by ANALYZE command is used by the query planner to plan the possible query plan. Moreover, the ANALYZE is used in a situation where records are modified/altered within tables.
This article presents a brief working mechanism and usage of the Postgres ANALYZE command.
Prerequisites
For using this Postgres command, you must have the following set of prerequisites on your system.
- Ensure the Postgres service is active and in a running state.
- A Postgres database, table, and several columns must be on board to apply.
How to use Postgres ANALYZE
As discussed earlier, Postgres ANALYZE can be used to get statistical information about databases, tables, or columns. So, this section enlists all the above-mentioned possibilities to use ANALYZE in Postgres.
Getting statistics of all databases
With the help of ANALYZE command, you can get the stats of all the Postgres databases. For this, log in to your Postgres console and then type ANALYZE to execute the command. Upon successful execution of the command, it would return ANALYZE.
Getting statistics of a specific database
The ANALYZE command works in a hierarchical manner, from database to tables and tables to several columns. To get the statistics of any database, you just have to use the ANALYZE keyword (while logging in to a database).
The image below shows that we are logged into the linuxhint database, and the ANALYZE keyword is applied to it. The ANALYZE command would examine all the columns of tables to look for the occurrence of any update.
Getting the statistics of a table
Like targeting a specific database, the ANALYZE command can be used for specific tables as well. The syntax to apply this command to a specific table is provided below.
Syntax
For instance, we have a table named staff in a database named linuxhint. The ANALYZE command can be applied in the following way.
Note: Make sure you are logged into a correct database and applying the command on an existing table.
Getting statistics of columns
Similarly, specific columns of a table can also be examined using the ANALYZE command. The syntax to apply ANALYZE command on the column name is provided below.
- <table-name> denotes the table that you are targeting
- And the column1, column2, … refers to the column names that are associated with a table.
For instance, the following command will firstly look for the existence of table (staff) and then apply the ANALYZE command on columns named name and designation.
Using the verbose option with ANALYZE
The verbose option of Postgres presents the output in a more detailed form. If the verbose option is not exercised, then the command performs the action at the back end without showing any processing on the screen. The verbose can be used with each analyze command. Like in our case, the command provided below practices verbose with Postgres analyze on the linuxhint database.
Using ANALYZE with the VACUUM command
The vacuum command is practiced for vacating useless space by removing the old records or the older versions of the records. The vacuum command can be used with the analyze command also. The hierarchy of executing both commands(in parallel) is as follows: vacuum command is executed first and then analyze command is used to build a querying plan for the new data. The vacuum and analyze commands can jointly be executed in the following way.
Conclusion
The ANALYZE command is a powerful command-line utility for Postgres databases. Based on the ANALYZE command, the Postgres querying system designs query plans. This article presents the usage of Postgres ANALYZE that can be applied to databases, the tables, and the columns of the tables. The famous Postgres command named vacuum can also be used with analyzing commands to vacate space and build querying plans for new records. The execution of the ANALYZE command presented in this article is performed on Ubuntu whereas the syntax of Postgres is generic thus the commands can be executed on several other distributions as well.
