Re: optimising data load

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
Cc: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>, PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: optimising data load
Date: 2002-05-23 12:28:44
Message-ID: 1022156925.2411.1492.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Thu, 2002-05-23 at 21:10, John Taylor wrote:
> >
> > Oh, ok. How about creating an intermediary, indexless table
> > that the 300,000 records will be inserted into. Then, after
> > the intermediary table is populated, drop the index(es) on
> > orderlines and INSERT INTO orderlines SELECT * FROM intermediary;
> >
>
> That sounds a good idea.
> Would it be quicker to copy out from the intermediary, and then copy in to the real one ?
> I guess I'll have to experiment.

It will be quickest to:

"INSERT INTO orderlinesupdate SELECT * FROM intermediary", like Ron said

No COPY involved, except to get the records into the intermediary table
initially.

That syntax on the INSERT statement is _really_ useful for this sort of
thing.

> I have a feeling a lot of the problems may be due to the indexes.
> I've been experimenting with an easier table.
> copy in to the live takes 5 minutes for 850000 records
> copy in to the updates takes 2 hours !
> It is the same data with 3 extra columns, but there are an additional 2 indexes.
> I think it must be the indexes making the diffrence. I'm just checking now.

Also consider that if the numbers of records inthe table is changing a
lot, then it is well worth while doing a Vacuum analyze after time, so
the planner statistics get updated and the system produces queries
appropriate to a 400k record table (e.g.) rather than a 100k record
table.

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Andrew McMillan 2002-05-23 12:34:45 Re: SELECT DISTINCT
Previous Message Rory Campbell-Lange 2002-05-23 12:04:39 when to use char, varchar or text