{"id":12192,"date":"2019-01-11T06:29:44","date_gmt":"2019-01-11T05:29:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/"},"modified":"2019-01-11T06:29:44","modified_gmt":"2019-01-11T05:29:44","slug":"postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/","title":{"rendered":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid"},"content":{"rendered":"

PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements<\/a>. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:<\/p>\n

<\/p>\n

\npg_stat_statements_reset() returns void\n<\/pre>\n
This means your only choice is to reset all the statistics. Today this commit<\/a> landed and this will give you more control on which statistics to reset. The signature of the function now looks like this:<\/p>\n
\npg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void\n<\/pre>\n
There are three new parameters for controlling what to reset: The user id, the database id and the id of a specific query. By default all of them are 0 meaning the the function will behave as in previous versions: Discarding all the statistics. Lets create two users, two databases and a table in each so we will have something in pg_stat_statements we can work with:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create user u1 with login password 'u1'\" postgres\nCREATE ROLE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create user u2 with login password 'u2'\" postgres\nCREATE ROLE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create database db1 with owner = u1\" postgres\nCREATE DATABASE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create database db2 with owner = u2\" postgres\nCREATE DATABASE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create table t1 (a int)\" -U u1 db1\nCREATE TABLE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"create table t1 (a int)\" -U u2 db2\nCREATE TABLE\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"insert into t1 select * from generate_series(1,100)\" -U u1 db1\nINSERT 0 100\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"insert into t1 select * from generate_series(1,100)\" -U u2 db2\nINSERT 0 100\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"select count(*) from t1\" -U u1 db1\n count \n-------\n   100\n(1 row)\npostgres@pgbox:\/u02\/pgdata\/DEV\/ [PGDEV] psql -c \"select count(*) from t1\" -U u2 db2\n count \n-------\n   100\n(1 row)\n<\/pre>\n
We should be able to see the statements in pg_stat_statements but before doing that lets check the dbids:<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PGDEV] oid2name \nAll databases:\n    Oid  Database Name  Tablespace\n----------------------------------\n  16394            db1  pg_default\n  16395            db2  pg_default\n  13569       postgres  pg_default\n  13568      template0  pg_default\n      1      template1  pg_default\n<\/pre>\n
What do we see for our two databases?<\/p>\n
\npostgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395);\n userid | dbid  |       queryid        | calls |                        query                        \n--------+-------+----------------------+-------+-----------------------------------------------------\n  16392 | 16394 |  7490503619681577402 |     3 | set client_encoding to 'unicode'\n  16393 | 16395 |   843119317166481275 |     1 | insert into t1 select * from generate_series($1,$2)\n  16392 | 16394 | -3672942776844552312 |     1 | insert into t1 select * from generate_series($1,$2)\n  16393 | 16395 |  7490503619681577402 |     3 | set client_encoding to 'unicode'\n  16392 | 16394 |  5583984467630386743 |     1 | select count(*) from t1\n  16393 | 16395 |  4983979802666994390 |     1 | select count(*) from t1\n  16393 | 16395 |  6842879890091936614 |     1 | create table t1 (a int)\n  16392 | 16394 |  6842879890091936614 |     1 | create table t1 (a int)\n<\/pre>\n
We should be able to reset the statistics for a specific query:<\/p>\n
\npostgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;\n userid | dbid  |       queryid       | calls |          query          \n--------+-------+---------------------+-------+-------------------------\n  16393 | 16395 | 6842879890091936614 |     1 | create table t1 (a int)\n  16392 | 16394 | 6842879890091936614 |     1 | create table t1 (a int)\n(2 rows)\npostgres=# select pg_stat_statements_reset(0, 0, 6842879890091936614);\n pg_stat_statements_reset \n--------------------------\n \n(1 row)\n\npostgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;\n userid | dbid | queryid | calls | query \n--------+------+---------+-------+-------\n(0 rows)\n<\/pre>\n
Notice that this of course resets the statistics for both statements as they have the same queryid. You could specify the userid and\/or dbid as well to reset just one of them. Nice new feature.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12192","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-11T05:29:44+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid\",\"datePublished\":\"2019-01-11T05:29:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/\"},\"wordCount\":226,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/\",\"name\":\"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-01-11T05:29:44+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid","og_description":"PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/","og_site_name":"dbi Blog","article_published_time":"2019-01-11T05:29:44+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid","datePublished":"2019-01-11T05:29:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/"},"wordCount":226,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/","name":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-01-11T05:29:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-pg_stat_statements_reset-for-userid-queryid-and-dbid\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12192","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12192"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12192\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12192"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12192"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}