Detailed explanation and installation test of PostgreSQL module pg_stat_statements

Source: Internet
Author: User
Tags postgresql module postgres database
This module requires additional shared memory. The memory size is roughly pg_stat_statements.max * track_activity_query_size. Note that once a module is loaded, that is

This module requires additional shared memory. The memory size is roughly pg_stat_statements.max * track_activity_query_size. Note that once a module is loaded, that is

In fact, it is very simple. Recently, postgresql may need to be monitored, so I have been exposed to many related monitoring commands and tools. This article mainly records the work process, and I am afraid I will forget it later.

The function I want is to record the execution time of each SQL statement and query the top 10 execution times per day.

The following describes pg_stat_statements: (Translation)

The pg_stat_statements module provides a method to track statistics of all SQL statements executed.

This module must rewrite the shared_preload_libraries variable in the configuration file postgresql. conf (later I will explain how to configure it) because it requires additional shared memory. It also means that the service needs to be restarted.

1-first look at the pg_stat_statements View

Field name







Performer id




Execution Database id



Executed statement



Number of executions


Double precision

Total execution time (average = total_time/CILS)



Total number of affected rows



Number of shared block hits



Number of shared block reads



Number of shared block writes



Number of Local block hits



Number of Local block reads



Number of Local block writes

The above view and function pg_stat_statements_reset will take effect only after the database has been correctly installed and the pg_stat_statements. SQL script has been executed. All database operations on the server will be tracked as long as pg_stat_statements is successfully added.

For security reasons, normal users are not allowed to view the statement information (query) executed by other users. If the view is installed in its database, then you can view the relevant statistical information (Zijian's experiment is really a Super User: S user postgres database)

Note: If the statement information (query) is the same, the statements are considered to be the same no matter whether the value of any out-of-line variable is used. Using the out-of-line variable can help organize statements and make statistical data more useful.


Pg_stat_statements_reset () returns void

Pg_stat_statements_reset discards all information currently counted by pg_stat_statements. By default, this function can only run under a Super User.

3-configuration variables

Pg_stat_statements.max (integer)

Pg_stat_statements.max is the maximum number of Statistics records (that is, the maximum number of rows in the view ). If the data volume is greater than the maximum value, the statement that executes the minimum value will be discarded (in my test, if the statement is executed for 1 times, followed by the data that has been discarded for a long time ), the default value is 1000. This variable is set before the service starts.

Pg_stat_statements.track (enum)

Pg_stat_statements.track controls statistical data rules. top is used to track top-level statement (directly sent by the client). all also traces nested statements (such as statements called in functions)

Pg_stat_statements.track_utility (boolen)

Pg_stat_statements.track_utility controls whether to track public program commands (utility commands). The public program command is a command other than SELECT/INSERT/UPDATE/DELETE. The default value is enable. Only Super Users can modify this setting. (boolean) specifies whether to save statistics when the server is shut down. If off is set, statistics are not saved when the service is disabled. The default value is on. This value can only be set in postgresql. conf or the command line.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.