pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance
19+ messages / 10 participants
[nested] [flat]
* Performance
@ 1999-03-10 01:33 Tim Perdue <perdue@raccoon.com>
1999-03-10 02:29 ` Re: [SQL] Performance Clark Evans <clark.evans@manhattanproject.com>
1999-03-10 02:52 ` Re: [SQL] Performance Brett W. McCoy <bmccoy@lan2wan.com>
1999-03-10 03:11 ` Re: [SQL] Performance D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 3 replies; 19+ messages in thread
From: Tim Perdue @ 1999-03-10 01:33 UTC (permalink / raw)
To: pgsql-sql@hub.org
Some time back I sent out a message asking if PGSQL would be able to handle
my mailing list archive at http://www.geocrawler.com/ , and whether it would
scale up to 100MB.
BELIEVE IT OR NOT, PostgreSQL is handling 1.8GB in just one table, and its
performance is amazing. I have archived 1,100,000 emails in the database and
I don't really see much slowdown yet.
I'm just stunned at how well this is working, and I keep pushing myself to
find more and more mail archives to add to my database.
It's fun! Check out the linux/freebsd/etc mail archives at geocrawler.com
Tim
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 01:33 Performance Tim Perdue <perdue@raccoon.com>
@ 1999-03-10 02:29 ` Clark Evans <clark.evans@manhattanproject.com>
1999-03-10 02:50 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
2 siblings, 1 reply; 19+ messages in thread
From: Clark Evans @ 1999-03-10 02:29 UTC (permalink / raw)
To: Tim Perdue <perdue@raccoon.com>; +Cc: pgsql-sql@hub.org
Tim Perdue wrote:
> BELIEVE IT OR NOT, PostgreSQL is handling 1.8GB in just one table, and its
> performance is amazing. I have archived 1,100,000 emails in the database and
> I don't really see much slowdown yet.
What is your hardware, os, etc?
Thanks!
Clark Evans
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 01:33 Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 02:29 ` Re: [SQL] Performance Clark Evans <clark.evans@manhattanproject.com>
@ 1999-03-10 02:50 ` Karl Denninger <karl@Denninger.Net>
0 siblings, 0 replies; 19+ messages in thread
From: Karl Denninger @ 1999-03-10 02:50 UTC (permalink / raw)
To: Clark Evans <clark.evans@manhattanproject.com>; Tim Perdue <perdue@raccoon.com>; +Cc: pgsql-sql@hub.org
On Wed, Mar 10, 1999 at 02:29:45AM +0000, Clark Evans wrote:
> Tim Perdue wrote:
> > BELIEVE IT OR NOT, PostgreSQL is handling 1.8GB in just one table, and its
> > performance is amazing. I have archived 1,100,000 emails in the database and
> > I don't really see much slowdown yet.
>
> What is your hardware, os, etc?
>
> Thanks!
>
> Clark Evans
I have had tables exceeding 10GB in one dataset, and the performance wa
STILL excellent. Hardware was a Pentium Pro 200, FreeBSD, and a CMD RAID
controller.
--
--
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 01:33 Performance Tim Perdue <perdue@raccoon.com>
@ 1999-03-10 02:52 ` Brett W. McCoy <bmccoy@lan2wan.com>
2 siblings, 0 replies; 19+ messages in thread
From: Brett W. McCoy @ 1999-03-10 02:52 UTC (permalink / raw)
To: Tim Perdue <perdue@raccoon.com>; +Cc: pgsql-sql@hub.org
On Tue, 9 Mar 1999, Tim Perdue wrote:
> Some time back I sent out a message asking if PGSQL would be able to handle
> my mailing list archive at http://www.geocrawler.com/ , and whether it would
> scale up to 100MB.
>
> BELIEVE IT OR NOT, PostgreSQL is handling 1.8GB in just one table, and its
> performance is amazing. I have archived 1,100,000 emails in the database and
> I don't really see much slowdown yet.
>
> I'm just stunned at how well this is working, and I keep pushing myself to
> find more and more mail archives to add to my database.
>
> It's fun! Check out the linux/freebsd/etc mail archives at geocrawler.com
Yeah, I've got a database I'm still putting together, several tables of
which have over 2 million rows. It's returning queries in under 30
seconds or so, in most cases. You just gotta make sure you have you
indices set up properly.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy/
-----------------------------------------------------------------------
Losing your drivers' license is just God's way of saying "BOOGA, BOOGA!"
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++
------END GEEK CODE BLOCK------
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 01:33 Performance Tim Perdue <perdue@raccoon.com>
@ 1999-03-10 03:11 ` D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 19+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-03-10 03:11 UTC (permalink / raw)
To: perdue@raccoon.com; +Cc: pgsql-sql@hub.org
Thus spake Tim Perdue
> Some time back I sent out a message asking if PGSQL would be able to handle
> my mailing list archive at http://www.geocrawler.com/ , and whether it would
> scale up to 100MB.
Cool. We now have a searchable archive for PostgreSQL. However, I
tried a search and it didn't seem to work. In pgsql-hackers I entered
"niladic" (I tried "Niladic" just in case) and it failed to find any
messages, even though I could see a few on the list of recent messages.
Any ideas?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
@ 1999-03-10 03:13 Tim Perdue <perdue@raccoon.com>
1999-03-10 03:24 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
0 siblings, 1 reply; 19+ messages in thread
From: Tim Perdue @ 1999-03-10 03:13 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql@hub.org
It searches the bodies, not the subjects or authors. I don't see why anyone
needs to search for a particular author. But if it's requested enough, it
would be trivial to implement.....
Tim
-----Original Message-----
From: D'Arcy J.M. Cain <darcy@druid.net>
To: perdue@raccoon.com <perdue@raccoon.com>
Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
Date: Tuesday, March 09, 1999 9:10 PM
Subject: Re: [SQL] Performance
>Thus spake Tim Perdue
>> Some time back I sent out a message asking if PGSQL would be able to
handle
>> my mailing list archive at http://www.geocrawler.com/ , and whether it
would
>> scale up to 100MB.
>
>Cool. We now have a searchable archive for PostgreSQL. However, I
>tried a search and it didn't seem to work. In pgsql-hackers I entered
>"niladic" (I tried "Niladic" just in case) and it failed to find any
>messages, even though I could see a few on the list of recent messages.
>Any ideas?
>
>--
>D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
>http://www.druid.net/darcy/ | and a sheep voting on
>+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 03:13 Re: [SQL] Performance Tim Perdue <perdue@raccoon.com>
@ 1999-03-10 03:24 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 03:30 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
1999-03-10 03:44 ` Re: [SQL] Performance Brett W. McCoy <bmccoy@lan2wan.com>
0 siblings, 2 replies; 19+ messages in thread
From: Jason Slagle @ 1999-03-10 03:24 UTC (permalink / raw)
To: Tim Perdue <perdue@raccoon.com>; +Cc: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql@hub.org
What, if anything, are you using as an index.
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?
How CPU intensive is a select?
Jason
---
Jason Slagle
Network Administrator - Toledo Internet Access - Toledo Ohio
- raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172
On Tue, 9 Mar 1999, Tim Perdue wrote:
> It searches the bodies, not the subjects or authors. I don't see why anyone
> needs to search for a particular author. But if it's requested enough, it
> would be trivial to implement.....
>
> Tim
>
>
> -----Original Message-----
> From: D'Arcy J.M. Cain <darcy@druid.net>
> To: perdue@raccoon.com <perdue@raccoon.com>
> Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
> Date: Tuesday, March 09, 1999 9:10 PM
> Subject: Re: [SQL] Performance
>
>
> >Thus spake Tim Perdue
> >> Some time back I sent out a message asking if PGSQL would be able to
> handle
> >> my mailing list archive at http://www.geocrawler.com/ , and whether it
> would
> >> scale up to 100MB.
> >
> >Cool. We now have a searchable archive for PostgreSQL. However, I
> >tried a search and it didn't seem to work. In pgsql-hackers I entered
> >"niladic" (I tried "Niladic" just in case) and it failed to find any
> >messages, even though I could see a few on the list of recent messages.
> >Any ideas?
> >
> >--
> >D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
> >http://www.druid.net/darcy/ | and a sheep voting on
> >+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 03:13 Re: [SQL] Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 03:24 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
@ 1999-03-10 03:30 ` Karl Denninger <karl@Denninger.Net>
1 sibling, 0 replies; 19+ messages in thread
From: Karl Denninger @ 1999-03-10 03:30 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; Tim Perdue <perdue@raccoon.com>; +Cc: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql@hub.org
Build appropriate indices for the fields you intend to discriminate on
during a SELECT.
200MB is SMALL. I used to run searches over 10G of data in under 2 minutes
looking for RADIUS detail records. The trick is to build the right set of
indices for what you intend to be querying on.
--
--
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.
> What, if anything, are you using as an index.
>
> 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?
>
> How CPU intensive is a select?
>
> Jason
>
> ---
> Jason Slagle
> Network Administrator - Toledo Internet Access - Toledo Ohio
> - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172
>
> On Tue, 9 Mar 1999, Tim Perdue wrote:
>
> > It searches the bodies, not the subjects or authors. I don't see why anyone
> > needs to search for a particular author. But if it's requested enough, it
> > would be trivial to implement.....
> >
> > Tim
> >
> >
> > -----Original Message-----
> > From: D'Arcy J.M. Cain <darcy@druid.net>
> > To: perdue@raccoon.com <perdue@raccoon.com>
> > Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
> > Date: Tuesday, March 09, 1999 9:10 PM
> > Subject: Re: [SQL] Performance
> >
> >
> > >Thus spake Tim Perdue
> > >> Some time back I sent out a message asking if PGSQL would be able to
> > handle
> > >> my mailing list archive at http://www.geocrawler.com/ , and whether it
> > would
> > >> scale up to 100MB.
> > >
> > >Cool. We now have a searchable archive for PostgreSQL. However, I
> > >tried a search and it didn't seem to work. In pgsql-hackers I entered
> > >"niladic" (I tried "Niladic" just in case) and it failed to find any
> > >messages, even though I could see a few on the list of recent messages.
> > >Any ideas?
> > >
> > >--
> > >D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
> > >http://www.druid.net/darcy/ | and a sheep voting on
> > >+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> >
> >
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 03:13 Re: [SQL] Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 03:24 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
@ 1999-03-10 03:44 ` Brett W. McCoy <bmccoy@lan2wan.com>
1 sibling, 0 replies; 19+ messages in thread
From: Brett W. McCoy @ 1999-03-10 03:44 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; +Cc: Tim Perdue <perdue@raccoon.com>; D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql@hub.org
On Tue, 9 Mar 1999, Jason Slagle wrote:
> What, if anything, are you using as an index.
>
> 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.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy/
-----------------------------------------------------------------------
That's what she said.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++
------END GEEK CODE BLOCK------
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
@ 1999-03-10 03:33 Tim Perdue <perdue@raccoon.com>
0 siblings, 0 replies; 19+ messages in thread
From: Tim Perdue @ 1999-03-10 03:33 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; pgsql-sql@hub.org
I'm doing "combination" (my term) indexes on all criteria.
So if I have a...
SELECT * FROM tbl_mail_archive WHERE fld_mail_list=1 AND fld_mail_year=1999
AND fld_mail_month=2;
Then I...
CREATE INDEX idx_mail_list_year_month ON tbl_mail_archive (fld_mail_list,
fld_mail_year, fld_mail_month);
And it works very very well.
As far as searching the mail bodies, that is not indexed right now.
I do a...
SELECT * FROM tbl_mail_archive WHERE fld_mail_list=1 AND fld_mail_year=1999
AND lower(fld_mail_body)~~'%keyword1%' AND
lower(fld_mail_body)~~'%keyword2%';
It works well. I'm totally amazed so far.
Tim
-----Original Message-----
From: Jason Slagle <raistlin@tacorp.net>
To: Tim Perdue <perdue@raccoon.com>
Cc: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql@hub.org
<pgsql-sql@hub.org>
Date: Tuesday, March 09, 1999 9:22 PM
Subject: Re: [SQL] Performance
>What, if anything, are you using as an index.
>
>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?
>
>How CPU intensive is a select?
>
>Jason
>
>---
>Jason Slagle
>Network Administrator - Toledo Internet Access - Toledo Ohio
>- raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172
>
>On Tue, 9 Mar 1999, Tim Perdue wrote:
>
>> It searches the bodies, not the subjects or authors. I don't see why
anyone
>> needs to search for a particular author. But if it's requested enough, it
>> would be trivial to implement.....
>>
>> Tim
>>
>>
>> -----Original Message-----
>> From: D'Arcy J.M. Cain <darcy@druid.net>
>> To: perdue@raccoon.com <perdue@raccoon.com>
>> Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
>> Date: Tuesday, March 09, 1999 9:10 PM
>> Subject: Re: [SQL] Performance
>>
>>
>> >Thus spake Tim Perdue
>> >> Some time back I sent out a message asking if PGSQL would be able to
>> handle
>> >> my mailing list archive at http://www.geocrawler.com/ , and whether it
>> would
>> >> scale up to 100MB.
>> >
>> >Cool. We now have a searchable archive for PostgreSQL. However, I
>> >tried a search and it didn't seem to work. In pgsql-hackers I entered
>> >"niladic" (I tried "Niladic" just in case) and it failed to find any
>> >messages, even though I could see a few on the list of recent messages.
>> >Any ideas?
>> >
>> >--
>> >D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
>> >http://www.druid.net/darcy/ | and a sheep voting on
>> >+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
>>
>>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
@ 1999-03-10 15:37 Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 1999-03-10 15:37 UTC (permalink / raw)
To: pgsql-sql@hub.org
"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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-03-10 18:27 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
1999-03-10 18:44 ` Re: [SQL] Performance G. Anthony Reina <reina@nsi.edu>
0 siblings, 2 replies; 19+ messages in thread
From: Jason Slagle @ 1999-03-10 18:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org
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
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
@ 1999-03-10 18:35 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 18:39 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1 sibling, 1 reply; 19+ messages in thread
From: Karl Denninger @ 1999-03-10 18:35 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org
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
> >
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
@ 1999-03-10 18:39 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:46 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
0 siblings, 1 reply; 19+ messages in thread
From: Jason Slagle @ 1999-03-10 18:39 UTC (permalink / raw)
To: Karl Denninger <karl@Denninger.Net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@hub.org
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
> > >
> >
> >
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
1999-03-10 18:39 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
@ 1999-03-10 18:46 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 18:51 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 19:11 ` Re: [SQL] Performance Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Karl Denninger @ 1999-03-10 18:46 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@hub.org
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@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: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@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
> > > >
> > >
> > >
> >
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
1999-03-10 18:39 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:46 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
@ 1999-03-10 18:51 ` Jason Slagle <raistlin@tacorp.net>
1 sibling, 0 replies; 19+ messages in thread
From: Jason Slagle @ 1999-03-10 18:51 UTC (permalink / raw)
To: Karl Denninger <karl@Denninger.Net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@hub.org
And there off :D Reloading it. We'll try again :blah:
Thanks,
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:
> 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@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: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@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
> > > > >
> > > >
> > > >
> > >
> >
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
1999-03-10 18:39 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:46 ` Re: [SQL] Performance Karl Denninger <karl@Denninger.Net>
@ 1999-03-10 19:11 ` Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 0 replies; 19+ messages in thread
From: Bruce Momjian @ 1999-03-10 19:11 UTC (permalink / raw)
To: karl@Denninger.Net; +Cc: raistlin@tacorp.net; tgl@sss.pgh.pa.us; pgsql-sql@hub.org
> 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 :-)
Ah, but VACUUM ANALYZE does statistics, which is needed. I should break
out ANALYZE as a separate command.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Re: [SQL] Performance Jason Slagle <raistlin@tacorp.net>
@ 1999-03-10 18:44 ` G. Anthony Reina <reina@nsi.edu>
1 sibling, 0 replies; 19+ messages in thread
From: G. Anthony Reina @ 1999-03-10 18:44 UTC (permalink / raw)
To: Jason Slagle <raistlin@tacorp.net>; +Cc: pgsql-sql@hub.org
Jason Slagle wrote:
> And how long is normal for Vacuum analyze to take on bout 350 megs of
> DATA?
>
> Jason
>
My database is 1.4 Gig. It takes about 2-3 minutes for a vacuum analyze verbose.
I have had problems in the past with a corrupted main index (the table of
tables). In such a case, the vacuum never stops even after > 48 hours. Try
vacuuming your tables one-by-one if you have really long vaccums (> 1 hour). If
the tables vacuum one-by-one alright, then you have a corrupted main index.
-Tony
^ permalink raw reply [nested|flat] 19+ messages in thread
* performance
@ 2003-02-26 03:09 James Cooper <jim@luckydigital.com>
0 siblings, 0 replies; 19+ messages in thread
From: James Cooper @ 2003-02-26 03:09 UTC (permalink / raw)
To: pgsql-sql
Im toying with performance issues in my queries at the moment see below:
select count(person_id) as tot from person where person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 2 )
and person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 3 )
-->Seq Scan on person (cost=0.00..446281.56 rows=1146 width=4)
select person_id as tot from person where person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 2 )
INTERSECT
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 3 )
-->SetOp Intersect (cost=223522.57..223537.75 rows=304 width=16)
two questions,
1- is there a performance difference between "intersect" and "AND person_id IN
2 -when I use INTERSECT if I try and use count() like in the first example I get nothing back, why is this?
James
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2003-02-26 03:09 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-10 01:33 Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 02:29 ` Clark Evans <clark.evans@manhattanproject.com>
1999-03-10 02:50 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 02:52 ` Brett W. McCoy <bmccoy@lan2wan.com>
1999-03-10 03:11 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-03-10 03:13 Re: [SQL] Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 03:24 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 03:30 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 03:44 ` Brett W. McCoy <bmccoy@lan2wan.com>
1999-03-10 03:33 Re: [SQL] Performance Tim Perdue <perdue@raccoon.com>
1999-03-10 15:37 Re: [SQL] Performance Tom Lane <tgl@sss.pgh.pa.us>
1999-03-10 18:27 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:35 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 18:39 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 18:46 ` Karl Denninger <karl@Denninger.Net>
1999-03-10 18:51 ` Jason Slagle <raistlin@tacorp.net>
1999-03-10 19:11 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-03-10 18:44 ` G. Anthony Reina <reina@nsi.edu>
2003-02-26 03:09 performance James Cooper <jim@luckydigital.com>
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