[Postgres-xc-general] INSERT vs COPY performance

[Aaron J. <aja...@re...>]

When I load data into my table "detail" with COPY, the table loads at a rate of about 56k rows per second.  The data is distributed on a key to achieve this rate of insert (width is 678).  However, when I do the following:

INSERT INTO DETAIL SELECT 123 as Id, ... FROM DETAIL WHERE Id = 500;

I see the write performance drop to only 2.5K rows per second.  The total data set loaded from Id = 500 is 200k rows and takes about 7s to load into the data coordinator.  So, I can attribute almost all of the time (about 80 seconds) directly to the insert.

Insert on detail  (cost=0.00..10.00 rows=1000 width=678) (actual time=79438.038..79438.038 rows=0 loops=1)
   Node/s: node_pgs01_1, node_pgs01_2, node_pgs02_1, node_pgs02_2
   Node expr: productid
   ->  Data Node Scan on detail "_REMOTE_TABLE_QUERY_"  (cost=0.00..10.00 rows=1000 width=678) (actual time=3.917..2147.231 rows=200000 loops=1)
         Node/s: node_pgs01_1, node_pgs01_2, node_pgs02_1, node_pgs02_2

IMO, it seems like an insert like this should approach the performance of a COPY.  Am I missing something or can you recommend a different approach?