Wednesday, October 12, 2022

PostgreSQL 13 pg_stat_statements

Overview

Today, we will discuss, how to configure pg_stat_statements in PostgreSQL 13 for Performance improvement strategies

The pg_stat_statements module provides a means for tracking the planning and execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in PostgreSQL. conf, because it requires additional shared memory.

Let's start

We will run below SQL Query for pg_stat_statements findings.

SQL Query (Make sure that you have opened and connected with your database using PgAdmin 4 Tool)

SQL

SELECT * FROM pg_stat_statements ORDER BY Total_time DESC;

Error Message

As we can see, it's generating an Error message with the description "ERROR: relation "pg_stat_statements" does not exist at character 17

How to resolve this Error

First of all, we have to install an Extension for pg_stat_statements for further work, so we will type the below SQL command in PgAdmin 4 Query Editor

CREATE Extension pg_stat_statements;

Now, as we can see the problem is not resolved yet, so after digging out for finding the root cause

Following PostgreSQL.conf entries are required to be added for proper functionality

Open PostgreSQL.conf and add these lines

# Configuration for pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

#compute_query_id = on

pg_stat_statements.max = 10000

pg_stat_statements.track = all

Restart PostgreSQL services from Services.msc

Now the problem is still there, what is exactly happening with this Error message.

Looking into Log File

After reading PostgreSQL documentation, i found these lines which are explaining clearly about this error message

pg_stat_statements for Postgres 13 does not contain: total_time, min_time, max_time, mean_time, stddev_time columns

https://www.postgresql.org/docs/13/pgstatstatements.html

Modify the above Query/SQL statement

SQL Query

SELECT t2.rolname, t3.datname, queryid, calls
, ( total_plan_time + total_exec_time ) / 1000 as total_time_seconds
, ( min_plan_time + min_exec_time ) / 1000 as min_time_seconds, ( max_plan_time + max_exec_time ) / 1000 as max_time_seconds
,( mean_plan_time + mean_exec_time ) / 1000 as mean_time_seconds
,( stddev_plan_time + stddev_exec_time ) / 1000 as stddev_time_seconds
, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written
, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read
, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds
FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid)
JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'
AND queryid IS NOT NULL;