Consolidating large table 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 &lt; now() - interval '7 days' 
      GROUP BY
          class,
          DATE_TRUNC( 'day', timestamp),
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
)
AND timestamp &lt; now() - interval '7 days'
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.


Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts &lt; now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;


Detailed explanation:
Select first row in each GROUP BY group?