Showing posts sorted by relevance for query PostgreSQL. Sort by date Show all posts

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;

Conclusion

So in this article, we have learned how to configure pg_stat_statements in PostgreSQL 13.

Useful Links

https://github.com/prometheus-community/postgres_exporter/issues/541

https://stackoverflow.com/questions/31021174/pg-stat-statements-enabled-but-the-table-does-not-exist

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

Natural DBA

sql server to postgresql converter Thursday, September 15, 2022

SQL server to PostgreSQL data types mapping

postgresql vs sql server syntax differences

Data Types Mapping for Migrating

Microsoft SQL Server to PostgreSQL or vice versa

Microsoft SQL Server and PostgreSQL have some similar data types while others are not. Before migration, it is important to keep in mind the right types representing:

SQL Server Data Types	PostgreSQL Data Types
BIGINT	BIGINT
BINARY(n)	BYTEA
BIT	BOOLEAN
CHAR(n), CHARACTER(n)	CHAR(n), CHARACTER(n)
DATE	DATE
DATETIME	TIMESTAMP(3)
DATETIME2(p)	TIMESTAMP(p)
DATETIMEOFFSET(p)	TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT(p)	FLOAT(p)
IMAGE	BYTEA
INT, INTEGER	INT, INTEGER
MONEY	MONEY
NCHAR(n)	CHAR(n)
NTEXT	TEXT
NUMERIC(p,s)	NUMERIC(p,s)
NVARCHAR(n)	VARCHAR(n)
NVARCHAR(max)	TEXT
REAL	REAL
ROWVERSION	BYTEA
SMALLDATETIME	TIMESTAMP(0)
SMALLINT	SMALLINT
SMALLMONEY	MONEY
TEXT	TEXT
TIME(p)	TIME(p)
TIMESTAMP	BYTEA
TINYINT	SMALLINT
UNIQUEIDENTIFIER	UUID or CHAR(16)
VARBINARY(n), VARBINARY(max)	BYTEA
VARCHAR(n)	VARCHAR(n)
VARCHAR(max)	TEXT
XML	XML

Natural DBA

PostgreSQL count rows in multiple tables Thursday, September 15, 2022

PostgreSQL Count Rows in all Tables

Postgresql Count Rows in All Tables

Overview

Open Pgadmin

Now it will look like the below window

Type this below Query or Just Copy From Here.

WITH TBL AS

(SELECT TABLE_SCHEMA,

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME not like 'pg_%' )

SELECT TABLE_SCHEMA,

TABLE_NAME,

(XPATH('/row/c/text()',

QUERY_TO_XML(FORMAT('select count(*) as c from %I.%I',

TABLE_SCHEMA,

TABLE_NAME),

FALSE,

TRUE,

'')))[1]::text::int AS TOTAL_ROWS

FROM TBL

ORDER BY 1 DESC,

3 DESC;

Results

Conclusion

This is better to use this query when you are planning either to migrate your database or you have recovered your database from any backups and verify PostgreSQL records.

Natural DBA