Hi Kevin,
>>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.
85.000
> 2. Try doing a REINDEX TABLE dbmail_messages.
The REINDEX was much faster than the query - and there are lot of
indizes on that table :-/
That didn't help.
> 3. Are you running autovacuum? If not, how often do you vacuum the
> tables in this database?
Yes I am running autovacuum.
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
