Re: smart copy? - Mailing list pgsql-admin

From Nigel J. Andrews
Subject Re: smart copy?
Date
Msg-id Pine.LNX.4.21.0204262244580.965-100000@ponder.fairway2k.co.uk
Whole thread Raw
In response to Re: smart copy?  ("Joel Burton" <joel@joelburton.com>)
List pgsql-admin
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


pgsql-admin by date:

Previous
From: "Gaetano Mendola"
Date:
Subject: Re: RPMS
Next
From: "Gaetano Mendola"
Date:
Subject: Re: strange behaviour