Michael, this has already been fixed in 2.3:
if a mailbox has no child-mailboxes: delete from dbmail_mailboxes where mailbox_idnr = ?; if a mailbox does have child-mailboxes: BEGIN; UPDATE dbmail_messages SET status=PURGE WHERE mailbox_idnr = ?; UPDATE dbmail_mailboxes SET no_select = 1 WHERE mailbox_idnr = ?; COMMIT Michael Monnerie wrote: > I tested the deletion of a folder with subfolders with lots of messages. > It's horribly slow, using 100% CPU of the "postmaster" process from > Postgres for a long time, doing things like those logged below (that's > only a very small excerpt from all log lines). > So dbmail seems to still delete each message on it's own, that > should all be replaced by > DELETE FROM dbmail_mailboxes WHERE owner_idnr=XXX AND name=YYY; > The rest is done by the database, many times faster than actually. > I guess there might be the need to update the curmail_size, so before that > with a SELECT find that mailbox and all submailboxes and all > their message sizes. I guess that would be: > SELECT sum(messagesize) FROM dbmail_physmessage p JOIN dbmail_messages m ON > (p.id=m.physmessage_id) JOIN dbmail_mailboxes USING (mailbox_idnr) WHERE > owner_idnr=XXX AND name LIKE 'YYY%'; > > If that's right: should I submit it as a patch? > > 2009-02-22 16:11:49 CET pid=21291 19/4155 idle: LOG: statement: DELETE FROM > dbmail_messages WHERE message_idnr = 3644491 > 2009-02-22 16:11:50 CET pid=21291 19/4156 idle: LOG: statement: SELECT > message_idnr FROM dbmail_messages WHERE physmessage_id = 1834264 > 2009-02-22 16:11:50 CET pid=21291 19/4157 idle: LOG: statement: DELETE FROM > dbmail_physmessage WHERE id = 1834264 > 2009-02-22 16:11:50 CET pid=21291 19/4158 idle: LOG: statement: DELETE FROM > dbmail_messageblks WHERE physmessage_id = 1834264 > 2009-02-22 16:11:50 CET pid=21291 19/4159 idle: LOG: statement: SELECT > physmessage_id FROM dbmail_messages WHERE message_idnr = 3644713 > 2009-02-22 16:11:50 CET pid=21291 19/4160 idle: LOG: statement: DELETE FROM > dbmail_messages WHERE message_idnr = 3644713 > 2009-02-22 16:11:50 CET pid=21291 19/4161 idle: LOG: statement: SELECT > message_idnr FROM dbmail_messages WHERE physmessage_id = 1834375 > 2009-02-22 16:12:57 CET pid=21291 19/5826 idle: LOG: statement: SELECT > max(message_idnr)+1 FROM dbmail_messages WHERE mailbox_idnr=3404 > 2009-02-22 16:12:57 CET pid=21291 19/5827 idle: LOG: statement: SELECT > mailbox_idnr FROM dbmail_mailboxes WHERE name ILIKE 'ITM/intern' AND > owner_idnr=100 > 2009-02-22 16:12:57 CET pid=21291 19/5828 idle: LOG: statement: SELECT > owner_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = 3403 > 2009-02-22 16:12:57 CET pid=21291 19/5829 idle: LOG: statement: SELECT > no_select FROM dbmail_mailboxes WHERE mailbox_idnr = 3403 > 2009-02-22 16:12:57 CET pid=21291 19/5830 idle: LOG: statement: SELECT > permission,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag > FROM dbmail_mailboxes WHERE mailbox_idnr = 3403 > 2009-02-22 16:12:57 CET pid=21291 19/5831 idle: LOG: statement: SELECT > 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=3403 AND (status < 2) > UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=3403 AND > (status < 2) A > ND seen_flag=1 UNION SELECT 'c',COUNT(*) FROM dbmail_messages WHERE > mailbox_idnr=3403 AND (status < 2) AND recent_flag=1 > 2009-02-22 16:12:57 CET pid=21291 19/5832 idle: LOG: statement: SELECT > max(message_idnr)+1 FROM dbmail_messages WHERE mailbox_idnr=3403 > 2009-02-22 16:12:57 CET pid=21291 19/5833 idle: LOG: statement: SELECT > message_idnr FROM dbmail_messages WHERE mailbox_idnr = 3403 AND status IN > (0,1) ORDER BY message_idnr > 2009-02-22 16:12:57 CET pid=21291 19/5834 idle: LOG: statement: 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_m > essages msg, dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND > message_idnr BETWEEN 3644143 AND 3644145 AND mailbox_idnr = 3403 AND status > IN (0,1,2) ORDER BY message_idnr ASC > 2009-02-22 16:12:57 CET pid=21291 19/5835 idle: LOG: statement: SELECT > MIN(message_idnr) FROM dbmail_messages WHERE mailbox_idnr = 3403 AND status < > 2 AND seen_flag = 0 > 2009-02-22 16:12:57 CET pid=21291 19/5836 idle: LOG: statement: SELECT > distinct(mbx.name), mbx.mailbox_idnr, mbx.owner_idnr FROM dbmail_mailboxes > mbx LEFT JOIN dbmail_acl acl ON mbx.mailbox_idnr = acl.mailbox_id LEFT JOIN > dbmail_users > usr ON acl.user_id = usr.user_idnr WHERE mbx.name ILIKE 'Mailinglists' AND > 1=1 AND ((mbx.owner_idnr = 100) OR (acl.user_id = 100 AND acl.lookup_flag = > 1) OR (usr.userid = 'anyone' AND acl.lookup_flag = 1)) > > mfg zmi > > > ------------------------------------------------------------------------ > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
