Message-ID: <00c601be4fe5$a0796d10$0b8c5aa5@timnt.weather.net>
From: "Tim Perdue" <tim@directricity.com>
To: <pgsql-sql@hub.org>
Subject: Performance Question
Date: Wed, 3 Feb 1999 20:25:25 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Please email directly to tim@dmcity.net.


Some of you may have followed the thread about my mailing archive system
that uses PostgreSQL.

The database is growing quickly and I want to make sure the performance is
going to be OK, so here's a question:

I have a table with records like this:

fld_mail_list (int) | etc | etc | fld_mail_body
      1                x x x     <----big TEXT Field ----->
      2                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      4                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      7                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      7                x x x     <----big TEXT Field ----->
      1                x x x     <----big TEXT Field ----->
      8                x x x     <----big TEXT Field ----->

Essentially, I have a several different mailings lists being archived into 1
big table, and the key to accessing them is using the fld_mail_list key.

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?? It's running OK now, with
the table at 20MB, but I have 100MB more worth of letters to drop into the
table.....  8-)

Please email directly to tim@dmcity.net.