pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dirk Lutzebaeck <lutzeb@aeccom.com>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] best strategy doing a large copy and using indexes
Date: Tue, 11 Jan 2000 11:14:35 -0500
Message-ID: <4618.947607275@sss.pgh.pa.us> (raw)
In-Reply-To: <14459.19087.735901.684613@ampato.aeccom.com>
References: <14459.19087.735901.684613@ampato.aeccom.com>

Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> I have a table with 7 individual indexes (it models an object store
> with different types) and need to copy large data lists into this
> table regularly. I'm using COPY for it but it is unacceptable slow
> (4min for 1000 rows on a Linux/PII-400). I'm anticipating more than
> 20000 rows to copy. What combination of the following options is the
> best strategy to make this more efficient?

> a) drop indexes before COPY and recreate them after COPY

That should certainly help a great deal (unless the 20000 rows are
a small number compared to what's already in the table --- in that
case, the cost of reindexing the existing rows might outweigh the
efficiency of indexing the new rows "wholesale" instead of "retail").

Also, if you have any triggers or anything like that, you might consider
turning them off for the duration of the copy.

> b) put COPY in a transaction

Won't do anything --- COPY is only one transaction command anyway.

> c) do VACUUM before (or after?)
> d) do VACUUM ANALYZE before (or after?)

These wouldn't directly affect the speed of COPY, afaik.  You should
consider doing a VACUUM ANALYZE after adding any large amount of data
to a table, so that the optimizer has reasonably up-to-date info about
the size of the table.  But that's only going to affect subsequent
queries, not the COPY itself.

> e) COPY to temp table and then make an INSERT SELECT FROM temp table

That would be more total elapsed time, but if your main concern is to
minimize the downtime of your primary table, I suppose there could be
reason to do it that way.

			regards, tom lane



view thread (2+ messages)

Message-ID: <4618.947607275@sss.pgh.pa.us>
Permalink:  ../4618.947607275@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4618.947607275@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, lutzeb@aeccom.com, pgsql-sql@hub.org
  Subject: Re: [SQL] best strategy doing a large copy and using indexes
  In-Reply-To: <4618.947607275@sss.pgh.pa.us>
  References: <14459.19087.735901.684613@ampato.aeccom.com> <4618.947607275@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox