pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jason Slagle <raistlin@tacorp.net>
To: Karl Denninger <karl@Denninger.Net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Performance
Date: Wed, 10 Mar 1999 13:39:19 -0500 (EST)
Message-ID: <Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net> (raw)
In-Reply-To: <19990310123506.B18608@Denninger.Net>

Haven't created any yet, as I have not yet determined what I want to index
on.  I just created the database and bulk loaded 211 megs of : delimited
data, generating a 350 meg file in the data dir.  It has been vacuuming
for 45 mins or so now...  Prob needs more horsepower :D

Jason

---
Jason Slagle                        
Network Administrator - Toledo Internet Access - Toledo Ohio
- raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 

On Wed, 10 Mar 1999, Karl Denninger wrote:

> Depends on the indices that have to be gone through.  It can take quite a
> while.
> 
> --
> -- 
> Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl
> I ain't even *authorized* to speak for anyone other than myself, so give
> up now on trying to associate my words with any particular organization.
> 
> 
> On Wed, Mar 10, 1999 at 01:27:20PM -0500, Jason Slagle wrote:
> > And how long is normal for Vacuum analyze to take on bout 350 megs of
> > DATA?
> > 
> > Jason
> > 
> > ---
> > Jason Slagle                        
> > Network Administrator - Toledo Internet Access - Toledo Ohio
> > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 
> > 
> > On Wed, 10 Mar 1999, Tom Lane wrote:
> > 
> > > "Brett W. McCoy" <bmccoy@lan2wan.com> writes:
> > > >> I have 1,400,000 entries (200MB) I'm inserting into a database.  Radius
> > > >> detail files as a matter of fact.  Apart from COPY taking forever to load
> > > >> that (probably due to my several indexes), it seems the select is VERY
> > > >> slow.  Any tips?
> > > 
> > > > I found that if you create an index before doing a bulk COPY, yes, it does
> > > > take forever to load, and the select is slow.  What I did was drop the
> > > > indices built from the COPY and rebuild them.  Speeded the selects up
> > > > significantly.  So now I don't build any indices until after I load my
> > > > huge databases in.
> > > 
> > > Not building the indexes until you've done the bulk load is good advice;
> > > it does seem a lot faster to build an index on an already-loaded table
> > > than to construct it piecemeal during the COPY.  However, either way
> > > should result in the same index, so I don't see why it'd affect the
> > > speed of a subsequent SELECT.  Did you remember to do VACUUM ANALYZE
> > > both times?  The system is likely to ignore the index until you have
> > > vacuumed the table.
> > > 
> > > In short, best bulk load procedure is
> > > 
> > > 	CREATE TABLE ...
> > > 	COPY ...
> > > 	CREATE INDEX(es) on table
> > > 	Repeat as needed for all tables being bulk-loaded
> > > 	VACUUM ANALYZE
> > > 
> > > BTW, if you use pg_dump to dump and reload a big database, pg_dump
> > > knows about the create-indexes-last trick.  But it doesn't do a VACUUM
> > > for you; you have to do that by hand after running the reload script,
> > > or your database will be slow.
> > > 
> > > 			regards, tom lane
> > > 
> > 
> > 
> 




view thread (19+ messages)  latest in thread

Message-ID: <Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net>
Permalink:  ../Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net

 · 

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: raistlin@tacorp.net, karl@Denninger.Net, tgl@sss.pgh.pa.us, pgsql-sql@hub.org
  Subject: Re: [SQL] Performance
  In-Reply-To: <Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net>
  References: <19990310123506.B18608@Denninger.Net> <Pine.LNX.4.03.9903101336240.20770-100000@tacorp.net>

* 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