Thread: Re: [SQL] Performance
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.
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. > >
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. > > > > > >
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------