I need some help with a complex query. Table def:
Table "public.agent_log"
Column | Type | Modifiers
-----------------+--------------------------+-------------------------------------------------------------
class | text |
hostname | text |
ip_address | text |
promise_handle | text |
promiser | text |
promisee | text |
policy_server | text |
rowId | integer | not null default nextval('"agent_log_rowId_seq"'::regclass)
timestamp | timestamp with time zone |
promise_outcome | text |
Indexes:
"primary_key" PRIMARY KEY, btree ("rowId")
"client_by_timestamp" btree ("timestamp", class)
Each line in the table is a log. There will be many rows that are the same except for the timestamp. As the data ages, I want to cull the rows. I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group. Example:
2014-01-22T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-22T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 cpu_1 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 cpu_1 192.168.0.2 mars.example.com ...
I'm testing with this, note that I'm using select instead of delete for this test. I can't get the where correct and the few things I've tried resulted in a very long query. Any suggestions for improvement or alternate approaches welcome.
explain SELECT * FROM agent_log
WHERE
NOT IN (
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'
GROUP BY
class,
DATE_TRUNC( 'day', timestamp),
hostname,
ip_address,
promise_handle,
promiser,
promisee,
policy_server,
promise_outcome
)
AND timestamp < now() - interval '7 days'