How can you use the PostgreSQL pg_stat_statements extension to monitor query performance?

1 Installing and enabling the extension

To use the pg_stat_statements extension, you need to install it on your PostgreSQL server and enable it in your database. You can install the extension using the CREATE EXTENSION command, as long as you have the appropriate privileges and the extension is available in your PostgreSQL installation. For example, to install the extension in the database named mydb, you can run: CREATE EXTENSION pg_stat_statements; To enable the extension, you need to add it to the shared_preload_libraries parameter in your postgresql.conf file, which is usually located in the data directory of your PostgreSQL server. You can edit the file using any text editor and append pg_stat_statements to the existing value of the parameter, separated by a comma. For example, if the original value is: shared_preload_libraries = 'some_other_extension' You can change it to: shared_preload_libraries = 'some_other_extension,pg_stat_statements' After editing the file, you need to restart your PostgreSQL server for the changes to take effect.

2 Configuring the extension

The pg_stat_statements extension has several parameters that you can configure to customize its behavior and performance. You can set these parameters in your postgresql.conf file or in your session using the SET command. Some of the most important ones are pg_stat_statements.max, pg_stat_statements.track, and pg_stat_statements.track_utility. The pg_stat_statements.max parameter determines the maximum number of statements that the extension can track, with the default value being 5000; however, increasing this value can increase memory usage, so you should consider your available resources when adjusting this parameter. The pg_stat_statements.track parameter controls which types of statements are tracked by the extension, with the default value being top, which means that only top-level statements are tracked and excludes those inside functions or triggers. You can change this to all if you want to track all statements or to a specific list if you want to filter out some types. Lastly, the pg_stat_statements.track_utility parameter determines whether utility commands are tracked by the extension, with the default value being on, meaning that utility commands are tracked; however, you can change this value to off if you want to exclude them from the statistics.

3 Querying the extension

The pg_stat_statements extension provides a view named pg_stat_statements that contains the statistics for each tracked statement. You can query this view using standard SQL commands, such as SELECT, ORDER BY, LIMIT, etc. The view has many columns that store various metrics, such as the total number of executions, the total and average time spent, the total and average number of rows returned or affected, the total and average shared and exclusive block hits and reads, etc. You can use these columns to analyze the performance and efficiency of your queries and compare them with each other. For example, to find the top 10 most time-consuming queries in your database, you can run:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;        

This will return the query text, the total time spent, the number of executions, and the average time per execution for each query.

4 Resetting the extension

The pg_stat_statements extension accumulates the statistics over time, which can make them less relevant or accurate as your data and queries change. You can reset the statistics at any time using the pg_stat_statements_reset() function, which requires superuser privileges. For example, to reset the statistics for all statements, you can run: SELECT pg_stat_statements_reset(); You can also reset the statistics for a specific database or user using the pg_stat_statements_reset(dbid, userid) function, where dbid and userid are the identifiers of the database and user, respectively. You can find these identifiers in the pg_database and pg_user system catalogs. For example, to reset the statistics for the database named mydb and the user named alice, you can run:

SELECT pg_stat_statements_reset(
  (SELECT oid FROM pg_database WHERE datname = 'mydb'),
  (SELECT usesysid FROM pg_user WHERE usename = 'alice')
);        

5 Using the extension with other tools

The pg_stat_statements extension can be used in conjunction with other tools and extensions that can help you monitor and optimize your query performance. For example, you can use the EXPLAIN and EXPLAIN ANALYZE commands to examine the execution plan and the actual execution time and cost of your queries. You can also use the pg_stat_activity view to see the current activity and state of your sessions and connections. Additionally, you can use the pgBadger tool to generate detailed reports and graphs from your PostgreSQL log files, including the statistics from the pg_stat_statements extension. These tools and extensions can provide you with valuable insights and recommendations for improving your query performance and database efficiency.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?