Re: advice on indexing email

Regarding your first suggestion, I cannot use a trigger since the actual
email contents will not be inserted in the database because of the 8K
limit on text fields. Instead, I will be keeping the messages as a
seperate file and only keep the pathname in the database.

As for your second suggestion, which of the following methods should I use
to actually enter the words into the database:
- INSERT for each word, which will probably be slow considering an email
message will most likely contain >50 words.
- BEGIN before and END after inserting every word, not sure if that makes
much difference considering there will still be the overhead of single
inserts.
- creating a temporary file and using COPY, which also has it's own share
of overhead considering the additional i/o of creating a file.

PS. Thanks for the fti crontrib, much appreciated. I have already used
your example to create a few other indexing alternatives of my own. I have
also created a daily maintenance script to cluster and rebuild the indices
of my fti tables. Let me know if that could be of interest to you, perhaps
it could be added to your contrib directory.

> > My problem is how to create the full word index. The actual code to
> > seperate the email into seperate words isn't a problem, but should I be
> > using INSERT, BEGIN/END or COPY? In this last case, I would have to create
> > a temporary file holding each word of the email and then use COPY... all
> > of which also has it's fair share of overhead.
>
> You can use one of 2 ways.
>
> 1. the fti stuff in contrib uses triggers, so every time you
> insert/update/delete something in/from the 'fti-ed' table, the full text index
> is also updated. If you're coding abilities are OK, you can just replace the
> word breakup code in contrib/fti with your own one.
>
> 2. if you have to insert large amounts of data, it is probably faster to *not*
> create the triggers at first, bulk load all your data, write a little perl
> script that reads the data from your table, does the word breakup and inserts
> those words into the full text index table. Using a 'sort' on the output of
> the perl script will help performance as the fti data will now already be
> pre-sorted in the database (you could also use CLUSTER on the fti table after
> the index has been created). I think I described this somewhat better in the
> README in contrib/fti. If you take this approach, don't forget to create the
> triggers after the bulk load of the fti table!