On Tue, Aug 01, 2006 at 09:34:19AM -0400, Matthew T. O'Connor wrote: > > 2. PostgreSQL 7.4 speed: > > > > dbmail-util -ay run for 11 hours(!) to fix the header caches etc. and > > IMAP was _very_ slow after the upgrade. Especially fetching message > > headers was 10 or 100 times slower than with 2.0.10. I found out that the > > problem is that the queries sent to PostgreSQL have conditions like > > physmessage_id IN (573485) > > in it, but PostgreSQL 7.4 can only use indexes if the condition is > > written as > > physmessage_id IN (753485::bigint) > > > > Meanwhile I upgraded to PostgreSQL 8.1.4 which does not have this > > problem with using indexes when types don't match exactly. IMAP is now at > > least as fast as with 2.0.10. > > Sounds like we should strongly recommend PostgreSQL 8.1 or later (Though I > think 8.0 can do the above mentioned optimization, but I'm not sure.) in > the release notes and possibly the DBMail documentation. I don't think > it's worth while to optimize DBMail for older releases of PostgreSQL.
Yes, 8.0 made that change (see E14.4.1 in http://lnk.nu/postgresql.org/apj.html), but performance has been improving dramatically in PostgreSQL. 7.4->8.0 usually results in at least 50%-100% better performance, while 8.0->8.1 nets at least 40% (though, these numbers are typically for larger installations). Something else worth mentioning is that the default PostgreSQL settings are very conservative; anyone who's concerned about performance should increase shared_buffers to at least 10% of memory. In later releases, people have seen large gains by going as high as 50%. -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net
