shulkae wrote:
> DELETE from mytable WHERE (now() - timestamp_field > INTERVAL '400
> hour' ) LIMIT 100;
>
Force of habit (not sure if the optimizer does this trick for you) is
first to rewrite this as follows:
DELETE from mytable WHERE timestamp_field < (now() - INTERVAL '400
hour' ) LIMIT 100;
Just to turn the comparison into a constant being compared with the field.
If there's a useful primary key on this table, you can do this to delete:
DELETE FROM mytable where pkey IN (SELECT pkey from mytable WHERE
timestamp_field < (now() - INTERVAL '400 hour' ) LIMIT 100);
If there's not a primary key, you can use a hidden field named ctid to
get your record list:
http://www.postgresql.org/docs/current/static/ddl-system-columns.html
And then use that as the way to communicate the candidate deletion list
out of the subselect:
DELETE FROM mytable where ctid IN (SELECT ctid from mytable WHERE
timestamp_field < (now() - INTERVAL '400 hour' ) LIMIT 100);
The main advantage of using the primary key is that the result will be
more portable to other databases--the ctid field is very much a
PostgreSQL specific hack.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
