{"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":28,"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 v28.8 (Yoast SEO v28.8) - 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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}