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 1jkltl-0005MW-Cp for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2020 09:59:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jkltk-0003Ri-1L for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2020 09:59:24 +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 1jkltj-0003RZ-Ma for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2020 09:59:23 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jkltg-0007GI-Uz for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2020 09:59:23 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 9719721C6D2B; Mon, 15 Jun 2020 12:59:19 +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.210]) (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 0C60B21C6D27; Mon, 15 Jun 2020 12:59:18 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1592215159; bh=yIkN9IpkQnmwDVc6Ox6Ktqg0+uhSJcQ7wbCnoMvqFjw=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=lKj1XTUN7I5h/B9f4sh5koWU+AAVrChnTkFdQ9x6WRzdpxPPPbcPeSuZeLI63VI6y iMEOh/Z/LL2qoiJZAN55z7CpqbCMsC2UZWoSHWpIHAnrGgGd+60PfI0gjQSP6R57Cj 9fK8Y7nR7zNH2yK1qiy2sTROZoboH07n0ROdV748= 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: <90cced83-9061-0cac-0012-84e5faac8c78@postgrespro.ru> <20200610.120510.2075667454883130505.horikyota.ntt@gmail.com> <20200615.172934.587211238374081803.horikyota.ntt@gmail.com> From: "Andrey V. Lepikhov" Organization: Postgres Professional Message-ID: <3aa386ee-99d7-551f-85cf-c13b78947d4f@postgrespro.ru> Date: Mon, 15 Jun 2020 14:59:18 +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: <20200615.172934.587211238374081803.horikyota.ntt@gmail.com> Content-Type: multipart/mixed; boundary="------------4557AC3C15FDFDAA3E958D2E" 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. --------------4557AC3C15FDFDAA3E958D2E Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 6/15/20 1:29 PM, Kyotaro Horiguchi wrote: > Thanks for testing, but.. > > At Mon, 15 Jun 2020 08:51:23 +0500, "Andrey V. Lepikhov" wrote in >> 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. > > I had more or less the same plan with the second one without the patch > (that is, vanilla master/HEAD, but used merge joins instead). > > I'm not sure what prevented join pushdown, but the difference between > the two is whether the each partitionwise join is pushed down to > remote or not, That is hardly seems related to the async execution > patch. > > Could you tell me how did you get the first plan? 1. Use clear current vanilla master. 2. Start two instances with the script 'frgn2n.sh' from attachment. There are I set GUCs: enable_partitionwise_join = true enable_partitionwise_aggregate = true 3. Execute query: explain analyze SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a AND second.b < 100; That's all. -- Andrey Lepikhov Postgres Professional https://postgrespro.com --------------4557AC3C15FDFDAA3E958D2E 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 cyAoYSBzZXJpYWwsIGIgaW50KSBQQVJUSVRJT04gQlkgSEFTSChhKTsKCUNSRUFURSBUQUJM RSBwYXJ0XzAgUEFSVElUSU9OIE9GIHBhcnRzIEZPUiBWQUxVRVMgV0lUSCAobW9kdWx1cyAk UEFSVFNOVU0sIHJlbWFpbmRlciAwKTsKCUNSRUFURSBUQUJMRSBzZWNvbmQgKGEgc2VyaWFs LCBiIGludCkgUEFSVElUSU9OIEJZIEhBU0goYSk7CglDUkVBVEUgVEFCTEUgc2Vjb25kXzAg UEFSVElUSU9OIE9GIHNlY29uZCBGT1IgVkFMVUVTIFdJVEggKG1vZHVsdXMgJFBBUlRTTlVN LCByZW1haW5kZXIgMCk7IgoKZm9yICgoIGk9MTsgaSA8ICRQQVJUU05VTTsgaSsrICkpCmRv CgllY2hvICJjcmVhdGUgcGFydGl0aW9uICRpLiIKCXBzcWwgLXAgNTQzMyAtYyAiQ1JFQVRF IFRBQkxFIHBhcnRfJGkgKGEgaW50LCBiIGludCkiCglwc3FsIC1wIDU0MzIgLWMgIgoJCUNS RUFURSBGT1JFSUdOIFRBQkxFIHBhcnRfJGkgUEFSVElUSU9OIE9GIHBhcnRzIAoJCUZPUiBW QUxVRVMgV0lUSCAobW9kdWx1cyAkUEFSVFNOVU0sIHJlbWFpbmRlciAkaSkgU0VSVkVSIHJl bW90ZTsiCgoJcHNxbCAtcCA1NDMzIC1jICJDUkVBVEUgVEFCTEUgc2Vjb25kXyRpIChhIHNl cmlhbCwgYiBpbnQpIgoJcHNxbCAtcCA1NDMyIC1jICIKCQlDUkVBVEUgRk9SRUlHTiBUQUJM RSBzZWNvbmRfJGkgUEFSVElUSU9OIE9GIHNlY29uZCAKCQlGT1IgVkFMVUVTIFdJVEggKG1v ZHVsdXMgJFBBUlRTTlVNLCByZW1haW5kZXIgJGkpIFNFUlZFUiByZW1vdGU7Igpkb25lCgpw c3FsIC1wIDU0MzIgLWMgIklOU0VSVCBJTlRPIHBhcnRzIChiKSAoU0VMRUNUICogRlJPTSBn ZW5lcmF0ZV9zZXJpZXMoMSwgMWU1KSBhcyBnKSIKcHNxbCAtcCA1NDMyIC1jICJJTlNFUlQg SU5UTyBzZWNvbmQgKGIpIChTRUxFQ1QgKiBGUk9NIGdlbmVyYXRlX3NlcmllcygxLCAxZTMp IGFzIGcpIgpwc3FsIC1wIDU0MzIgLWMgImFuYWx5emUgcGFydHMiCgojIFRoZSBlbmQgb2Yg ZGVwbG95CgojIHNlbGVjdCAqIGZyb20gcGFydHM7CiMgZXhwbGFpbiBzZWxlY3QgKiBmcm9t IHBhcnRzOwojIFNFTEVDVCAqIEZST00gcGFydHMgQVMgYSwgKFNFTEVDVCAqIEZST00gcGFy dHMpIEFTIGIgV0hFUkUgYS5hICE9IGIuYjsKIyBTRUxFQ1Qgc3VtKGIpIEZST00gcGFydHMs IHNlY29uZCBXSEVSRSBwYXJ0cy5hID0gc2Vjb25kLmE7CiMgZXhwbGFpbiBTRUxFQ1QgKiBG Uk9NIHBhcnRzLCBzZWNvbmQgV0hFUkUgcGFydHMuYSA9IHNlY29uZC5hIEFORCBwYXJ0cy5i IDwgMTAwOwojIGV4cGxhaW4gU0VMRUNUIHN1bShwYXJ0cy5iKSBGUk9NIHBhcnRzLCBzZWNv bmQgV0hFUkUgcGFydHMuYSA9IHNlY29uZC5hIEFORCBwYXJ0cy5iIDwgMTAwOwo= --------------4557AC3C15FDFDAA3E958D2E--