Hi,
We still have slow 'COPY FROM' operation for foreign tables in current
master.
Now we have a foreign batch insert operation And I tried to rewrite the
patch [1] with this machinery.
The patch (see in attachment) smaller than [1] and no changes required
in FDW API.
Benchmarking
============
I used two data sets: with a number of 1E6 and 1E7 tuples. As a foreign
server emulation I used loopback FDW links.
Test table:
CREATE TABLE test(a int, payload varchar(80));
Execution time of COPY FROM into single foreign table:
version | 1E6 tuples | 1E7 tuples |
master: | 64s | 775s |
Patch [1]: | 5s | 50s |
Current: | 4s | 42s |
Execution time of the COPY operation into a plane table is 0.8s for 1E6
tuples and 8s for 1E7 tuples.
Execution time of COPY FROM into the table partitioned by three foreign
partitions:
version | 1E6 tuples | 1E7 tuples |
master: | 85s | 900s |
Patch [1]: | 10s | 100s |
Current: | 3.5s | 34s |
But the bulk insert execution time in current implementation strongly
depends on MAX_BUFFERED_TUPLES/BYTES value and in my experiments was
reduced to 50s.
[1]
https://www.postgresql.org/message-id/flat/3d0909dc-3691-a576-208a-90986e55489f%40postgrespro.ru
--
regards,
Andrey Lepikhov
Postgres Professional
