Greetings everyone! I've been using DBMail for quite some time now and
recently I've found myself interested in tweaking performance in
PostgreSQL. I've noticed that there's one particular query that keeps
popping up that is notoriously slow and I believe I've found a tweak to
increase the performance significantly. Here's what I've seen:
Original query:
SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr BETWEEN '1611115' AND '1611115' AND
mailbox_idnr = '5' AND status IN ('0','1') ORDER BY message_idnr ASC;
PostgreSQL query plan and execution time:
Sort (cost=906.07..906.08 rows=1 width=36) (actual time=79.336..79.338
rows=1 loops=1)
Sort Key: msg.message_idnr
-> Nested Loop (cost=0.00..906.06 rows=1 width=36) (actual
time=0.809..79.325 rows=1 loops=1)
-> Index Scan using dbmail_messages_status_notdeleted_idx,
dbmail_messages_status_notdeleted_idx on dbmail_messages msg
(cost=0.00..900.04 rows=1 width=28) (actual time=0.721..79.230
rows=1 loops=1)
Index Cond: ((status = 0::smallint) OR (status =
1::smallint))
Filter: ((message_idnr >= 1611115::bigint) AND
(message_idnr <= 1611115::bigint) AND (mailbox_idnr =
5::bigint))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage pm (cost=0.00..6.01 rows=1 width=24) (actual
time=0.055..0.057 rows=1 loops=1)
Index Cond: (pm.id = "outer".physmessage_id)
Total runtime: 79.426 ms
Possible new query:
SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr BETWEEN '1611115' AND '1611115' AND
mailbox_idnr = '5' AND status < 2 ORDER BY message_idnr ASC;
New PostgreSQL query plan and execution time:
Nested Loop (cost=0.00..1216.39 rows=1 width=36) (actual
time=0.128..0.138 rows=1 loops=1)
-> Index Scan using messages_pkey on dbmail_messages msg
(cost=0.00..1210.37 rows=1 width=28) (actual time=0.025..0.028 rows=1
loops=1)
Index Cond: ((message_idnr >= 1611115::bigint) AND (message_idnr
<= 1611115::bigint))
Filter: ((mailbox_idnr = 5::bigint) AND (status < 2))
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage pm
(cost=0.00..6.01 rows=1 width=24) (actual time=0.056..0.060 rows=1 loops=1)
Index Cond: (pm.id = "outer".physmessage_id)
Total runtime: 0.203 ms
(7 rows)
Forgive my ignorance since this is the first time I've looked at the code
but I've simply replaced the "IN" clause with a "less than 2" clause. I
looked in my table and saw that the status flags in the dbmail-messages
table were all either 0, 1, 2, or 5. It looks like the status types in
dbmailtypes.h will probably be pretty static and are defined as follows:
MESSAGE_STATUS_NEW = 0
MESSAGE_STATUS_SEEN = 1
MESSAGE_STATUS_DELETE = 2
MESSAGE_STATUS_PURGE = 3
MESSAGE_STATUS_UNUSED = 4
MESSAGE_STATUS_INSERT = 5
MESSAGE_STATUS_ERROR = 6
So, my question is... is this the right place to suggest performance
tweaks to SQL statements? If not, where/to whom can I send this?
Thanks,
Tim Mattison
[EMAIL PROTECTED]
