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
- test_copy_from.sql (application/octet-stream)
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
-
Allow batch insertion during COPY into a foreign table.
- 97da48246d34 16.0 landed
-
postgres_fdw: Disable batch insertion when there are WCO constraints.
- 82593b9a3d5f 16.0 cited
-
Enforce foreign key correctly during cross-partition updates
- ba9a7e392171 15.0 cited
-
Fix permission checks on constraint violation errors on partitions.
- 6214e2b22804 14.0 cited
-
Fix compilation of uuid-ossp
- 525e60b74299 14.0 cited