Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jkg9i-0001AW-E7 for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2020 03:51:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jkg9h-0001SQ-38 for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2020 03:51:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jkg9g-0001SI-Mw for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2020 03:51:28 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jkg9d-0003dc-UO for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2020 03:51:28 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id A04F921C4C1E; Mon, 15 Jun 2020 06:51:24 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.129.197] (unknown [94.25.177.94]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 13C4521C05D0; Mon, 15 Jun 2020 06:51:23 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1592193084; bh=ARalhMFGJtK/BU33fDzV3lRf4gJggqL6DssUaRPNHc0=; h=From:Subject:To:Cc:References:Date:In-Reply-To; b=BogG5fwwFbwYh9usrimwUo2k9a6DZ6e+IaWm5oQmgowoIZbn36Vt54F2N0npSMzFk vYrDqawJlE8kDkwISdtffpdWHQ4KKPhUPyumA+LlQJnLjuIqwWSt/S1J1YHVAdzcjM xWsa05Ti8L8UcRyyl67TtKf4LQItVrVU+q/6mZmM= From: "Andrey V. Lepikhov" Subject: Re: Asynchronous Append on postgres_fdw nodes. To: Kyotaro Horiguchi Cc: etsuro.fujita@gmail.com, movead.li@highgo.ca, pgsql-hackers@lists.postgresql.org References: <20200604.150015.2305436578805492574.horikyota.ntt@gmail.com> <90cced83-9061-0cac-0012-84e5faac8c78@postgrespro.ru> <20200610.120510.2075667454883130505.horikyota.ntt@gmail.com> Organization: Postgres Professional Message-ID: Date: Mon, 15 Jun 2020 08:51:23 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.8.0 MIME-Version: 1.0 In-Reply-To: <20200610.120510.2075667454883130505.horikyota.ntt@gmail.com> Content-Type: multipart/mixed; boundary="------------B8E8BBD8660275C47BDC99FE" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------B8E8BBD8660275C47BDC99FE Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit The patch has a problem with partitionwise aggregates. Asynchronous append do not allow the planner to use partial aggregates. Example you can see in attachment. I can't understand why: costs of partitionwise join are less. Initial script and explains of the query with and without the patch you can see in attachment. -- Andrey Lepikhov Postgres Professional https://postgrespro.com --------------B8E8BBD8660275C47BDC99FE Content-Type: application/x-shellscript; name="frgn2n.sh" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="frgn2n.sh" IyEvYmluL2Jhc2gKCiMgIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMj IyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjCiMKIyBEZXBsb3kgbG9j YWwgMi1ub2RlIGNvbmZpZ3VyYXRpb24gb2YgcG9zdGdyZXMgaW5zdGFuY2VzLiBOb2RlMSBo YXMgZm9yZWlnbgojIHBhcnRpdGlvbnMgYXQgdGhlIG5vZGUyLgojCiMKIyAjIyMjIyMjIyMj IyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMj IyMjIyMjIyMjIyMjIyMKClBHSU5TVEFMTD1gcHdkYC90bXBfaW5zdGFsbC8KCmV4cG9ydCBM RF9MSUJSQVJZX1BBVEg9JFBHSU5TVEFMTC9saWI6JExEX0xJQlJBUllfUEFUSApleHBvcnQg UEFUSD0kUEdJTlNUQUxML2JpbjokUEFUSApleHBvcnQgTENfQUxMPUMKZXhwb3J0IExBTkdV QUdFPSJlbl9VUzplbiIKZXhwb3J0IFBHUE9SVD01NDMyICNkZWZhdWx0IGhlYWQKZXhwb3J0 IFBHREFUQUJBU0U9YHdob2FtaWAKZXhwb3J0IFBHSE9TVD1sb2NhbGhvc3QKZXhwb3J0IFBH VVNFUj1gd2hvYW1pYAoKUEFSVFNOVU09NApwa2lsbCAtVSBgd2hvYW1pYCAtOSAtZSBwb3N0 Z3Jlcwpwa2lsbCAtVSBgd2hvYW1pYCAtOSAtZSBwZ2JlbmNoCgpEMT1gcHdkYC9QR0RBVEEx CkQyPWBwd2RgL1BHREFUQTIKCnJtIC1yZiAkRDEgJiYgbWtkaXIgJEQxICYmIHJtIC1yZiAk RDIgJiYgbWtkaXIgJEQyCnJtIC1yZiAkUEdJTlNUQUxMICYmIHJtIG4xLmxvZyAmJiBybSBu Mi5sb2cKCiMgQnVpbGRpbmcgcHJvamVjdAptYWtlID4gL2Rldi9udWxsCm1ha2UgLUMgY29u dHJpYiA+IC9kZXYvbnVsbAptYWtlIGluc3RhbGwgPiAvZGV2L251bGwKbWFrZSAtQyBjb250 cmliIGluc3RhbGwgPiAvZGV2L251bGwKCnJlbW90ZVNydk5hbWU9ZmR3cmVtb3RlCgppbml0 ZGIgLUQgJEQxIC1FIFVURjggLS1sb2NhbGU9Qwppbml0ZGIgLUQgJEQyIC1FIFVURjggLS1s b2NhbGU9QwoKZWNobyAic2hhcmVkX3ByZWxvYWRfbGlicmFyaWVzID0gJ3Bvc3RncmVzX2Zk dyciID4+ICREMS9wb3N0Z3Jlc3FsLmNvbmYKZWNobyAic2hhcmVkX3ByZWxvYWRfbGlicmFy aWVzID0gJ3Bvc3RncmVzX2ZkdyciID4+ICREMi9wb3N0Z3Jlc3FsLmNvbmYKZWNobyAiZW5h YmxlX3BhcnRpdGlvbndpc2Vfam9pbiA9IHRydWUiID4+ICREMS9wb3N0Z3Jlc3FsLmNvbmYK ZWNobyAiZW5hYmxlX3BhcnRpdGlvbndpc2VfYWdncmVnYXRlID0gdHJ1ZSIgPj4gJEQxL3Bv c3RncmVzcWwuY29uZgoKZWNobyAibGlzdGVuX2FkZHJlc3NlcyA9ICcqJyIgPj4gJEQxL3Bv c3RncmVzcWwuY29uZgplY2hvICJsaXN0ZW5fYWRkcmVzc2VzID0gJyonIiA+PiAkRDIvcG9z dGdyZXNxbC5jb25mCmVjaG8gImhvc3QgICAgYWxsICAgICAgICAgICAgIGFsbCAgICAgICAg ICAgICAwLjAuMC4wLzAgICAgICAgICAgICAgICAgIHRydXN0IiA+PiAkRDEvcGdfaGJhLmNv bmYKZWNobyAiaG9zdCAgICBhbGwgICAgICAgICAgICAgYWxsICAgICAgICAgICAgIDAuMC4w LjAvMCAgICAgICAgICAgICAgICAgdHJ1c3QiID4+ICREMi9wZ19oYmEuY29uZgoKIyBUYWtl b2ZmCnBnX2N0bCAtdyAtYyAtbyAiLXAgNTQzMyIgLUQgJEQyIC1sIG4yLmxvZyBzdGFydApw Z19jdGwgLXcgLWMgLW8gIi1wIDU0MzIiIC1EICREMSAtbCBuMS5sb2cgc3RhcnQKY3JlYXRl ZGIgLXAgNTQzMgpjcmVhdGVkYiAtcCA1NDMzCgojIEluaXQgZm9yZWlnbiB0YWJsZXMKcHNx bCAtcCA1NDMzIC1jICJDUkVBVEUgRVhURU5TSU9OIHBvc3RncmVzX2ZkdzsiCnBzcWwgLXAg NTQzMiAtYyAiQ1JFQVRFIEVYVEVOU0lPTiBwb3N0Z3Jlc19mZHc7IgoKcHNxbCAtcCA1NDMy IC1jICJDUkVBVEUgU0VSVkVSIHJlbW90ZSBGT1JFSUdOIERBVEEgV1JBUFBFUiBwb3N0Z3Jl c19mZHcgCglPUFRJT05TIChwb3J0ICc1NDMzJywgdXNlX3JlbW90ZV9lc3RpbWF0ZSAnb24n KTsKCUNSRUFURSBVU0VSIE1BUFBJTkcgRk9SIFBVQkxJQyBTRVJWRVIgcmVtb3RlOyIKCiMg Q3JlYXRlIHBhcnRpdGlvbnMKcHNxbCAtcCA1NDMyIC1jICIKCUNSRUFURSBUQUJMRSBwYXJ0 cyAoYSBpbnQsIGIgaW50KSBQQVJUSVRJT04gQlkgSEFTSChhKTsKCUNSRUFURSBUQUJMRSBw YXJ0XzAgUEFSVElUSU9OIE9GIHBhcnRzIEZPUiBWQUxVRVMgV0lUSCAobW9kdWx1cyAkUEFS VFNOVU0sIHJlbWFpbmRlciAwKTsKCUNSRUFURSBUQUJMRSBzZWNvbmQgKGEgaW50LCBiIGlu dCkgUEFSVElUSU9OIEJZIEhBU0goYSk7CglDUkVBVEUgVEFCTEUgc2Vjb25kXzAgUEFSVElU SU9OIE9GIHNlY29uZCBGT1IgVkFMVUVTIFdJVEggKG1vZHVsdXMgJFBBUlRTTlVNLCByZW1h aW5kZXIgMCk7IgoKZm9yICgoIGk9MTsgaSA8ICRQQVJUU05VTTsgaSsrICkpCmRvCgllY2hv ICJjcmVhdGUgcGFydGl0aW9uICRpLiIKCXBzcWwgLXAgNTQzMyAtYyAiQ1JFQVRFIFRBQkxF IHBhcnRfJGkgKGEgaW50LCBiIGludCkiCglwc3FsIC1wIDU0MzIgLWMgIgoJCUNSRUFURSBG T1JFSUdOIFRBQkxFIHBhcnRfJGkgUEFSVElUSU9OIE9GIHBhcnRzIAoJCUZPUiBWQUxVRVMg V0lUSCAobW9kdWx1cyAkUEFSVFNOVU0sIHJlbWFpbmRlciAkaSkgU0VSVkVSIHJlbW90ZTsi CgoJcHNxbCAtcCA1NDMzIC1jICJDUkVBVEUgVEFCTEUgc2Vjb25kXyRpIChhIGludCwgYiBp bnQpIgoJcHNxbCAtcCA1NDMyIC1jICIKCQlDUkVBVEUgRk9SRUlHTiBUQUJMRSBzZWNvbmRf JGkgUEFSVElUSU9OIE9GIHNlY29uZCAKCQlGT1IgVkFMVUVTIFdJVEggKG1vZHVsdXMgJFBB UlRTTlVNLCByZW1haW5kZXIgJGkpIFNFUlZFUiByZW1vdGU7Igpkb25lCgojcHNxbCAtcCA1 NDMyIC1jICJBTFRFUiBUQUJMRSBwYXJ0cyBBREQgUFJJTUFSWSBLRVkgKGEpIgojcHNxbCAt cCA1NDMyIC1jICJBTFRFUiBUQUJMRSBzZWNvbmQgQUREIFBSSU1BUlkgS0VZIChhKSIKcHNx bCAtcCA1NDMyIC1jICJJTlNFUlQgSU5UTyBwYXJ0cyAoYikgKFNFTEVDVCAqIEZST00gZ2Vu ZXJhdGVfc2VyaWVzKDEsIDFlNSkgYXMgZykiCnBzcWwgLXAgNTQzMiAtYyAiSU5TRVJUIElO VE8gc2Vjb25kIChiKSAoU0VMRUNUICogRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMWUzKSBh cyBnKSIKcHNxbCAtcCA1NDMyIC1jICJhbmFseXplIHBhcnRzIgoKIyBUaGUgZW5kIG9mIGRl cGxveQoKIyBzZWxlY3QgKiBmcm9tIHBhcnRzOwojIGV4cGxhaW4gc2VsZWN0ICogZnJvbSBw YXJ0czsKIyBTRUxFQ1QgKiBGUk9NIHBhcnRzIEFTIGEsIChTRUxFQ1QgKiBGUk9NIHBhcnRz KSBBUyBiIFdIRVJFIGEuYSAhPSBiLmI7CiMgU0VMRUNUIHN1bShiKSBGUk9NIHBhcnRzLCBz ZWNvbmQgV0hFUkUgcGFydHMuYSA9IHNlY29uZC5hOwojIGV4cGxhaW4gU0VMRUNUICogRlJP TSBwYXJ0cywgc2Vjb25kIFdIRVJFIHBhcnRzLmEgPSBzZWNvbmQuYSBBTkQgcGFydHMuYiA8 IDEwMDsKIyBleHBsYWluIFNFTEVDVCBzdW0ocGFydHMuYikgRlJPTSBwYXJ0cywgc2Vjb25k IFdIRVJFIHBhcnRzLmEgPSBzZWNvbmQuYSBBTkQgcGFydHMuYiA8IDEwMDsK --------------B8E8BBD8660275C47BDC99FE Content-Type: text/plain; charset=UTF-8; name="explain_partial_aggregates_problem.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="explain_partial_aggregates_problem.txt" Execution without asynchronous append ===================================== explain analyze SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a AND second.b < 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2144.36..2144.37 rows=1 width=8) (actual time=25.821..25.821 rows=1 loops=1) -> Append (cost=463.39..2144.35 rows=4 width=8) (actual time=9.495..25.816 rows=4 loops=1) -> Partial Aggregate (cost=463.39..463.40 rows=1 width=8) (actual time=9.495..9.495 rows=1 loops=1) -> Hash Join (cost=5.58..463.33 rows=27 width=4) (actual time=0.109..9.486 rows=27 loops=1) Hash Cond: (parts.a = second.a) -> Seq Scan on part_0 parts (cost=0.00..363.26 rows=25126 width=8) (actual time=0.018..5.901 rows=25126 loops=1) -> Hash (cost=5.24..5.24 rows=27 width=4) (actual time=0.084..0.084 rows=27 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on second_0 second (cost=0.00..5.24 rows=27 width=4) (actual time=0.014..0.071 rows=27 loops=1) Filter: (b < 100) Rows Removed by Filter: 232 -> Partial Aggregate (cost=560.68..560.69 rows=1 width=8) (actual time=6.017..6.017 rows=1 loops=1) -> Foreign Scan (cost=105.29..560.61 rows=29 width=4) (actual time=6.008..6.011 rows=30 loops=1) Relations: (part_1 parts_1) INNER JOIN (second_1) -> Partial Aggregate (cost=560.88..560.89 rows=1 width=8) (actual time=5.920..5.920 rows=1 loops=1) -> Foreign Scan (cost=105.75..560.82 rows=24 width=4) (actual time=5.908..5.912 rows=25 loops=1) Relations: (part_2 parts_2) INNER JOIN (second_2) -> Partial Aggregate (cost=559.33..559.34 rows=1 width=8) (actual time=4.380..4.381 rows=1 loops=1) -> Foreign Scan (cost=105.09..559.29 rows=16 width=4) (actual time=4.371..4.373 rows=17 loops=1) Relations: (part_3 parts_3) INNER JOIN (second_3) Planning Time: 6.734 ms Execution Time: 26.079 ms Execution with asynchronous append ================================== explain analyze SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a AND second.b < 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=5758.83..5758.84 rows=1 width=8) (actual time=184.849..184.849 rows=1 loops=1) -> Append (cost=727.82..5758.82 rows=4 width=8) (actual time=11.735..184.843 rows=4 loops=1) -> Partial Aggregate (cost=727.82..727.83 rows=1 width=8) (actual time=11.735..11.735 rows=1 loops=1) -> Hash Join (cost=677.34..725.94 rows=753 width=4) (actual time=11.693..11.729 rows=27 loops=1) Hash Cond: (second.a = parts.a) -> Seq Scan on second_0 second (cost=0.00..38.25 rows=753 width=4) (actual time=0.024..0.052 rows=27 loops=1) Filter: (b < 100) Rows Removed by Filter: 232 -> Hash (cost=363.26..363.26 rows=25126 width=8) (actual time=11.644..11.644 rows=25126 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1238kB -> Seq Scan on part_0 parts (cost=0.00..363.26 rows=25126 width=8) (actual time=0.013..5.595 rows=25126 loops=1) -> Partial Aggregate (cost=1676.97..1676.98 rows=1 width=8) (actual time=58.958..58.958 rows=1 loops=1) -> Hash Join (cost=1377.15..1440.85 rows=94449 width=4) (actual time=58.922..58.948 rows=30 loops=1) Hash Cond: (second_1.a = parts_1.a) -> Foreign Scan on second_1 (cost=100.00..153.31 rows=753 width=4) (actual time=0.366..0.374 rows=30 loops=1) -> Hash (cost=963.58..963.58 rows=25086 width=8) (actual time=58.534..58.534 rows=24978 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1232kB -> Foreign Scan on part_1 parts_1 (cost=100.00..963.58 rows=25086 width=8) (actual time=0.355..51.993 rows=24978 loops=1) -> Partial Aggregate (cost=1676.97..1676.98 rows=1 width=8) (actual time=57.126..57.126 rows=1 loops=1) -> Hash Join (cost=1377.15..1440.85 rows=94449 width=4) (actual time=57.105..57.120 rows=25 loops=1) Hash Cond: (second_2.a = parts_2.a) -> Foreign Scan on second_2 (cost=100.00..153.31 rows=753 width=4) (actual time=0.329..0.333 rows=25 loops=1) -> Hash (cost=963.58..963.58 rows=25086 width=8) (actual time=56.750..56.750 rows=24971 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1232kB -> Foreign Scan on part_2 parts_2 (cost=100.00..963.58 rows=25086 width=8) (actual time=0.362..50.083 rows=24971 loops=1) -> Partial Aggregate (cost=1676.99..1677.00 rows=1 width=8) (actual time=57.020..57.020 rows=1 loops=1) -> Hash Join (cost=1377.15..1440.87 rows=94449 width=4) (actual time=57.005..57.015 rows=17 loops=1) Hash Cond: (second_3.a = parts_3.a) -> Foreign Scan on second_3 (cost=100.00..153.31 rows=753 width=4) (actual time=0.280..0.283 rows=17 loops=1) -> Hash (cost=963.58..963.58 rows=25086 width=8) (actual time=56.656..56.656 rows=24925 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1230kB -> Foreign Scan on part_3 parts_3 (cost=100.00..963.58 rows=25086 width=8) (actual time=0.310..50.014 rows=24925 loops=1) Planning Time: 5.809 ms Execution Time: 185.640 ms (34 rows) --------------B8E8BBD8660275C47BDC99FE--