From: Tom Lane <tgl@sss.pgh.pa.us>
To: tim@dmcity.net
Cc: pgsql-sql@hub.org
Subject: Re: Performance Question
Date: Thu, 04 Feb 1999 10:27:54 -0500
Message-ID: <7741.918142074@sss.pgh.pa.us> (raw)
In-Reply-To: <199902040400.XAA27668@hub.org>
"Tim Perdue" <tim@directricity.com> writes:
> Am I going to run into huge performance problems on this? Should each
> mailing list be archived in its own table??
> When I do a
> SELECT * FROM tbl_mail_archive WHERE fld_mail_list=1 AND
> fld_mail_body~~'%keyword%';
> am I going to get killed with a performance hit??
Should be OK as long as you make an index on fld_mail_list (and don't
forget to vacuum regularly).
Of course, maintaining that index is not zero-cost. The appropriate
thing to ask is what your usage patterns will be. If you frequently
make searches across multiple mailing lists, then you undoubtedly
want to do it as you show above. If you never (or hardly ever) do that,
you might as well keep each mailing list in its own table and live with
having to do multiple SELECTs when you do want to look across multiple
lists.
> It's running OK now, with the table at 20MB, but I have 100MB more
> worth of letters to drop into the table..... 8-)
I suspect your real problem is going to be that searching 100MB with
"fld_mail_body~~'%keyword%'" is going to be dog-slow. I think you
are going to want a full-text index if you expect to do that a lot.
There is a simple all-Postgres FTI in the contrib part of the
distribution, but I think it'd probably run out of steam long before you
got to 100MB. What I'd probably do in your situation is to use
Glimpse (http://glimpse.cs.arizona.edu/) for the text index.
That'd likely mean storing the message bodies in separate files outside
the database proper, and keeping only the file names in the database
rows. (But that'd get rid of the message-over-8K problem, so it isn't
all bad...)
regards, tom lane
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: tgl@sss.pgh.pa.us, tim@dmcity.net, pgsql-sql@hub.org
Subject: Re: Performance Question
In-Reply-To: <7741.918142074@sss.pgh.pa.us>
* 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