Ilja, There have been various discussions in the past on the lists reguarding indexing issues... I don't remember all the issues, but a couple items that come to mind are:
1) It would be nice to provide multiple index sets - most of the ones that have been posted are good for imap, but almost entireley extra overhead if you just use pop3. We've improved our pop3 performance by removing most of them and perhaps optimizing a couple (or they may be identical, I don't remember). It'd be nice to list a set that are good for pop3-only sites, imap-only sites, and sites with both services. And of course we'll throw in a set of recommended indexes for running weDBmail. ;) 2) Please read the first paragraph of this message: http://twister.fastxs.net/pipermail/dbmail-dev/2003-June/000163.html That really ought to be done (ie. use only status flag to mark a message that is being inserted, and drop unique_id out of the indexes completely (well, except for the one index that is supposed to be for that field)). I was planning on working on it, but kind of ran out of time. Jn ---- Original Message ---- From: Paul J Stevens <[email protected]> To: [email protected] Subject: Re: [Dbmail] Wow 1.2 is nice but found a few things. Sent: Wed, 15 Oct 2003 15:25:59 +0200 > > > 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 > > _______________________________________________ > Dbmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > -- End Original Message -- -- Jesse Norell jesse (at) kci.net
