A BUGNOTE has been added to this bug. ====================================================================== http://www.dbmail.org/mantis/bug_view_advanced_page.php?bug_id=0000171 ====================================================================== Reported By: OutboundIndex Assigned To: ====================================================================== Project: DBMail Bug ID: 171 Category: Database layer Reproducibility: always Severity: major Priority: normal Status: new ====================================================================== Date Submitted: 02-Feb-05 22:48 CET Last Modified: 06-Feb-05 19:06 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. 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 ======================================================================
