The following bug has been RESOLVED. ====================================================================== http://www.dbmail.org/mantis/bug_view_advanced_page.php?bug_id=0000171 ====================================================================== Reported By: OutboundIndex Assigned To: aaron ====================================================================== Project: DBMail Bug ID: 171 Category: Database layer Reproducibility: always Severity: major Priority: normal Status: resolved Resolution: fixed ====================================================================== Date Submitted: 02-Feb-05 22:48 CET Last Modified: 06-Feb-05 19:55 CET ====================================================================== Summary: max() is indexable in mysql but not in postgresql causing extreme slowness every folder open Description: An often used sql query is fast on mysql (indexed) but unbearably horribly slow on postgresql once dbmail is full of a lot of messages (400,000 and maybe a lot less.) Particularly easy to feel when using squirrelmail. Postgresql cannot and does not use indexes on a min() or max() function. Blazingly fast performance in postgresql is restored by using the current value of the next sequence instead of max()+1 in the query. ======================================================================
---------------------------------------------------------------------- aaron - 06-Feb-05 19:06 CET ---------------------------------------------------------------------- There was a suggestion of using LIMIT, which is supported by both MySQL and PostgreSQL. That should work just fine in 2.0 since we only support MySQL and PostgreSQL anyways. During 2.1, when more database shims are added, we'll have to complexify the solution a little bit. Cross that bridge when we get to it. ---------------------------------------------------------------------- aaron - 06-Feb-05 19:46 CET ---------------------------------------------------------------------- Ok, John Hansen's suggestion is now in CVS: SELECT message_idnr + 1 FROM dbmail_messages ORDER BY message_idnr DESC LIMIT 1 Please double check this on PostgreSQL! ---------------------------------------------------------------------- aaron - 06-Feb-05 19:55 CET ---------------------------------------------------------------------- Now in CVS. I sent an email to Petru to confirm this for him, since he found the bug in the first place. Please reopen if the fix is unsatisfactory! Bug History Date Modified Username Field Change ====================================================================== 02-Feb-05 22:48OutboundIndex New Bug 02-Feb-05 22:48OutboundIndex File Added: pg-speed.diff 06-Feb-05 19:06aaron Bugnote Added: 0000579 06-Feb-05 19:46aaron Bugnote Added: 0000580 06-Feb-05 19:55aaron Bugnote Added: 0000581 06-Feb-05 19:55aaron Assigned To => aaron 06-Feb-05 19:55aaron Resolution open => fixed 06-Feb-05 19:55aaron Status new => resolved ======================================================================
