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?
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 |