pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
To: Tang, Haiying <tanghy.fnst@cn.fujitsu.com>
Cc: Alexey Kondratov <a.kondratov@postgrespro.ru>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Ashutosh Bapat <ashutosh.bapat@2ndquadrant.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: PostgreSQL-Dev <pgsql-hackers@postgresql.org>
Cc: Hou, Zhijie <houzj.fnst@cn.fujitsu.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Tsunakawa, Takayuki <tsunakawa.takay@fujitsu.com>
Subject: Re: [POC] Fast COPY FROM command for the table with foreign partitions
Date: Tue, 12 Jan 2021 08:45:19 +0500
Message-ID: <880ddd37-bada-909e-b5b3-d9d20286e963@postgrespro.ru> (raw)
In-Reply-To: <bc75078fc9614263a0d5f006dd6f0c3c@G08CNEXMBPEKD05.g08.fujitsu.local>
References: <ae25e79e-6122-720e-1349-fb832c6021df@postgrespro.ru>
	<8ddacbf0-d489-cd56-fecc-e09a7e02a7c1@postgrespro.ru>
	<TYAPR01MB2990DC396B338C98F27C8ED3FE1F0@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<CA+HiwqEZG__hJp2Zp1o5u-brPnF985nKnTqs9ox3PKHy5U=OMw@mail.gmail.com>
	<TYAPR01MB299070A2B01D63A31337F105FEF90@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<CA+HiwqFxJksYQtGhje-VozoC3GYxyOtv5QxNb3fJ5O1FHOB-+g@mail.gmail.com>
	<TYAPR01MB29900B0604FEB2D7AD9EE61BFEF40@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<CA+HiwqFJG-cJtdWZAqyEQEnA4e-rd=H2dzF+uNSbh5O-1O9tyw@mail.gmail.com>
	<9bff5ccc-8649-9699-1cdd-82b53164f9bb@postgrespro.ru>
	<7166ded145a947fbb11b67ebe68041f3@G08CNEXMBPEKD05.g08.fujitsu.local>
	<c46c1929-d44f-0ce3-a74b-4cfaddf9da82@postgrespro.ru>
	<2b465f4cdeeb4a78b3096d31e8af7c77@G08CNEXMBPEKD05.g08.fujitsu.local>
	<ca933cda-b8c3-9e9c-23a0-91af2e2a2624@postgrespro.ru>
	<bc75078fc9614263a0d5f006dd6f0c3c@G08CNEXMBPEKD05.g08.fujitsu.local>

On 1/11/21 4:59 PM, Tang, Haiying wrote:
> Hi Andrey,
> 
> I had a general look at this extension feature, I think it's beneficial for some application scenarios of PostgreSQL. So I did 7 performance cases test on your patch(v13). The results are really good. As you can see below we can get 7-10 times improvement with this patch.
> 
> PSA test_copy_from.sql shows my test cases detail(I didn't attach my data file since it's too big).
> 
> Below are the test results:
> 'Test No' corresponds to the number(0 1...6) in attached test_copy_from.sql.
> %reg=(Patched-Unpatched)/Unpatched), Unit is millisecond.
> 
> |Test No| Test Case                                                                               |Patched(ms)  | Unpatched(ms) |%reg   |
> |-------|-----------------------------------------------------------------------------------------|-------------|---------------|-------|
> |0      |COPY FROM insertion into the partitioned table(parition is foreign table)                | 102483.223  |  1083300.907  |  -91% |
> |1      |COPY FROM insertion into the partitioned table(parition is foreign partition)            | 104779.893  |  1207320.287  |  -91% |
> |2      |COPY FROM insertion into the foreign table(without partition)                            | 100268.730  |  1077309.158  |  -91% |
> |3      |COPY FROM insertion into the partitioned table(part of foreign partitions)               | 104110.620  |  1134781.855  |  -91% |
> |4      |COPY FROM insertion into the partitioned table with constraint(part of foreign partition)| 136356.201  |  1238539.603  |  -89% |
> |5      |COPY FROM insertion into the foreign table with constraint(without partition)            | 136818.262  |  1189921.742  |  -89% |
> |6      |\copy insertion into the partitioned table with constraint.                              | 140368.072  |  1242689.924  |  -89% |
> 
> If there is any question on my tests, please feel free to ask.
> 
> Best Regard,
> Tang
Thank you for this work.
Sometimes before i suggested additional optimization [1] which can 
additionally speed up COPY by 2-4 times. Maybe you can perform the 
benchmark for this solution too?

[1] 
https://www.postgresql.org/message-id/da7ed3f5-b596-2549-3710-4cc2a602ec17%40postgrespro.ru

-- 
regards,
Andrey Lepikhov
Postgres Professional





view thread (84+ messages)  latest in thread

Message-ID: <880ddd37-bada-909e-b5b3-d9d20286e963@postgrespro.ru>
Permalink:  ../880ddd37-bada-909e-b5b3-d9d20286e963@postgrespro.ru/
Also on:    postgresql.org/message-id/880ddd37-bada-909e-b5b3-d9d20286e963@postgrespro.ru

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: a.lepikhov@postgrespro.ru, tanghy.fnst@cn.fujitsu.com, a.kondratov@postgrespro.ru, michael@paquier.xyz, ashutosh.bapat@2ndquadrant.com, ashutosh.bapat.oss@gmail.com, etsuro.fujita@gmail.com, houzj.fnst@cn.fujitsu.com, amitlangote09@gmail.com, tsunakawa.takay@fujitsu.com
  Subject: Re: [POC] Fast COPY FROM command for the table with foreign partitions
  In-Reply-To: <880ddd37-bada-909e-b5b3-d9d20286e963@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox