Andrzej,
> > DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60;
> > DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content IS
> > NULL;
>
> For large tables I'd suggest one improvement:
> SET @min_time := UNIX_TIMESTAMP()-14*24*60*60;
> DELETE FROM msgs WHERE time_num < @mintime;
>
> That would avoid calculating UNIX_TIMESTAMP() and the subtraction for
> every row in the table (thousands or millions times).
Did you try it and makes a difference, or is it just a theory?
I'd think the constant subexpression would be factored out of the loop
by an optimizer.
> > README.sql-pg:
> > DELETE FROM msgs WHERE time_iso < now() - INTERVAL '3 weeks';
> > DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS
> > NULL;
> >
> > At least on PostgreSQL it does use an index on time_iso.
>
> They, too, force SQL to calculate now() and the subtraction for every
> row in the table (see EXPLAIN VERBOSE), but I don't know how to
> optimize these queries in pure SQL as understood by PgSQL.
I don't think it makes any difference with PostgreSQL:
SELECT now() - INTERVAL '30 days';
2007-07-30 00:02:57
EXPLAIN DELETE FROM msgs WHERE time_iso < '2007-07-30';
QUERY PLAN
-----------------------------------------------------------------------------------------
Bitmap Heap Scan on msgs (cost=18068.35..127812.26 rows=548073 width=6)
Recheck Cond: (time_iso < '2007-07-30 00:00:00+02'::timestamp with time zone)
-> Bitmap Index Scan on msgs_idx_time_iso (cost=0.00..17931.33 rows=548073
width=0)
Index Cond: (time_iso < '2007-07-30 00:00:00+02'::timestamp with time
zone)
EXPLAIN DELETE FROM msgs WHERE time_iso < now() - INTERVAL '30 days';
QUERY PLAN
-----------------------------------------------------------------------------------------
Bitmap Heap Scan on msgs (cost=18073.10..130559.08 rows=548170 width=6)
Recheck Cond: (time_iso < (now() - '30 days'::interval))
-> Bitmap Index Scan on msgs_idx_time_iso (cost=0.00..17936.06 rows=548170
width=0)
Index Cond: (time_iso < (now() - '30 days'::interval))
(similar on actual deletions with EXPLAIN ANALYZE, even without a foreign key
constraint)
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
