Subject: Difference between Bulk Load (Multiple inserts or single inserts) and COPY

From: PG Doc comments form <[email-masked]>
Date: Tue, 19 Nov 2019 18:55:23 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/sql-copy.html
Description:

Hello, 

Myself Mayank. I am a Ph.D. student.

I experimented with Bulk load and COPY.
Loading in COPY was very fast. 
However, after COPYing data from a CSV file to PostgreSQL Table. The query
execution took lot of time for 1 of the first 4 queries.
Only this slow query was taking so much time, that even if I had used normal
bulk load, it would have been faster in total.
Then all other Query executions took equal time as it took while querying a
table after the Bulk data load method.

So, I want to know the exact reason what's the issue with COPY.
How exactly they differ? The only thing from the document I could identify
was row security.
But it did not mention anything about indexing. Like, in Bulk load, do
indices(or constraint checks) are created with data loading?
& in COPY it's done after? so when indices are being created that query
slows down??

Please reply soon with more details or send a link where I can read it in
depth.
Thanks.
Mayank.
[email-masked]

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 19 Nov 2019 23:55:44 +0100

> Please reply soon with more details or send a link where I can read it in

That cannot be answered without knowing the exact statements and the
table definitions.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 30 Nov 2019 19:55:50 -0500

> That cannot be answered without knowing the exact statements and the

I wonder if it is the overhead of rewriting all the rows to set the
per-row HEAP_XMIN_COMMITTED bit.  Unfortunately, I don't know a way to
test this hypothesis.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +