A NOTE has been added to this issue.
======================================================================
http://www.dbmail.org/mantis/view.php?id=709
======================================================================
Reported By: yavo
Assigned To:
======================================================================
Project: DBMail
Issue ID: 709
Category: Database layer
Reproducibility: always
Severity: tweak
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 14-Jun-08 09:45 CEST
Last Modified: 11-Sep-08 23:10 CEST
======================================================================
Summary: Some sql optimizations
Description:
The following queries can be rewritten to optimize them
db.c line 3009
snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr+1 FROM %smessages "
"WHERE mailbox_idnr=%llu "
"ORDER BY message_idnr DESC LIMIT 1",DBPFX, mb->uid);
to
snprintf(query, DEF_QUERYSIZE, "SELECT MAX(message_idnr)+1 FROM
%smessages "
"WHERE mailbox_idnr=%llu ", DBPFX, mb->uid);
db.c line 4021
snprintf(query, DEF_QUERYSIZE,
"SELECT message_idnr FROM %smessages "
"WHERE mailbox_idnr = %llu "
"AND status < %d AND seen_flag = 0 "
"ORDER BY message_idnr LIMIT 1",DBPFX,
mailbox_idnr, MESSAGE_STATUS_DELETE);
to
snprintf(query, DEF_QUERYSIZE,
"SELECT MIN(message_idnr) FROM %smessages "
"WHERE mailbox_idnr = %llu "
"AND status < %d AND seen_flag = 0 ",DBPFX,
mailbox_idnr, MESSAGE_STATUS_DELETE);
======================================================================
----------------------------------------------------------------------
paul - 14-Jun-08 11:18
----------------------------------------------------------------------
The reason for *not* using MAX or MIN is the poor performance of those on
postgresql. Has this changed?
Proposed performance tweaks without documentation and/or benchmarks (or at
the very least EXPLAIN analysis) are a bit suspect imo.
Feel free to discuss this on the list please.
----------------------------------------------------------------------
yavo - 14-Jun-08 11:46
----------------------------------------------------------------------
These are the explains from my production database Postgres 8
Am not sure how it would fare on older PG versions.
dbmail=> explain analyze
dbmail-> SELECT message_idnr FROM dbmail_messages WHERE mailbox_idnr = 10
dbmail-> AND status < 2
dbmail-> AND seen_flag = 0
dbmail->
dbmail-> ORDER BY message_idnr
dbmail->
dbmail-> LIMIT 1
dbmail-> ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..206.18 rows=1 width=8) (actual time=266.235..266.236
rows=1 loops=1)
-> Index Scan using dbmail_messages_pkey on dbmail_messages
(cost=0.00..315462.28 rows=1530 width=8) (actual time=266.231..266.231
rows=1 loops=1)
Filter: ((mailbox_idnr = 10) AND (status < 2) AND (seen_flag =
0))
Total runtime: 266.296 ms
(4 rows)
dbmail=>
dbmail=>
dbmail=>
dbmail=> explain analyze
dbmail-> SELECT min(message_idnr) FROM dbmail_messages WHERE mailbox_idnr
= 10
dbmail-> AND status < 2
dbmail-> AND seen_flag = 0 ;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5526.80..5526.80 rows=1 width=8) (actual
time=0.841..0.842 rows=1 loops=1)
-> Index Scan using dbmail_messages_7 on dbmail_messages
(cost=0.00..5522.97 rows=1530 width=8) (actual time=0.045..0.662 rows=191
loops=1)
Index Cond: ((mailbox_idnr = 10) AND (status < 2) AND (seen_flag
= 0))
Total runtime: 0.972 ms
(4 rows)
dbmail=>
----------------------------------------------------------------------
hinote - 11-Sep-08 23:10
----------------------------------------------------------------------
Agree completely with this proposal, the severety should be changed to
*major* imho instead of tweak - without this change our dbmail system
built on postgres 8.3 became completely unusable in just afew days of our
normal load. We have http://www.dbmail.org/mantis/view.php?id=93#300 mailboxes
storing spam (http://www.dbmail.org/mantis/view.php?id=#20000 messages in each
mailbox, http://www.dbmail.org/mantis/view.php?id=11#5M total), with "LIMIT 1"
it takes up to 15-20 min to open a
folder in an IMAP client. In addition, the dbmail-export utility that is
launched nightly to dump old messages out to /dev/null can run for days
opening each mailbox for half an hour due to this issue...
I found this issue and the same solution independently ;-( a couple of
days ago, and can confirm that this change is strictly needed for pgsql
systems with high load or storage, otherwise you are in risk to obtain
similar huge delays...
(I enrolled it as a change into dbmail 2.2.10 original code with
#if !defined(PGSQL_DAMAGED_BRAIN ... #endif
surrounding "LIMIT 1" in those two places in the code)
Issue History
Date Modified Username Field Change
======================================================================
14-Jun-08 09:45 yavo New Issue
14-Jun-08 11:18 paul Note Added: 0002568
14-Jun-08 11:46 yavo Note Added: 0002569
11-Sep-08 23:10 hinote Note Added: 0002601
======================================================================
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
