Re: [SQL] Performance - Mailing list pgsql-sql
From | Tim Perdue |
---|---|
Subject | Re: [SQL] Performance |
Date | |
Msg-id | 005b01be6aa6$d4cf66b0$0b8c5aa5@timnt.weather.net Whole thread Raw |
List | pgsql-sql |
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. >> >>