The following bug has been CLOSED ====================================================================== 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: closed ====================================================================== Date Submitted: 02-Feb-05 22:48 CET Last Modified: 19-May-05 14:24 CEST ====================================================================== 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! ---------------------------------------------------------------------- OutboundIndex - 06-Feb-05 20:26 CET ---------------------------------------------------------------------- You can close the bug again as resolved, I just wanted to add this query explain info for a system with close to 500K records of the difference between doing it by LIMIT or sequence. If the estimate is correct, LIMIT seems to be better and of course has the advantage of being compatible with both mysql and pgsql: SELECT message_idnr + 1 FROM dbmail_messages ORDER BY message_idnr DESC LIMIT 1; Limit (cost=0.00..0.06 rows=1 width=8) -> Index Scan Backward using dbmail_messages_pkey on dbmail_messages (cost=0.00..30644.91 rows=481316 width=8) VS SELECT last_value+1 FROM dbmail_message_idnr_seq; Seq Scan on dbmail_message_idnr_seq (cost=0.00..1.01 rows=1 width=8) ---------------------------------------------------------------------- aaron - 06-Feb-05 22:07 CET ---------------------------------------------------------------------- So this'll be good for now. When this gets revisited in 2.1 for compatibility with Oracle and MaxDB we'll come up with a more-better solution. I'm marking this as suspended, and will change the version to 2.1. 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 06-Feb-05 20:26OutboundIndex Bugnote Added: 0000582 06-Feb-05 20:26OutboundIndex Resolution fixed => reopened 06-Feb-05 20:26OutboundIndex Status resolved => feedback 06-Feb-05 22:07aaron Bugnote Added: 0000583 06-Feb-05 22:07aaron Resolution reopened => suspended 06-Feb-05 22:07aaron Status feedback => resolved 06-Feb-05 22:07aaron Resolution suspended => reopened 06-Feb-05 22:07aaron Status resolved => feedback 06-Feb-05 22:08aaron Resolution reopened => suspended 06-Feb-05 22:08aaron Status feedback => resolved 06-Feb-05 22:08aaron version 2.0.0 => CVS HEAD 19-May-05 14:24paul Status resolved => closed ======================================================================
