From: Tim Perdue <perdue@raccoon.com>
To: Jason Slagle <raistlin@tacorp.net>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Performance
Date: Tue, 9 Mar 1999 21:33:57 -0600
Message-ID: <005b01be6aa6$d4cf66b0$0b8c5aa5@timnt.weather.net> (raw)
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.
>>
>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: perdue@raccoon.com, raistlin@tacorp.net, pgsql-sql@hub.org
Subject: Re: [SQL] Performance
In-Reply-To: <005b01be6aa6$d4cf66b0$0b8c5aa5@timnt.weather.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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