From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | mayank(dot)l(dot)patel90(at)gmail(dot)com, pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Difference between Bulk Load (Multiple inserts or single inserts) and COPY |
Date: | 2019-12-01 00:55:50 |
Message-ID: | 20191201005550.GC4326@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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(at)momjian(dot)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 +
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2019-12-01 22:38:49 | Re: Schema Usage Patterns vs. dbowner or CREATEROLE |
Previous Message | Alvaro Herrera | 2019-11-28 14:22:20 | Re: Example not producing shown output |