From: | Jason Slagle <raistlin(at)tacorp(dot)net> |
---|---|
To: | Karl Denninger <karl(at)Denninger(dot)Net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-sql(at)hub(dot)org |
Subject: | Re: [SQL] Performance |
Date: | 1999-03-10 18:51:46 |
Message-ID: | Pine.LNX.4.03.9903101351310.20770-100000@tacorp.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
And there off :D Reloading it. We'll try again :blah:
Thanks,
Jason
---
Jason Slagle
Network Administrator - Toledo Internet Access - Toledo Ohio
- raistlin(at)tacorp(dot)net - jslagle(at)toledolink(dot)com - WHOIS JS10172
On Wed, 10 Mar 1999, Karl Denninger wrote:
> That's a waste - a vacuum on a table with no deletes having been executed
> and no indices will go through everything and do absolutely nothing :-)
>
> --
> --
> Karl Denninger (karl(at)denninger(dot)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:39:19PM -0500, Jason Slagle wrote:
> > 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(at)tacorp(dot)net - jslagle(at)toledolink(dot)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(at)denninger(dot)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(at)tacorp(dot)net - jslagle(at)toledolink(dot)com - WHOIS JS10172
> > > >
> > > > On Wed, 10 Mar 1999, Tom Lane wrote:
> > > >
> > > > > "Brett W. McCoy" <bmccoy(at)lan2wan(dot)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
> > > > >
> > > >
> > > >
> > >
> >
>
From | Date | Subject | |
---|---|---|---|
Next Message | pierre | 1999-03-10 19:03:39 | HJ#####.# |
Previous Message | Karl Denninger | 1999-03-10 18:46:20 | Re: [SQL] Performance |