PostgreSQL Performance Diagnostics: Analyze Query Times with pg_stat_statements

Purpose

This PostgreSQL SQL query utilizes the power of the pg_stat_statements extension to provide you with essential insights into query performance. It retrieves crucial metrics like total execution time, average execution time, minimum and maximum execution times, and even the standard deviation of execution times. Armed with this information, you can identify slow-running queries, pinpoint performance bottlenecks, and make data-driven decisions for optimization.

Sample Code from Command Line

 1CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 2
 3SELECT
 4  calls,
 5  rows,
 6  ROUND((total_exec_time::numeric / 1000), 4) AS total_secs,
 7  -- newer versions of PostgreSQL have mean_exec_time field, don't need to calculate
 8  --ROUND((total_exec_time / 1000 / calls)::numeric, 4) AS average_secs,
 9  ROUND(mean_exec_time::numeric / 1000, 4) AS average_secs,
10  ROUND(min_exec_time::numeric / 1000, 4) AS min_secs,
11  ROUND(max_exec_time::numeric / 1000, 4) AS max_secs,
12  ROUND(stddev_exec_time::numeric / 1000, 4) AS stddev_secs,
13  query
14FROM
15  pg_stat_statements
16ORDER BY
17  average_secs DESC,
18  calls DESC,
19  rows DESC
20LIMIT 100;

Code Breakdown:

1. CREATE EXTENSION IF NOT EXISTS pg_stat_statements;:

   • This line ensures that the pg_stat_statements extension is installed in your database. This extension is pivotal for gathering and aggregating performance statistics about SQL queries executed on the server.

2. SELECT calls, rows, ROUND((total_exec_time::numeric / 1000), 4) AS total_secs, ...:

   • Extracts and calculates the following columns:
     • calls: The number of times the specific query has been executed.
     • rows: The total number of rows processed or returned by the query.
     • total_secs: The total execution time of the query in seconds, rounded to 4 decimal places.
     • average_secs: The average execution time of the query in seconds, rounded to 4 decimal places. Utilizes the mean_exec_time column available in newer PostgreSQL versions.
     • min_secs: The minimum execution time of the query in seconds, rounded to 4 decimal places.
     • max_secs: The maximum execution time of the query in seconds, rounded to 4 decimal places.
     • stddev_secs: The standard deviation of the query's execution time in seconds, rounded to 4 decimal places. This helps understand the variability in query performance.
     • query: The actual SQL text of the query.

3. FROM pg_stat_statements:

   • Queries the pg_stat_statements view, which stores aggregated statistics about executed SQL statements.

4. ORDER BY average_secs DESC, calls DESC, rows DESC LIMIT 100;:

   • Sorts the results based on three criteria:
     • average_secs in descending order: Prioritizes queries with the highest average execution times.
     • calls in descending order: If multiple queries have the same average time, those executed more frequently are shown first.
     • rows in descending order: For queries with identical average times and call counts, those processing more rows are listed first.
   • LIMIT 100: Restricts the output to the top 100 queries.

Key Points:

• Performance Metrics: Focuses on essential metrics for evaluating query performance, including total time, average time, minimum and maximum times, and standard deviation.
• Prioritized Results: Sorts results by average execution time, call count, and rows processed to highlight queries with the greatest potential for optimization.
• Compatibility: Requires PostgreSQL 13 or later for optimal functionality due to the use of the mean_exec_time column. Provides alternative queries for older versions.

Insights:

• Identify Slow Queries: Quickly pinpoint queries with high average execution times or wide variations in performance.
• Optimize Performance: Analyze the identified queries to uncover opportunities for optimization, such as indexing, query rewriting, or configuration changes.
• Resource Planning: Understand the resource demands of different queries to aid in capacity planning and resource allocation.

Explanations:

• pg_stat_statements: A powerful extension that tracks and aggregates statistics about SQL queries, making it indispensable for performance tuning.
• total_exec_time, mean_exec_time, min_exec_time, max_exec_time, stddev_exec_time: Columns in pg_stat_statements that provide detailed timing information about query execution.
• The calculations and rounding ensure that the output is presented in a clear and user-friendly format.

Helpful Reference Links:

• PostgreSQL Documentation on pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html
• Reference Code: https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_query_times.sql