A NOTE has been added to this issue.
======================================================================
http://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: 14-Jun-08 11:46 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=>
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
======================================================================
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
