title: Postgres: How to remove duplicates of old records in log table
body: <p>I have a log table with about 5 million records:</p>
  
  <pre><code>  id BIGSERIAL, 
    object_type_name VARCHAR(255), 
    object_id BIGINT, 
    user_id BIGINT,
    service_id BIGINT,
    op_id INTEGER,
    dt TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(),
    property_name VARCHAR(255),
    CONSTRAINT object_log_object_log_pkey PRIMARY KEY(id)
  </code></pre>
  
  <p>I need to delete duplicate records leaving only the latest record (the one with the max <code>id</code>). The problem is that my query is very slow (> 1 min):</p>
  
  <pre><code>DELETE FROM sys.object_log AS t5 
   USING (
     SELECT t3.id 
       FROM sys.object_log t3 LEFT JOIN (
         SELECT t1.id 
           FROM sys.object_log t1 
          WHERE t1.id = ( 
                SELECT max(t2.id) 
                  FROM sys.object_log t2 
                 WHERE t2.object_type_name = t1.object_type_name 
                   AND t2.object_id = t1.object_id
                   AND t2.property_name = t1.property_name 
                )
       ) t4 ON t3.id=t4.id 
     WHERE t4.id IS NULL
   ) t6 
   WHERE t5.id = t6.id
  </code></pre>
  
  <p><strong>QUERY PLAN</strong></p>
  
  <pre><code>Delete on object_log t5  (cost=1.30..72821293.06 rows=8298362 width=18)
    -&gt;  Merge Join  (cost=1.30..72821293.06 rows=8298362 width=18)
          Merge Cond: (t3.id = t5.id)
          -&gt;  Merge Anti Join  (cost=0.86..72365877.02 rows=8298362 width=20)
                Merge Cond: (t3.id = t1.id)
                -&gt;  Index Scan using object_log_object_log_pkey on object_log t3  (cost=0.43..330836.36 rows=8340062 width=14)
                -&gt;  Index Scan using object_log_object_log_pkey on object_log t1  (cost=0.43..72013669.25 rows=41700 width=14)
                      Filter: (id = (SubPlan 1))
                      SubPlan 1
                        -&gt;  Aggregate  (cost=8.58..8.59 rows=1 width=8)
                              -&gt;  Index Only Scan using object_log_idx1 on object_log t2  (cost=0.56..8.58 rows=1 width=8)
                                    Index Cond: ((object_type_name = (t1.object_type_name)::text) AND (object_id = t1.object_id) AND (property_name = (t1.property_name)::text))
          -&gt;  Index Scan using object_log_object_log_pkey on object_log t5  (cost=0.43..330836.36 rows=8340062 width=14)
  </code></pre>
  
  <p>Any idea how to improve performance ? </p>
  
  <p><strong>UPD.1</strong></p>
  
  <p>Next query is also slow:</p>
  
  <pre><code>DELETE FROM sys.object_log
      WHERE id IN (
      SELECT id
      FROM (
          SELECT id, ROW_NUMBER() OVER w AS rnum
          FROM sys.object_log
          WINDOW w AS (PARTITION BY object_type_name, object_id, property_name ORDER BY id)
          ) t
      WHERE t.rnum &gt; 1)
  </code></pre>
  
  <p><strong>QUERY PLAN</strong></p>
  
  <pre><code>QUERY PLAN
  Delete on object_log  (cost=1703454.67..1960873.74 rows=2780021 width=38)
    -&gt;  Hash Semi Join  (cost=1703454.67..1960873.74 rows=2780021 width=38)
          Hash Cond: (object_log.id = t.id)
          -&gt;  Seq Scan on object_log  (cost=0.00..197648.62 rows=8340062 width=14)
          -&gt;  Hash  (cost=1668704.40..1668704.40 rows=2780021 width=40)
                -&gt;  Subquery Scan on t  (cost=1355952.08..1668704.40 rows=2780021 width=40)
                      Filter: (t.rnum &gt; 1)
                      -&gt;  WindowAgg  (cost=1355952.08..1564453.63 rows=8340062 width=38)
                            -&gt;  Sort  (cost=1355952.08..1376802.23 rows=8340062 width=30)
                                  Sort Key: object_log_1.object_type_name, object_log_1.object_id, object_log_1.property_name, object_log_1.id
                                  -&gt;  **Seq Scan** on object_log object_log_1  (cost=0.00..197648.62 rows=8340062 width=30)
  </code></pre>