Playing with pg_stat_statment

Playing with pg_stat_statment

Step 1: Enable pg_stat_statements Extension

First, ensure that the pg_stat_statements extension is installed and enabled in your PostgreSQL instance. You can check if the extension is already installed and enabled by running the following SQL command:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

If it is not installed, you can add the extension by running:

CREATE EXTENSION pg_stat_statements;

Next, ensure that the pg_stat_statements module is loaded by modifying your postgresql.conf file (usually found in the data directory). Add or uncomment the following line:

shared_preload_libraries = 'pg_stat_statements'

After making this change, restart PostgreSQL for the changes to take effect.

Step 2: Query pg_stat_statements

Once pg_stat_statements is enabled, you can query the pg_stat_statements view to get performance data related to SQL queries. Below are some common queries you can use:

1. List all executed queries with execution statistics

This query retrieves a list of queries that have been executed, showing statistics such as total execution time, number of calls, and average time per call.

SELECT 
    query,
    calls,
    total_time,
    mean_time,
    min_time,
    max_time,
    stddev_time,
    rows
FROM 
    pg_stat_statements
ORDER BY 
    total_time DESC
LIMIT 10;

• query: The SQL query text.
• calls: The number of times the query has been executed.
• total_time: The total time spent executing this query.
• mean_time: The average time per execution.
• min_time: The minimum execution time.
• max_time: The maximum execution time.
• stddev_time: The standard deviation of execution times.
• rows: The total number of rows returned by the query.

2. Find the most time-consuming queries

To identify the queries that have consumed the most total execution time:

SELECT 
    query,
    total_time,
    calls,
    total_time / calls AS avg_time_per_call
FROM 
    pg_stat_statements
ORDER BY 
    total_time DESC
LIMIT 10;

This query orders the results by the total execution time, helping you identify the queries that have been the most expensive in terms of resources.

3. Queries that are called the most

To find the queries that are called most frequently, you can run:

SELECT 
    query,
    calls,
    total_time,
    calls / (EXTRACT(EPOCH FROM now() - pg_stat_statements.last_reset)) AS calls_per_second
FROM 
    pg_stat_statements
ORDER BY 
    calls DESC
LIMIT 10;

• This shows the queries with the highest number of executions.

4. Queries that have the highest average execution time

You can also find queries that are taking the longest on average by using:

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

This will give you queries with the highest average execution time.

5. Clear statistics for pg_stat_statements

If you want to reset the statistics in pg_stat_statements, for example after a performance tuning or database changes:

SELECT pg_stat_statements_reset();

This will clear the accumulated statistics, so you can start collecting fresh data.

6. Get the execution count for each unique query plan

To get execution stats based on the query plan, use:

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

• queryid: The hash ID of the query (unique identifier for the query plan).
• This is useful when you have many variations of the same query, as it groups queries by plan.

Conclusion

These are just some of the common queries you can use to analyze query performance using the pg_stat_statements extension. You can modify and combine these queries depending on the specific performance information you are seeking to monitor or optimize in your PostgreSQL database.