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
--
http://www.tmueller.com for pgp key (95702B3B)
