The dbmail_messages_physmessage_idx isn't the best index to use.
You have several options:
put extra columns in dbmail_physmessage that reference the message_idnr
and mailbox_idnr (and index them WITH the dbmail_physmessage.id) -- THEN
adapt the query to look for these fields (e.g. AND pm.message_idnr
BETWEEN 1 AND 417966 AND msg.message_idnr BETWEEN 1 AND 417966)
This is ugly. But it's the fastest for Pg.
Another option (probably would help other dbs too) is to use two
subselects - one on dbmail_messages and one on dbmail_physmessage. This
way, the best index for dbmail_messages can be used-
CREATE INDEX dbmail_messages_wide ON dbmail_messages
(mailbox_idnr,message_idnr,status);
CREATE INDEX dbmail_messages_wider ON dbmail_messages
(mailbox_idnr,message_idnr,status,physmessage_id);
I'd have to experiment to find out which one would generate the better
query plan -- only one of those two indexes should actually be used.
The query then should look like this:
SELECT
a.seen_flag,
a.answered_flag,
a.deleted_flag,
a.flagged_flag,
a.draft_flag,
a.recent_flag,
b.internaldate,
b.rfcsize,
a.message_idnr
FROM (
SELECT seen_flag,answered_flag,deleted_flag,
flagged_flag,draft_flag,recent_flag,
message_idnr,physmessage_id AS id
FROM dbmail_messages
WHERE message_idnr BETWEEN '1' AND '417966'
AND mailbox_idnr = '81'
AND (status = '0' OR status = '1')
ORDER BY message_idnr ASC
) AS a JOIN (
SELECT id,TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS') AS
internaldate, rfcsize
FROM dbmail_physmessage WHERE id IN (
SELECT physmessage_id FROM dbmail_messages
WHERE message_idnr BETWEEN '1' AND '417966'
AND mailbox_idnr = '81'
AND (status = '0' OR status = '1')
)
) AS b USING (id);
{whew!}
On Thu, 2005-03-10 at 10:42 +0100, Thomas Mueller wrote:
> Hi,
>
> now I have only one slow query left - unfortunately I have no idea why
> it is that slow. The mailbox contains ~1800 mails. It takes 45 seconds
> to open it:
>
> 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 '1' AND '417966' AND
> mailbox_idnr = '81' AND status < '2' ORDER BY message_idnr ASC
>
> Sort (cost=10809.69..10816.57 rows=2754 width=36) (actual
> time=44701.720..44709.281 rows=1827 loops=1)
> Sort Key: msg.message_idnr
> -> Merge Join (cost=0.00..10652.33 rows=2754 width=36) (actual
> time=205.203..44688.345 rows=1827 loops=1)
> Merge Cond: ("outer".physmessage_id = "inner".id)
> -> Index Scan using dbmail_messages_physmessage_idx on
> dbmail_messages msg (cost=0.00..24818.12 rows=2753 width=28) (actual
> time=161.471..42272.510 rows=1827 loops=1)
> Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
> 417966::bigint) AND (mailbox_idnr = 81::bigint) AND (status < 2::smallint))
> -> Index Scan using dbmail_physmessage_pkey on
> dbmail_physmessage pm (cost=0.00..3842.59 rows=73428 width=24) (actual
> time=0.050..1922.954 rows=103804 loops=1)
> Total runtime: 44716.105 ms
>
>
> I have really no idea why the index scan takes 42 seconds.
>
> I removed the BETWEEN (is that really required?) but there's no speed up.
> I'm on IRC, FreeNode network, channel #dbmail. I'd be happy to test
> queries if anyone has an idea.
>
>
> Thomas
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
