HM,
> The problem is the foreign key constraints on msgrcpt.
> I found a solution:
...
> Voila, takes less than 30 minutes, including typing/pasting, on 3.4
> million rows in msgs/4.2 million rows in msgrcpt which should be a
> small enough delay for most uses.
Thanks, useful for purging majority of records.
Perhaps using old style deletion when ON DELETE CASCADE constraint
was not yet suggested could give similar results (README.sql-mysql):
-- (optional) just in case the ON DELETE CASCADE did not do its job, we may
-- explicitly delete orphaned records (with no corresponding msgs entry);
-- if ON DELETE CASCADE did work, there should be no deletions at this step
DELETE FROM quarantine
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id);
DELETE FROM msgrcpt
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id);
> > DELETE FROM maddr WHERE id IN (
> > SELECT id FROM maddr LEFT JOIN (
> > SELECT sid AS id, 1 AS f FROM msgs UNION ALL
> > SELECT rid AS id, 1 AS f FROM msgrcpt
> > ) AS u USING(id) WHERE u.f IS NULL);
> >
> > I'd be interested in your experience before I update
> > the documentation.
>
> DELETEs are expensive but SELECTs are quick. The cost (through EXPLAIN
> <query>) of the above for my data (after getting rid of old messages)
> was "cost=1623113.61..1765429.58 rows=1664586 width=6".
>
> So instead, I used temporary tables to get a list of maddr.ids to
> delete, and the cost for the final delete was
> "cost=86070.32..177104.67 rows=1247620 width=6", an order of magnitude
> less.
>
> As it says in the postgres-docs, what is speedy for a small db might
> not be speedy for a large one. It seems that for postgres, using
> temporary tables can pay in a big way.
I don't know. Trying the CREATE TEMPORARY TABLE AS, followed by a DELETE
took about the same time as combining both in the same statement.
Building a table of maddr id-s to be purged took majority of time
(on frequent deletes where deletion does not remove lots of records):
CREATE TEMPORARY TABLE unused AS (
SELECT id FROM maddr LEFT JOIN (
SELECT sid AS id, 1 AS f FROM msgs UNION ALL
SELECT rid AS id, 1 AS f FROM msgrcpt)
AS u USING(id) WHERE u.f IS NULL);
DELETE FROM maddr WHERE id IN (SELECT * FROM unused);
Perhaps you had something else in mind?
Btw, both statements should probably be combined in a transaction
if amavisd is left running during purging.
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
