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 1l9OIF-00062b-28 for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Feb 2021 08:22:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l9OID-0000X4-FR for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Feb 2021 08:22:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l9OID-0000Wx-4g for pgsql-hackers@lists.postgresql.org; Tue, 09 Feb 2021 08:22:41 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l9OIA-0003Oc-Bd for pgsql-hackers@postgresql.org; Tue, 09 Feb 2021 08:22:40 +0000 Received: from [192.168.129.213] (unknown [94.25.177.42]) (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 051D821C7961; Tue, 9 Feb 2021 11:22:34 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1612858955; bh=cLVIGdWL96M2k3cq087Sh4Tj4El6fd6a95HLijaSo/o=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=eU72V5e5jy0s0mcCCt6+XXNGWKTW6kI1QXCvik8JVsq2O7CvK4EmjUabSQWK2YkZU XKgYF6YzNFUH1ouGwWEmwHx3QLsuEFUBrA7nwPE+SXZlOsnFRXx6wqec//Xdx9v016 A/e4svIY4MuEga616WS/rqFIBV7OOdcPu+wSyo/4= Subject: Re: [POC] Fast COPY FROM command for the table with foreign partitions To: "tsunakawa.takay@fujitsu.com" Cc: "tanghy.fnst@cn.fujitsu.com" , Alexey Kondratov , Michael Paquier , Ashutosh Bapat , Ashutosh Bapat , Etsuro Fujita , PostgreSQL-Dev , Tomas Vondra , "houzj.fnst@cn.fujitsu.com" , Amit Langote References: <9bff5ccc-8649-9699-1cdd-82b53164f9bb@postgrespro.ru> <7166ded145a947fbb11b67ebe68041f3@G08CNEXMBPEKD05.g08.fujitsu.local> <2b465f4cdeeb4a78b3096d31e8af7c77@G08CNEXMBPEKD05.g08.fujitsu.local> <9862b608-884b-da1f-afb5-2ee5f2396257@enterprisedb.com> <6160196a-88a4-ded8-1047-cec16537a111@postgrespro.ru> <95b3e446-92ec-f01f-2035-5f5509e6e016@postgrespro.ru> <737f24c8-3f0c-4201-e3da-b763dbf9c8b9@postgrespro.ru> From: "Andrey V. Lepikhov" Organization: Postgres Professional Message-ID: <4397cd58-1fcc-73f2-6565-4b3d1bd432bf@postgrespro.ru> Date: Tue, 9 Feb 2021 13:22:33 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2/9/21 12:47 PM, tsunakawa.takay@fujitsu.com wrote: > From: Andrey V. Lepikhov > I guess you used many hash partitions. Sadly, The current COPY implementation only accumulates either 1,000 rows or 64 KB of input data (very small!) before flushing all CopyMultiInsertBuffers. One CopyMultiInsertBuffer corresponds to one partition. Flushing a CopyMultiInsertBuffer calls ExecForeignCopy() once, which connects to a remote database, runs COPY FROM STDIN, and disconnects. Here, the flushing trigger (1,000 rows or 64 KB input data, whichever comes first) is so small that if there are many target partitions, the amount of data for each partition is small. I tried to use 1E4 - 1E8 rows in a tuple buffer. But the results weren't impressive. We can use one more GUC instead of a precompiled constant. > Why don't we focus on committing the basic part and addressing the extended part (0003 and 0004) separately later? I focused only on the 0001 and 0002 patches. > As Tang-san and you showed, the basic part already demonstrated impressive improvement. If there's no objection, I'd like to make this ready for committer in a few days. Good. -- regards, Andrey Lepikhov Postgres Professional