Troubleshooting PostgreSQL Query Performance: Tips and Techniques for Optimization

Monitoring query latency, wait events, and locks in PostgreSQL helps identify and diagnose performance bottlenecks. Here’s a guide on how to monitor these aspects:

Query Latency:

Use the pg_stat_statements extension to track query latencies. You can check the mean_time column in the pg_stat_statements view to find the average query execution time.
Configure log_min_duration_statement in postgresql.conf to log queries that take longer than a certain threshold (in milliseconds). Analyze the log to identify slow queries.

Wait Events:

Monitor the pg_stat_activity view to check wait events for each backend process. The wait_event_type and wait_event columns indicate the type of wait event and its specific name, respectively.
Use tools like pg_activity or PgHero to visualize wait events and identify patterns causing performance issues.

Locks:

Examine the pg_locks view to monitor the current lock statuses in the database. Join this view with pg_stat_activity to gather more information about the processes holding or waiting for locks.
Monitor the pg_stat_database view to track lock-related metrics such as deadlocks or conflicts. A high number of deadlocks or conflicts could indicate potential lock contention issues.

Here’s a sample query to gather information about locks, wait events, and query latency:

-- Retrieve the top 25 most time-consuming queries and related information
SELECT
    -- Client hostname
    psa.client_hostname AS client_hostname,

    -- Database name
    psa.datname AS database_name,

    -- Query text
    pss.query AS query,

    -- Timestamp when the active query started
    to_char(psa.query_start, 'DD-Mon-YYYY HH24:MI:SS') AS query_start,

    -- Timestamp when the state of the backend last changed
    to_char(psa.state_change, 'DD-Mon-YYYY HH24:MI:SS') AS state_change,

    -- Number of times the query has been executed
    pss.calls AS total_times,

    -- Total execution time of the query in milliseconds
    to_char(pss.total_exec_time / 1000, 'FM999,999,999,999.00') || ' ms' AS total_exec_time_ms,

    -- Timestamp when the backend process started
    to_char(psa.backend_start, 'DD-Mon-YYYY HH24:MI:SS') AS backend_start,

    -- Timestamp when the current transaction started
    to_char(psa.xact_start, 'DD-Mon-YYYY HH24:MI:SS') AS xact_start,

    -- Lock type held by the backend process
    pl.locktype AS lock_type,

    -- Wait event for which the backend is waiting
    psa.wait_event AS wait_event,

    -- Total shared blocks hit in a human-readable format
    pg_size_pretty(pss.shared_blks_hit * 8 * 1024) AS shared_blks_hit,

    -- Total shared blocks read in a human-readable format
    pg_size_pretty(pss.shared_blks_read * 8 * 1024) AS shared_blks_read,

    -- Total shared blocks written in a human-readable format
    pg_size_pretty(pss.shared_blks_written * 8 * 1024) AS shared_blks_written,

    -- Total shared blocks dirtied in a human-readable format
    pg_size_pretty(pss.shared_blks_dirtied * 8 * 1024) AS shared_blks_dirtied

-- Join pg_stat_statements with pg_stat_activity using the query text
FROM
    pg_stat_statements pss
LEFT OUTER JOIN pg_stat_activity psa ON pss.query = psa.query

-- Join the result with pg_locks using the process ID (pid)
LEFT OUTER JOIN pg_locks pl ON psa.pid = pl.pid

-- Order the result by the total execution time in descending order
ORDER BY total_exec_time DESC

-- Limit the result to the top 25 most time-consuming queries
LIMIT 25;

Conclusion

Monitoring query latency, wait events, and locks, and analyzing their trends over time can help you identify and resolve performance bottlenecks in PostgreSQL.

Running this in production?

MinervaDB provides PostgreSQL Consulting, PostgreSQL Support and PostgreSQL Remote DBA with 24x7 coverage and a 15-minute S1 response. Talk to an engineer.