Fast COPY FROM based on batch insert - Mailing list pgsql-hackers

From Andrey Lepikhov
Subject Fast COPY FROM based on batch insert
Date
Msg-id bc489202-9855-7550-d64c-ad2d83c24867@postgrespro.ru
Whole thread Raw
In response to Re: [POC] Fast COPY FROM command for the table with foreign partitions  (Etsuro Fujita <etsuro.fujita@gmail.com>)
Responses RE: Fast COPY FROM based on batch insert
Re: Fast COPY FROM based on batch insert
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Daniel Gustafsson
Date:
Subject: A few random typos in the docs
Next
From: Kyotaro Horiguchi
Date:
Subject: detailed error message of pg_waldump