explain-analyze.txt

                                                                                                  QUERY PLAN                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=546.68..546.72 rows=4 width=4) (actual time=1.886..1.887 rows=5 loops=1)
   Group Key: a.property_id
   ->  Hash Anti Join  (cost=347.73..546.55 rows=54 width=4) (actual time=1.814..1.885 rows=5 loops=1)
         Hash Cond: (a.property_id = b.property_id)
         Join Filter: (b.created_at > a.created_at)
         ->  Bitmap Heap Scan on change_histories a  (cost=73.84..271.28 rows=61 width=12) (actual time=1.079..1.147 rows=15 loops=1)
               Recheck Cond: ((target_type)::text = 'Asset'::text)
               Filter: ((created_at >= '2015-01-23 00:00:00'::timestamp without time zone) AND (created_at <= '2015-01-29 00:00:00'::timestamp without time zone) AND ((operation)::text = 'remove'::text))
               Rows Removed by Filter: 2862
               Heap Blocks: exact=66
               ->  Bitmap Index Scan on index_change_histories_on_target_type  (cost=0.00..73.82 rows=2872 width=0) (actual time=0.331..0.331 rows=2877 loops=1)
                     Index Cond: ((target_type)::text = 'Asset'::text)
         ->  Hash  (cost=271.32..271.32 rows=206 width=12) (actual time=0.726..0.726 rows=461 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 22kB
               ->  Bitmap Heap Scan on change_histories b  (cost=73.88..271.32 rows=206 width=12) (actual time=0.437..0.677 rows=461 loops=1)
                     Recheck Cond: ((target_type)::text = 'Asset'::text)
                     Filter: ((created_at >= '2015-01-23 00:00:00'::timestamp without time zone) AND (created_at <= '2015-01-29 00:00:00'::timestamp without time zone) AND ((operation)::text = 'add'::text))
                     Rows Removed by Filter: 2416
                     Heap Blocks: exact=66
                     ->  Bitmap Index Scan on index_change_histories_on_target_type  (cost=0.00..73.82 rows=2872 width=0) (actual time=0.189..0.189 rows=2877 loops=1)
                           Index Cond: ((target_type)::text = 'Asset'::text)
 Planning time: 0.150 ms
 Execution time: 1.924 ms
(23 rows)

explain.txt

                                                                                                  QUERY PLAN                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=546.68..546.72 rows=4 width=4)
   Group Key: a.property_id
   ->  Hash Anti Join  (cost=347.73..546.55 rows=54 width=4)
         Hash Cond: (a.property_id = b.property_id)
         Join Filter: (b.created_at > a.created_at)
         ->  Bitmap Heap Scan on change_histories a  (cost=73.84..271.28 rows=61 width=12)
               Recheck Cond: ((target_type)::text = 'Asset'::text)
               Filter: ((created_at >= '2015-01-23 00:00:00'::timestamp without time zone) AND (created_at <= '2015-01-29 00:00:00'::timestamp without time zone) AND ((operation)::text = 'remove'::text))
               ->  Bitmap Index Scan on index_change_histories_on_target_type  (cost=0.00..73.82 rows=2872 width=0)
                     Index Cond: ((target_type)::text = 'Asset'::text)
         ->  Hash  (cost=271.32..271.32 rows=206 width=12)
               ->  Bitmap Heap Scan on change_histories b  (cost=73.88..271.32 rows=206 width=12)
                     Recheck Cond: ((target_type)::text = 'Asset'::text)
                     Filter: ((created_at >= '2015-01-23 00:00:00'::timestamp without time zone) AND (created_at <= '2015-01-29 00:00:00'::timestamp without time zone) AND ((operation)::text = 'add'::text))
                     ->  Bitmap Index Scan on index_change_histories_on_target_type  (cost=0.00..73.82 rows=2872 width=0)
                           Index Cond: ((target_type)::text = 'Asset'::text)
(16 rows)

query.sql

      SELECT DISTINCT a.property_id FROM change_histories a
        LEFT OUTER JOIN change_histories b
          ON a.property_id = b.property_id
          AND b.created_at BETWEEN '2015-01-23' AND '2015-01-29'
          AND b.target_type='Asset'
          AND b.operation = 'add'
          AND b.created_at > a.created_at
        WHERE a.created_at BETWEEN '2015-01-23' AND '2015-01-29'
          AND a.target_type = 'Asset'
          AND a.operation = 'remove'
          AND b.property_id IS NULL