Re: smart copy?

On Fri, 26 Apr 2002, Joel Burton wrote:
>
> [edited some stuff out here]
>
> It's slower than running COPY, since you're executing
> dozens/hundreds/kajillions of INSERT commands rather than one COPY command.
> If that's a problem, you can reduce it a bit by dropping indexes and
> triggers on B.TheTable, doing the INSERTs, then re-adding these.

Ah, yes, the old dropping indexes etc. for loading data. I realise this is just
about standard practice on more than just postgres but does it really make a
difference?

I ask this because I have recently (and am doing so again) reloading a large
table with foriegn key constraints and indexes. I drop the table and recreate
it with out the foriegn keys, thus killing the FK triggers and indexes. [Well
actually I keep the primary key there]. Loading the data is fine. Sure it takes
a while but then it takes a while to write out to file in the first
place. However, when it comes to recreating the constraints and indexes it
takes ages. The indexes took hours if I recall correctly.

Has anyone actually done tests to confirm that load+index+constraint time is
less that just a plain load with everything still enabled?

Is the way pg_dump disables some of these things more efficient some how?

I am obviously being a tad lazy here,I could run tests myself, but I'm
currently restoring this table again and I'm thinking someone must have done
this in the past anyway.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants