Received: from slip1.raccoon.com (slip1.raccoon.com [165.90.135.16]) by hub.org (8.9.2/8.9.1) with ESMTP id WAA38471 for ; Tue, 9 Mar 1999 22:35:37 -0500 (EST) (envelope-from perdue@raccoon.com) Received: from timnt (tim.desmoinesnet.com [165.90.140.11]) by slip1.raccoon.com (8.8.7/8.8.7) with SMTP id VAA10911; Tue, 9 Mar 1999 21:34:06 -0600 Message-ID: <005b01be6aa6$d4cf66b0$0b8c5aa5@timnt.weather.net> Reply-To: "Tim Perdue" From: "Tim Perdue" To: "Jason Slagle" , Subject: Re: [SQL] Performance Date: Tue, 9 Mar 1999 21:33:57 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 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 To: Tim Perdue Cc: D'Arcy J.M. Cain ; 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 >> To: perdue@raccoon.com >> Cc: 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 | 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. >> >>