Ilja Booij wrote:
The tables are fixed in CVS. For people using the DBMail 1.2 release: I've
put Paul's SQL-script on http://www.dbmail.org so you can download it, and
run it against your PostgreSQL DBMail database.
This update script can also be found in the sql/postgresql/ directory in
the sources in CVS.
I guess we need the same alterations for 2.0? Paul?
Caveat: I'm no sql guru.
Rule of thumb: all fields used in where clauses should be indexed.
mysql has a nice sql command called 'explain select ...' which analyzes
usage of indexes for select queries. I don't know of a similar tool in
postgresql.
What I did back in april was check for select queries logged in syslog,
look for where clauses, and cut-n-paste those to a mysql command prompt
preceeded by 'explain'.
Next I added indexes for all necessary fields until the report generated
by explain told me the tables where optimized for that query. After
adding several indexes in this manner, I simply ported those to
postgresql. Performance was much improved to say the least.
I have not studied the code to look for all possible select queries, so
I may well have missed an indexable field or two.
AFAIK, the rule-of-thumb still applies for 2.0. I see hardly any indexes
other than primary keys in the default tables for 2.0/postgresql, so I
guess adding them is required for any but the most trivial dbmail
installations.
I was somewhat surprised by this lack of optimal table structure in the
default installation. I would expect IC&S to have seriously optimized
their tables since they claim to run installations with 10th of
thousands of mailboxes.
Still, I'm happy to be able to help out a bit.
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
