RE: [POC] Fast COPY FROM command for the table with foreign partitions

Tang, Haiying <tanghy.fnst@cn.fujitsu.com>

From: "Tang, Haiying" <tanghy.fnst@cn.fujitsu.com>
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Alexey Kondratov <a.kondratov@postgrespro.ru>, Michael Paquier <michael@paquier.xyz>, Ashutosh Bapat <ashutosh.bapat@2ndquadrant.com>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Etsuro Fujita <etsuro.fujita@gmail.com>, PostgreSQL-Dev <pgsql-hackers@postgresql.org>, "Hou, Zhijie" <houzj.fnst@cn.fujitsu.com>, Amit Langote <amitlangote09@gmail.com>, "Tsunakawa, Takayuki" <tsunakawa.takay@fujitsu.com>
Date: 2021-01-11T11:59:13Z
Lists: pgsql-hackers

Attachments

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


Commits

  1. Allow batch insertion during COPY into a foreign table.

  2. postgres_fdw: Disable batch insertion when there are WCO constraints.

  3. Enforce foreign key correctly during cross-partition updates

  4. Fix permission checks on constraint violation errors on partitions.

  5. Fix compilation of uuid-ossp