Date: Sat, 30 Nov 2019 19:55:50 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: mayank.l.patel90@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Difference between Bulk Load (Multiple inserts or single
 inserts) and COPY
Message-ID: <20191201005550.GC4326@momjian.us>
References: <157418972366.28426.16885409770050714771@wrigleys.postgresql.org>
 <41b4852ea43e59969cfefaf37c4578a23000075b.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <41b4852ea43e59969cfefaf37c4578a23000075b.camel@cybertec.at>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On Tue, Nov 19, 2019 at 11:55:44PM +0100, Laurenz Albe wrote:
> On Tue, 2019-11-19 at 18:55 +0000, PG Doc comments form wrote:
> > 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.
> 
> That cannot be answered without knowing the exact statements and the
> table definitions.

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  <bruce@momjian.us>        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 +