In reply, some hints for postgresql...:
> Oct 23 11:42:28 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
> executing query [SELECT message_idnr, seen_flag, recent_flag
> FROM dbmail_messages WHERE mailbox_idnr = '9' AND status <
> '2' AND unique_id != '' ORDER BY message_idnr ASC] Oct 23
> 11:44:19 localhost dbmail/imap4d[2762]: dbmysql.c,db_query:
> executing query [SELECT MAX(message_idnr) FROM dbmail_
> messages WHERE unique_id != '']
First of all,... REPLACE all occurences of 'MAX(whatever)' with
'whatever ORDER BY whatever DESC LIMIT 1'
The performance boost here can be as much as thousand fold with an index
on 'whatever'.
The indexes for the second query also seem wrong, an index specifically
for this query would be required as follows:
CREATE INDEX messages_mbox_stat_uniq on
dbmail_messages(mailbox_idnr,status,length(unique_id));
So, the query [SELECT MAX(message_idnr) FROM dbmail_messages WHERE
unique_id != ''] becomes [SELECT message_idnr FROM dbmail_messages WHERE
unique_id != '' ORDER BY message_idnr DESC LIMIT 1]
And the second query changed to: [SELECT message_idnr, seen_flag,
recent_flag FROM dbmail_messages WHERE mailbox_idnr = '9' AND status <
'2' AND length(unique_id) > 0 ORDER BY message_idnr ASC]
Let's analyze the two original queries:
explain SELECT MAX(message_idnr) FROM dbmail_messages WHERE unique_id !=
'';
QUERY PLAN
------------------------------------------------------------------------
------------------
Aggregate (cost=100005160.78..100005160.78 rows=1 width=8)
-> Seq Scan on dbmail_messages (cost=100000000.00..100004708.49
rows=180918 width=8)
Filter: ((unique_id)::text <> ''::text)
(3 rows)
explain SELECT message_idnr, seen_flag, recent_flag FROM dbmail_messages
WHERE mailbox_idnr = '9' AND status < '2' AND unique_id != '' ORDER BY
message_idnr ASC;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------
Sort (cost=76.34..76.39 rows=20 width=12)
Sort Key: message_idnr
-> Index Scan using messages_mailbox_idx on dbmail_messages
(cost=0.00..75.91 rows=20 width=12)
Index Cond: (mailbox_idnr = 9::bigint)
Filter: ((status < 2::smallint) AND ((unique_id)::text <>
''::text))
(5 rows)
And the modified queries:
explain SELECT message_idnr FROM dbmail_messages WHERE unique_id != ''
ORDER BY message_idnr DESC LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------
Limit (cost=0.00..0.08 rows=1 width=8)
-> Index Scan Backward using messages_pkey on dbmail_messages
(cost=0.00..14395.42 rows=181071 width=8)
Filter: ((unique_id)::text <> ''::text)
(3 rows)
explain SELECT message_idnr, seen_flag, recent_flag FROM dbmail_messages
WHERE mailbox_idnr = '9' AND status < '2' AND length(unique_id) > 0
ORDER BY message_idnr ASC;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------
Sort (cost=29.00..29.02 rows=7 width=12)
Sort Key: message_idnr
-> Index Scan using messages_mbox_stat_uniq on dbmail_messages
(cost=0.00..28.90 rows=7 width=12)
Index Cond: ((mailbox_idnr = 9::bigint) AND (status <
2::smallint) AND (length((unique_id)::text) > 0))
(4 rows)
Now to the rest of the queries:
> executing query [SELECT mailbox_idnr FROM dbmail_mailboxes WHERE
mailbox_idnr = '9' AND owner_idnr = '3']
explain SELECT mailbox_idnr FROM dbmail_mailboxes WHERE mailbox_idnr =
'9' AND owner_idnr = '3';
QUERY PLAN
------------------------------------------------------------------------
---------------
Index Scan using mailboxes_pkey on dbmail_mailboxes (cost=0.00..3.50
rows=1 width=8)
Index Cond: (mailbox_idnr = 9::bigint)
Filter: (owner_idnr = 3::bigint)
(3 rows)
There isn't much we can do about this without dropping all constraints,
creating a unique index on mailbox_idnr and restoring the constraints.
The reason for this is unknown to me, but it seems postgresql prefers
primary key indexes to compound indexes, so we cannot optimize this.
> executing query [SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag, recent_flag FROM dbmail_messages WHERE
message_idnr = '1088' AND status < '2' AND unique_id != '' AND
mailbox_idnr = '9']
explain SELECT seen_flag, answered_flag, deleted_flag, flagged_flag,
draft_flag, recent_flag FROM dbmail_messages WHERE message_idnr = '1088'
AND status < '2' AND unique_id != '' AND mailbox_idnr = '9';
QUERY PLAN
------------------------------------------------------------------------
-------------------------------
Index Scan using messages_pkey on dbmail_messages (cost=0.00..9.62
rows=1 width=12)
Index Cond: (message_idnr = 1088::bigint)
Filter: ((status < 2::smallint) AND ((unique_id)::text <> ''::text)
AND (mailbox_idnr = 9::bigint))
(3 rows)
This query seems to be ok, as message_idnr is the primary key, and
specified in the query, it will return at most 1 row.
> executing query [SELECT pm.rfcsize FROM dbmail_physmessage pm,
dbmail_messages msg WHERE pm.id = msg.physmessage_id AND
msg.message_idnr = '1088' AND msg.status< '2' AND msg.unique_id != ''
AND msg.mailbox_idnr = '9']
explain SELECT pm.rfcsize FROM dbmail_physmessage pm, dbmail_messages
msg WHERE pm.id = msg.physmessage_id AND msg.message_idnr = '1088' AND
msg.status< '2' AND msg.unique_id != '' AND msg.mailbox_idnr = '9';
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------
Nested Loop (cost=0.00..15.12 rows=1 width=8)
-> Index Scan using messages_pkey on dbmail_messages msg
(cost=0.00..9.62 rows=1 width=8)
Index Cond: (message_idnr = 1088::bigint)
Filter: ((status < 2::smallint) AND ((unique_id)::text <>
''::text) AND (mailbox_idnr = 9::bigint))
-> Index Scan using physmessage_pkey on dbmail_physmessage pm
(cost=0.00..5.49 rows=1 width=16)
Index Cond: (pm.id = "outer".physmessage_id)
(6 rows)
As with the previous query, this seems to be ok, as message_idnr is the
primary key on dbmail_messages, and specified in the query, it will
return at most 1 row from that table.
> executing query [SELECT DATE_FORMAT(pm.internal_date,'%Y-%m-%d %T')
FROM dbmail_physmessage pm, dbmail_messages msg WHERE msg.mailbox_idnr =
'9' AND msg.message_idnr = '1088' AND msg.unique_id!='' AND pm.id =
msg.physmessage_id]
explain SELECT to_char(pm.internal_date,'%Y-%m-%d %T') FROM
dbmail_physmessage pm, dbmail_messages msg WHERE msg.mailbox_idnr = '9'
AND msg.message_idnr = '1088' AND msg.unique_id!='' AND pm.id =
msg.physmessage_id;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------
Nested Loop (cost=0.00..15.12 rows=1 width=8)
-> Index Scan using messages_pkey on dbmail_messages msg
(cost=0.00..9.61 rows=1 width=8)
Index Cond: (message_idnr = 1088::bigint)
Filter: ((mailbox_idnr = 9::bigint) AND ((unique_id)::text <>
''::text))
-> Index Scan using physmessage_pkey on dbmail_physmessage pm
(cost=0.00..5.49 rows=1 width=16)
Index Cond: (pm.id = "outer".physmessage_id)
(6 rows)
As with the previous query, this seems to be ok, as message_idnr is the
primary key on dbmail_messages, and specified in the query, it will
return at most 1 row from that table.
> executing query [SELECT messageblk FROM dbmail_messageblks blk,
dbmail_messages msg WHERE blk.physmessage_id = msg.physmessage_id AND
msg.message_idnr = '1088' ORDER BY blk.messageblk_idnr ASC]
explain SELECT messageblk FROM dbmail_messageblks blk, dbmail_messages
msg WHERE blk.physmessage_id = msg.physmessage_id AND msg.message_idnr =
'1088' ORDER BY blk.messageblk_idnr ASC;
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------
Sort (cost=26.16..26.17 rows=4 width=1009)
Sort Key: blk.messageblk_idnr
-> Nested Loop (cost=0.00..26.12 rows=4 width=1009)
-> Index Scan using messages_pkey on dbmail_messages msg
(cost=0.00..9.60 rows=2 width=8)
Index Cond: (message_idnr = 1088::bigint)
-> Index Scan using messageblks_physmessage_idx on
dbmail_messageblks blk (cost=0.00..8.23 rows=2 width=1017)
Index Cond: (blk.physmessage_id = "outer".physmessage_id)
(7 rows)
This query seems ok as well,.. As no filtering is done at all. Only
index filters.
Kind regards,
John
