{"id":16670,"date":"2021-08-24T16:50:19","date_gmt":"2021-08-24T14:50:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/"},"modified":"2021-08-24T16:50:19","modified_gmt":"2021-08-24T14:50:19","slug":"parallel-distinct-for-postgresql-15","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/","title":{"rendered":"Parallel distinct for PostgreSQL 15"},"content":{"rendered":"<p>When parallel execution was introduced with <a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/release-9-6.html\" target=\"_blank\" rel=\"noopener\">PostgreSQL 9.6<\/a> it was quite limited and supported only a few operations to run in parallel. Over time more and more operations were enabled to run in parallel. When PostgreSQL 15 will be released next year it will come with parallel distinct. Here is an easy example:<\/p>\n<p><!--more--><\/p>\n<p>If you do something like this, e.g. in PostgreSQL 13:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                              version                                              \n---------------------------------------------------------------------------------------------------\n PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit\n(1 row)\npostgres=# create table t1 ( a int );\nCREATE TABLE\npostgres=# insert into t1 select 1 from generate_series(1,100000);\nINSERT 0 100000\npostgres=# insert into t1 select 2 from generate_series(1,300000);\nINSERT 0 300000\npostgres=# \npostgres=# insert into t1 select 4 from generate_series(1,400000);\nINSERT 0 400000\npostgres=# insert into t1 select 5 from generate_series(1,500000);\nINSERT 0 500000\npostgres=# analyze t1;\nANALYZE\n<\/pre>\n<p>A distinct over the &#8220;a&#8221; column will always run serially:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# explain analyze select distinct a from t1;\n\n                                                     QUERY PLAN                                                     \n--------------------------------------------------------------------------------------------------------------------\n HashAggregate  (cost=22003.00..22003.04 rows=4 width=4) (actual time=1295.168..1295.172 rows=4 loops=1)\n   Group Key: a\n   Batches: 1  Memory Usage: 24kB\n   -&gt;  Seq Scan on t1  (cost=0.00..18753.00 rows=1300000 width=4) (actual time=0.041..608.124 rows=1300000 loops=1)\n Planning Time: 0.249 ms\n Execution Time: 1295.887 ms\n(6 rows)\n<\/pre>\n<p>Doing the same test against the current master branch shows that parallel execution is triggered:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                               version                                                \n------------------------------------------------------------------------------------------------------\n PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit\n(1 row)\n\npostgres=# create table t1 ( a int );\nCREATE TABLE\npostgres=# insert into t1 select 1 from generate_series(1,100000);\nINSERT 0 100000\npostgres=# insert into t1 select 2 from generate_series(1,300000);\nINSERT 0 300000\npostgres=# insert into t1 select 4 from generate_series(1,400000);\nINSERT 0 400000\npostgres=# insert into t1 select 5 from generate_series(1,500000);\nINSERT 0 500000\npostgres=# analyze t1;\nANALYZE\npostgres=# explain analyze select distinct a from t1;\n                                                                 QUERY PLAN                                                                  \n---------------------------------------------------------------------------------------------------------------------------------------------\n Unique  (cost=13524.79..13524.83 rows=4 width=4) (actual time=1382.720..1383.346 rows=4 loops=1)\n   -&gt;  Sort  (cost=13524.79..13524.81 rows=8 width=4) (actual time=1382.716..1383.332 rows=12 loops=1)\n         Sort Key: a\n         Sort Method: quicksort  Memory: 25kB\n         -&gt;  Gather  (cost=13523.83..13524.67 rows=8 width=4) (actual time=1382.681..1383.307 rows=12 loops=1)\n               Workers Planned: 2\n               Workers Launched: 2\n               -&gt;  HashAggregate  (cost=12523.83..12523.87 rows=4 width=4) (actual time=1340.241..1340.245 rows=4 loops=3)\n                     Group Key: a\n                     Batches: 1  Memory Usage: 24kB\n                     Worker 0:  Batches: 1  Memory Usage: 24kB\n                     Worker 1:  Batches: 1  Memory Usage: 24kB\n                     -&gt;  Parallel Seq Scan on t1  (cost=0.00..11169.67 rows=541667 width=4) (actual time=0.028..663.671 rows=433333 loops=3)\n Planning Time: 0.338 ms\n Execution Time: 1383.477 ms\n(15 rows)\n<\/pre>\n<p>Nice, <a href=\"https:\/\/www.postgresql.org\/message-id\/E1mHlhP-0005P0-8X%40gemulon.postgresql.org\" target=\"_blank\" rel=\"noopener\">details here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When parallel execution was introduced with PostgreSQL 9.6 it was quite limited and supported only a few operations to run in parallel. Over time more and more operations were enabled to run in parallel. When PostgreSQL 15 will be released next year it will come with parallel distinct. Here is an easy example:<\/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-16670","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-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>Parallel distinct for PostgreSQL 15 - 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\/parallel-distinct-for-postgresql-15\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Parallel distinct for PostgreSQL 15\" \/>\n<meta property=\"og:description\" content=\"When parallel execution was introduced with PostgreSQL 9.6 it was quite limited and supported only a few operations to run in parallel. Over time more and more operations were enabled to run in parallel. When PostgreSQL 15 will be released next year it will come with parallel distinct. Here is an easy example:\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-08-24T14:50:19+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\/parallel-distinct-for-postgresql-15\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Parallel distinct for PostgreSQL 15\",\"datePublished\":\"2021-08-24T14:50:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\"},\"wordCount\":93,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\",\"name\":\"Parallel distinct for PostgreSQL 15 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-08-24T14:50:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Parallel distinct for PostgreSQL 15\"}]},{\"@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":"Parallel distinct for PostgreSQL 15 - 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\/parallel-distinct-for-postgresql-15\/","og_locale":"en_US","og_type":"article","og_title":"Parallel distinct for PostgreSQL 15","og_description":"When parallel execution was introduced with PostgreSQL 9.6 it was quite limited and supported only a few operations to run in parallel. Over time more and more operations were enabled to run in parallel. When PostgreSQL 15 will be released next year it will come with parallel distinct. Here is an easy example:","og_url":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/","og_site_name":"dbi Blog","article_published_time":"2021-08-24T14:50:19+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\/parallel-distinct-for-postgresql-15\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Parallel distinct for PostgreSQL 15","datePublished":"2021-08-24T14:50:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/"},"wordCount":93,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/","url":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/","name":"Parallel distinct for PostgreSQL 15 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-08-24T14:50:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/parallel-distinct-for-postgresql-15\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Parallel distinct for PostgreSQL 15"}]},{"@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\/16670","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=16670"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16670\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16670"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}