Please post the plan if we replace
status IN ('0', '1')
with
(status = '0' OR status = '1')
because that would allow us to keep our symbolic constants in the
source. I am also curious if this performs differently than IN because
it sure looks the exact same thing ;-)
Aaron
On Thu, 2006-10-05 at 09:27 -0400, [EMAIL PROTECTED] wrote:
> 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]
>
>
>
>
> _______________________________________________
> Dbmail-dev mailing list
> [email protected]
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
