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

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