Thomas Mueller wrote:
> 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)
The above index scan seems to be taking much/most of the time in both
this query and the other one you posted today. So:
1. How many messages are in dbmail_messages in total? I'd like to
get a sense for how selective this query is. That said, the
actual result is fairly close (within a binary order of magnitude)
to the planner's estimate, so it means the statistics are fairly
good here.
2. Try doing a REINDEX TABLE dbmail_messages.
3. Are you running autovacuum? If not, how often do you vacuum the
tables in this database?
--
Kevin Brown [EMAIL PROTECTED]
