No, you need to create an index that matches your query terms. See: http://www.postgresql.org/docs/9.1/static/indexes.html

You can try this :

delete from agent_log t1 where WHERE timestamp < now() - interval '7 days') and timespan < (select max(timestamp) from agent_log t2 where t2.class=t1.class and t2.hostname=t1.hostname and t2.id_address=t1.ip_address)

I suppose you have an index on timestamp and the other fields? A surrogate key might help, or even using timespan as primary key, if possible.

If you can create a history table, then you could:

SELECT
    class,
     max(timestamp) as timestamp,
     hostname,
     ip_address,
     promise_handle,
     promiser,
     promisee,
     policy_server,
     promise_outcome
FROM agent_log
WHERE timestamp < now() - interval '7 days'
into history_table ; 

delete from agent_log
WHERE timestamp < now() - interval '7 days'
[download]

That would be a good start towards partitioning your data, which you might need to consider.



---
libremen.com : legal case, contract and insurance claim management software

 
SELECT * FROM agent_log t1
WHERE timestamp < now() - interval '7 days' 
AND timestamp < ( 
   SELECT max(timestamp)
   FROM agent_log t2
   WHERE t2.class=t1.class AND t2.promiser=t1.promiser
    AND t2.hostname=t1.hostname AND t2.ip_address=t1.ip_address
)
ORDER by class,timestamp;
[download]

You almost had me there, but I don't think this will keep the maximum timestamp per day.

Neil Watson
watson-wilson.ca

I did some testing with a small sampling of data. Explain and analyze below.

Seq Scan on agent_log (cost=48184.35..52265496.54 rows=101705 width=122) (actual time=5288.903..175884.135 rows=203428 loops=1)
Filter: (("timestamp" < (now() - '7 days'::interval)) AND (NOT (SubPlan 1)))
SubPlan 1
-> Materialize (cost=48184.35..48646.77 rows=20342 width=77) (actual time=0.022..0.317 rows=2604 loops=203428)
-> GroupAggregate (cost=44040.21..47905.01 rows=20342 width=77) (actual time=4417.518..5286.975 rows=2604 loops=1)
-> Sort (cost=44040.21..44548.73 rows=203410 width=77) (actual time=4417.496..5223.285 rows=203428 loops=1)
Sort Key: (date_trunc('day'::text, public.agent_log."timestamp")), public.agent_log.class, public.agent_log.promiser, public.agent_log.hostname, public.agent_log.ip_address
Sort Method: external merge Disk: 19648kB
-> Seq Scan on agent_log (cost=0.00..8024.51 rows=203410 width=77) (actual time=0.011..175.827 rows=203428 loops=1)
Filter: ("timestamp" < (now() - '7 days'::interval))
Total runtime: 175924.695 ms