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 1l9M2A-0007rG-GD for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Feb 2021 05:57:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l9M29-0007nl-DJ for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Feb 2021 05:57:57 +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 1l9M29-0007ne-1Z for pgsql-hackers@lists.postgresql.org; Tue, 09 Feb 2021 05:57:57 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l9M24-0002BO-27 for pgsql-hackers@postgresql.org; Tue, 09 Feb 2021 05:57:56 +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 B80D521C35F7; Tue, 9 Feb 2021 08:57:46 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1612850269; bh=7e0vDMcAlZmoEYnzURRJqHsBq+/R8khagGZJ6H5SLYI=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=IzHQ1sDIVhxuMjl0xyu7stTEnunPUcSROy/5/mnHu4T5UVGVvCiypBL2200FUsIlS bmAepSLO1ErTyYb/i58xAuAbCCcJ06HsyVaAWeAmwl95HMSER/yyKPoN0p3nVc42tl j0zpL2pr5bx6Vf4b8A4Bv8NSYfn0MTC2QLeSgKsM= 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> From: "Andrey V. Lepikhov" Organization: Postgres Professional Message-ID: <737f24c8-3f0c-4201-e3da-b763dbf9c8b9@postgrespro.ru> Date: Tue, 9 Feb 2021 10:57:44 +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 9:35 AM, tsunakawa.takay@fujitsu.com wrote: > From: tsunakawa.takay@fujitsu.com >> From: Andrey Lepikhov >> Also, I might defer working on the extended part (v9 0003 and 0004) and further >> separate them in a different thread, if it seems to take longer. > > I reviewed them but haven't rebased them (it seems to take more labor.) > Andrey-san, could you tell us: > > * Why is a separate FDW connection established for each COPY? To avoid using the same FDW connection for multiple foreign table partitions in a single COPY run? With separate connection you can init a 'COPY FROM' session for each foreign partition just one time on partition initialization. > > * In what kind of test did you get 2-4x performance gain? COPY into many foreign table partitions where the input rows are ordered randomly enough that many rows don't accumulate in the COPY buffer? I used 'INSERT INTO .. SELECT * FROM generate_series(1, N)' to generate test data and HASH partitioning to avoid skews. -- regards, Andrey Lepikhov Postgres Professional