On Tue, 2005-03-15 at 13:49 +0100, Thomas Mueller wrote:
> I have 7.4.7 too. The results are MUCH better than what dbmail does at
> the moment so it's a real improvement. I don't know if it can be done
> better. The query on the mailbox with ~600 Mails (that's quite common I
> think) looses all its time here:
Agreed. Will include EXPLAIN ANALYZE when posting. I'm happy enough
looking at the plans :)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.00..726.83 rows=6245 width=36) (actual
time=75.749..535.647 rows=6246 loops=1)
Merge Cond: ("outer".id = "inner".physmessage_id)
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage b
(cost=0.00..216.71 rows=6246 width=24) (actual time=49.655..360.177
rows=6246 loops=1)
-> Index Scan using dbmail_messages_2 on dbmail_messages a
(cost=0.00..401.17 rows=6245 width=28) (actual time=13.445..51.841
rows=6246 loops=1)
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint) AND (mailbox_idnr = 1::bigint) AND (status <
2::smallint))
Total runtime: 558.631 ms
(6 rows)
[This is at 6K messages]
> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage b
> (cost=0.00..7631.57 rows=298853 width=24) (actual time=72.596..12891.334
> rows=104412 loops=1)
> I don't think we can speed that up. dbmail_physmessage_pkey is the best
> index to use.
As far as Pg is concerned- it is. The only thing that could speed it up
(without some highly incompatible magic) would be to store the critical
parts (mailbox_idnr, message_idnr) in dbmail_physmessage so Pg could do
better.
I _know_ you don't like queries like this, and [rant] I know it's
shitty, but it _does_ work for Pg.
After I'm done loading messages (see below) and examining the plans,
I'll dump my database, add some extra columns and indexes and
demonstrate.
> > If it is data related, see if you can give me a postgresql dump that
> > causes the problem (that is, if it IS your mailing lists, reload 'em
> > into a new db first) - you CAN send it directly to me (just remember to
> > remove the -dbmail from my email address)
>
> Thanks, but I don't think it's data related - it only depends on the
> number of mails in a mailbox. For every mail the database has to fetch
> the entry from the physmessage table.
Will verify; I'm going to load more messages in. You say 20k is enough?
[ this will take a while... ]
> I guess you get the same results if you take mailboxes with lot of mails.
> Most mails were copied into my database by the customers using their
> IMAP Client to import old mails. Thunderbird can be used to read mbox
> archives p.e. - I don't know if that's faster than dbmail-smtp ?
Probably not. I did a test a few weeks ago with imapsync (same thing)
and its APPEND operations took about the same amount of time.
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
