{"id":35222,"date":"2024-10-15T10:06:25","date_gmt":"2024-10-15T08:06:25","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=35222"},"modified":"2024-10-15T10:06:28","modified_gmt":"2024-10-15T08:06:28","slug":"postgresql-18-tweaking-relation-statistics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/","title":{"rendered":"PostgreSQL 18: Tweaking relation statistics"},"content":{"rendered":"\n<p>Up to now, there is no official way in PostgreSQL to tweak optimizer statistics manually. While this is quite common in other relational databases, PostgreSQL did not provide anything to do this. The first steps to allow this have now been <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commitdiff;h=e839c8ecc9352b7754e74f19ace013c0c0d18613\" target=\"_blank\" rel=\"noreferrer noopener\">committed<\/a> for PostgreSQL 18. <\/p>\n\n\n\n<p>Before look at the two new functions which have been added, let&#8217;s create a small test table and an index:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,5,7,9,11]; title: ; notranslate\" title=\"\">\npostgres=# create table t ( a int, b text );\nCREATE TABLE\npostgres=# insert into t values (1,&#039;aa&#039;);\nINSERT 0 1\npostgres=# insert into t select i, &#039;bb&#039; from generate_series(2,100) i;\nINSERT 0 99\npostgres=# analyze t;\nANALYZE\npostgres=# create index i on t(b);\nCREATE INDEX\npostgres=# \\d t\n                 Table &quot;public.t&quot;\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n b      | text    |           |          | \nIndexes:\n    &quot;i&quot; btree (b)\n\n<\/pre><\/div>\n\n\n<p>The number of pages and the number of rows are currently reported as this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select relpages,reltuples from pg_class where relname = &#039;t&#039;;\n relpages | reltuples \n----------+-----------\n        1 |       100\n(1 row)\n<\/pre><\/div>\n\n\n<p>Asking for the explain plan for a simple select over the table restricting on &#8221;aa&#8217; for column b will give a sequential scan:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# explain select * from t where b = &#039;aa&#039;;\n                   QUERY PLAN                    \n-------------------------------------------------\n Seq Scan on t  (cost=0.00..2.25 rows=1 width=7)\n   Filter: (b = &#039;aa&#039;::text)\n(2 rows)\n<\/pre><\/div>\n\n\n<p>As the table is consisting of only one block, it is still faster to read the whole table than to use the index, even if there is only one row matching our criteria. One of the two new function introduced by the commit linked above is pg_set_relation_stats:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# \\x\nExpanded display is on.\npostgres=# \\df pg_set_relation_stats\nList of functions\n-&#x5B; RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------\nSchema              | pg_catalog\nName                | pg_set_relation_stats\nResult data type    | boolean\nArgument data types | relation regclass, relpages integer DEFAULT NULL::integer, reltuples real DEFAULT NULL::real, relallvisible integer DEFAULT NULL::integer\nType                | func\n<\/pre><\/div>\n\n\n<p>As you can easily see, this function takes a relation name, the number of pages, the number of rows and the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/storage-vm.html\">all visible flag<\/a> as input parameters. This allows us to tweak the statistics so that the optimizer will go for an index scan instead of the sequential scan:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6,8,13]; title: ; notranslate\" title=\"\">\npostgres=# select * from pg_set_relation_stats(&#039;t&#039;::regclass, 1, 1000000 );\n pg_set_relation_stats \n-----------------------\n t\n(1 row)\npostgres=# \\x\nExpanded display is off.\npostgres=# select relpages,reltuples from pg_class where relname = &#039;t&#039;;\n relpages | reltuples \n----------+-----------\n        1 |     1e+06\n(1 row)\npostgres=# explain select * from t where b = &#039;aa&#039;;\n                           QUERY PLAN                            \n-----------------------------------------------------------------\n Index Scan using i on t  (cost=0.17..183.18 rows=10000 width=7)\n   Index Cond: (b = &#039;aa&#039;::text)\n(2 rows)\n\npostgres=# \n<\/pre><\/div>\n\n\n<p>Nice, now we can debug optimizer decisions by modifying these statistics. Just be aware that statistics we set using this function are kind of temporary. Any manual or automatic analyze will overwrite them:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,9]; title: ; notranslate\" title=\"\">\npostgres=# analyze t;\nANALYZE\npostgres=# select relpages,reltuples from pg_class where relname = &#039;t&#039;;\n relpages | reltuples \n----------+-----------\n        1 |       100\n(1 row)\n\npostgres=# explain select * from t where b = &#039;aa&#039;;\n                   QUERY PLAN                    \n-------------------------------------------------\n Seq Scan on t  (cost=0.00..2.25 rows=1 width=7)\n   Filter: (b = &#039;aa&#039;::text)\n(2 rows)\n<\/pre><\/div>\n\n\n<p>The second function provided can be used to reset the statistics as they would be when the table gets created:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,7]; title: ; notranslate\" title=\"\">\npostgres=# select * from pg_clear_relation_stats(&#039;t&#039;::regclass);\n pg_clear_relation_stats \n-------------------------\n t\n(1 row)\n\npostgres=# select relpages,reltuples from pg_class where relname = &#039;t&#039;;\n relpages | reltuples \n----------+-----------\n        0 |        -1\n(1 row)\n<\/pre><\/div>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Up to now, there is no official way in PostgreSQL to tweak optimizer statistics manually. While this is quite common in other relational databases, PostgreSQL did not provide anything to do this. The first steps to allow this have now been committed for PostgreSQL 18. Before look at the two new functions which have been [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[2749],"class_list":["post-35222","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql","type-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 18: Tweaking relation statistics - 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-18-tweaking-relation-statistics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 18: Tweaking relation statistics\" \/>\n<meta property=\"og:description\" content=\"Up to now, there is no official way in PostgreSQL to tweak optimizer statistics manually. While this is quite common in other relational databases, PostgreSQL did not provide anything to do this. The first steps to allow this have now been committed for PostgreSQL 18. Before look at the two new functions which have been [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-15T08:06:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-10-15T08:06:28+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=\"2 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-18-tweaking-relation-statistics\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 18: Tweaking relation statistics\",\"datePublished\":\"2024-10-15T08:06:25+00:00\",\"dateModified\":\"2024-10-15T08:06:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\"},\"wordCount\":258,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\",\"name\":\"PostgreSQL 18: Tweaking relation statistics - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-10-15T08:06:25+00:00\",\"dateModified\":\"2024-10-15T08:06:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 18: Tweaking relation statistics\"}]},{\"@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 &amp;\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 18: Tweaking relation statistics - 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-18-tweaking-relation-statistics\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 18: Tweaking relation statistics","og_description":"Up to now, there is no official way in PostgreSQL to tweak optimizer statistics manually. While this is quite common in other relational databases, PostgreSQL did not provide anything to do this. The first steps to allow this have now been committed for PostgreSQL 18. Before look at the two new functions which have been [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/","og_site_name":"dbi Blog","article_published_time":"2024-10-15T08:06:25+00:00","article_modified_time":"2024-10-15T08:06:28+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 18: Tweaking relation statistics","datePublished":"2024-10-15T08:06:25+00:00","dateModified":"2024-10-15T08:06:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/"},"wordCount":258,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/","name":"PostgreSQL 18: Tweaking relation statistics - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-10-15T08:06:25+00:00","dateModified":"2024-10-15T08:06:28+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-tweaking-relation-statistics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 18: Tweaking relation statistics"}]},{"@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 &amp;\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\/35222","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=35222"}],"version-history":[{"count":10,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35222\/revisions"}],"predecessor-version":[{"id":35232,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35222\/revisions\/35232"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=35222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=35222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=35222"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=35222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}