Delete relations efficiency in PostgreSQL I've got a collection of duplicate relations of relations that I need to tidy up. 

The table might be described like this

posts
id: 1

impressions
id: 1, post_id: 1, created_at: 2020-01-02
id: 2, post_id: 1, created_at: 2020-01-02
id: 3, post_id: 1, created_at: 2020-01-03

demographic_distribution
id: 1, impression_id: 1
id: 2, impression_id: 1
id: 3, impression_id: 2
id: 4, impression_id: 2
id: 5, impression_id: 3


I intend to remove the impression with id 1, but due to foreign key constraints, I would need to remove the related demographic_distributions first.

From demographic_distributions I would want to remove ids 1 &amp; 2. 

The following query works, but it's not very efficient. I have around 78 millions rows in the demographic_distribution table, and I have other similar sized related tables.

DELETE FROM demographic_distribution
WHERE impression_id NOT IN (
  SELECT id
  FROM impressions AS i1
  WHERE id IN (
    SELECT MAX(id)
    FROM impressions AS i2
    WHERE i2.post_id = i1.post_id
      AND i2.created_at::date = i1.created_at::date
    GROUP BY i2.post_id    
  )
);
You can do all in a single data modifying CTE

with deleted_impressions as (
  delete from impressions i1
  where exists (select * 
                from impressions i2
                where i2.id &gt; i1.id
                  and i2.created_at = i1.created_at)
  returning id
), delete_reg_impressions as (
  delete from regional_impressions
  where impression_id in (select id 
                        from deleted_impressions)
) 
delete from demographic_distribution
where impression_id in (select id 
                        from deleted_impressions);


First the duplicates from impressions are removed and all IDs that are returned from that query are then used to delete the corresponding rows in demographic_distribution as this is done in a single (atomic) statement the foreign keys are still valid.