{"id":35310,"date":"2024-10-18T10:10:39","date_gmt":"2024-10-18T08:10:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=35310"},"modified":"2024-10-18T10:11:49","modified_gmt":"2024-10-18T08:11:49","slug":"improved-not-null-null-planning-in-postgresql-17","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/","title":{"rendered":"Improved not null\/null planning in PostgreSQL 17"},"content":{"rendered":"\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/current\/release-17.html#RELEASE-17-CHANGES\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL 17<\/a> came with quite some additional features for the optimizer. One of them is about &#8220;is null&#8221; and &#8220;is not null&#8221; handling when column have a &#8220;not null&#8221; constraint defined. In the past it was common to just not add a &#8220;is not null&#8221; to a query when you are anyway sure that there cannot be any nulls because of a &#8220;not null&#8221; constraint. If you did it it anyway, there was some overhead because the &#8220;is not null&#8221; had to be evaluated. This changed with PostgreSQL 17 as the optimizer got smarter for such cases.<\/p>\n\n\n\n<p>This can easily be demonstrated by creating a simple table with one column having a not null constraint:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6,8,10]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# create table t ( a int not null );\nCREATE TABLE\npostgres=# insert into t select * from generate_series(1,1000000);\nINSERT 0 1000000\npostgres=# analyze t;\nANALYZE\n<\/pre><\/div>\n\n\n<p>&#8230; and the same in version 17:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6,8,10]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# create table t ( a int not null );\nCREATE TABLE\npostgres=# insert into t select * from generate_series(1,1000000);\nINSERT 0 1000000\npostgres=# analyze t;\nANALYZE\n<\/pre><\/div>\n\n\n<p>If we select from that table in version 16 by asking for all the row which are not null we&#8217;ll see this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# explain select * from t where a is not null;\n                        QUERY PLAN                         \n-----------------------------------------------------------\n Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)\n   Filter: (a IS NOT NULL)\n(2 rows)\n<\/pre><\/div>\n\n\n<p>The filter (a IS NOT NULL) is evaluated even that we know it does apply to all the rows because of the not null constraint. Doing the same in version 17 looks like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# explain select * from t where a is not null;\n                        QUERY PLAN                         \n-----------------------------------------------------------\n Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)\n(1 row)\n\n<\/pre><\/div>\n\n\n<p>The filter is gone which saves the overhead of evaluating it. The other case is asking for all rows which are null, which obviously cannot be the case because the constraint does not allow that. <\/p>\n\n\n\n<p>PostgreSQL 16 will do a parallel sequential scan over the whole table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# explain select * from t where a is null;\n                            QUERY PLAN                             \n-------------------------------------------------------------------\n Gather  (cost=1000.00..9591.77 rows=1 width=4)\n   Workers Planned: 2\n   -&gt;  Parallel Seq Scan on t  (cost=0.00..8591.67 rows=1 width=4)\n         Filter: (a IS NULL)\n(4 rows)\n\n<\/pre><\/div>\n\n\n<p>PostgreSQL 17 will not do that anymore:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,6]; title: ; notranslate\" title=\"\">\npostgres=# select version();\n                                                   version                                                    \n--------------------------------------------------------------------------------------------------------------\n PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)\npostgres=# explain select * from t where a is null;\n                QUERY PLAN                \n------------------------------------------\n Result  (cost=0.00..0.00 rows=0 width=0)\n   One-Time Filter: false\n(2 rows)\n<\/pre><\/div>\n\n\n<p>Seems to be obvious, but versions before 17 cannot do this. A really nice improvement and only this should make you using PostgreSQL 17 as soon as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL 17 came with quite some additional features for the optimizer. One of them is about &#8220;is null&#8221; and &#8220;is not null&#8221; handling when column have a &#8220;not null&#8221; constraint defined. In the past it was common to just not add a &#8220;is not null&#8221; to a query when you are anyway sure that there [&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-35310","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>Improved not null\/null planning in PostgreSQL 17 - 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\/improved-not-null-null-planning-in-postgresql-17\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Improved not null\/null planning in PostgreSQL 17\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL 17 came with quite some additional features for the optimizer. One of them is about &#8220;is null&#8221; and &#8220;is not null&#8221; handling when column have a &#8220;not null&#8221; constraint defined. In the past it was common to just not add a &#8220;is not null&#8221; to a query when you are anyway sure that there [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-18T08:10:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-10-18T08:11:49+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\/improved-not-null-null-planning-in-postgresql-17\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Improved not null\/null planning in PostgreSQL 17\",\"datePublished\":\"2024-10-18T08:10:39+00:00\",\"dateModified\":\"2024-10-18T08:11:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\"},\"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\/improved-not-null-null-planning-in-postgresql-17\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\",\"name\":\"Improved not null\/null planning in PostgreSQL 17 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-10-18T08:10:39+00:00\",\"dateModified\":\"2024-10-18T08:11:49+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Improved not null\/null planning in PostgreSQL 17\"}]},{\"@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":"Improved not null\/null planning in PostgreSQL 17 - 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\/improved-not-null-null-planning-in-postgresql-17\/","og_locale":"en_US","og_type":"article","og_title":"Improved not null\/null planning in PostgreSQL 17","og_description":"PostgreSQL 17 came with quite some additional features for the optimizer. One of them is about &#8220;is null&#8221; and &#8220;is not null&#8221; handling when column have a &#8220;not null&#8221; constraint defined. In the past it was common to just not add a &#8220;is not null&#8221; to a query when you are anyway sure that there [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/","og_site_name":"dbi Blog","article_published_time":"2024-10-18T08:10:39+00:00","article_modified_time":"2024-10-18T08:11:49+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\/improved-not-null-null-planning-in-postgresql-17\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Improved not null\/null planning in PostgreSQL 17","datePublished":"2024-10-18T08:10:39+00:00","dateModified":"2024-10-18T08:11:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/"},"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\/improved-not-null-null-planning-in-postgresql-17\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/","url":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/","name":"Improved not null\/null planning in PostgreSQL 17 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-10-18T08:10:39+00:00","dateModified":"2024-10-18T08:11:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/improved-not-null-null-planning-in-postgresql-17\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Improved not null\/null planning in PostgreSQL 17"}]},{"@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\/35310","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=35310"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35310\/revisions"}],"predecessor-version":[{"id":35316,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35310\/revisions\/35316"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=35310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=35310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=35310"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=35310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}