explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1iZ

Settings
# exclusive inclusive rows x rows loops node
1. 9.957 4,650,031.297 ↓ 407.6 2,038 1

GroupAggregate (cost=6,228,469.35..6,228,469.56 rows=5 width=24) (actual time=4,650,018.205..4,650,031.297 rows=2,038 loops=1)

  • Output: l.cid, l.property_id, count(DISTINCT l.application_id), l.property_week_id
  • Group Key: l.cid, l.property_id, l.property_week_id
  • Buffers: local hit=666257754 read=152002 written=57492
2. 55.077 4,650,021.340 ↓ 6,062.0 30,310 1

Sort (cost=6,228,469.35..6,228,469.36 rows=5 width=20) (actual time=4,650,018.175..4,650,021.340 rows=30,310 loops=1)

  • Output: l.cid, l.property_id, l.property_week_id, l.application_id
  • Sort Key: l.property_id, l.property_week_id
  • Sort Method: quicksort Memory: 3136kB
  • Buffers: local hit=666257754 read=152002 written=57492
3. 142.688 4,649,966.263 ↓ 6,062.0 30,310 1

Nested Loop (cost=408.50..6,228,469.29 rows=5 width=20) (actual time=16.982..4,649,966.263 rows=30,310 loops=1)

  • Output: l.cid, l.property_id, l.property_week_id, l.application_id
  • Buffers: local hit=666257754 read=152002 written=57492
4. 150.213 1,524.851 ↓ 304.8 31,393 1

Hash Join (cost=408.06..350,906.27 rows=103 width=32) (actual time=2.076..1,524.851 rows=31,393 loops=1)

  • Output: a.cid, a.lease_interval_id, a.property_week_id, a.event_datetime, cpw.id
  • Hash Cond: (a.property_week_id = cpw.id)
  • Join Filter: ((a.event_datetime)::date < (cpw.end_date + '1 day'::interval))
  • Buffers: local hit=1153 read=56280 written=22278
5. 1,372.618 1,372.618 ↓ 101.6 31,393 1

Seq Scan on pg_temp_23.activities a (cost=0.00..350,485.46 rows=309 width=24) (actual time=0.039..1,372.618 rows=31,393 loops=1)

  • Output: a.cid, a.lease_interval_id, a.event_type_id, a.event_datetime, a.default_event_result_id, a.default_event_result_ids, a.property_week_id
  • Filter: ((a.event_type_id = ANY ('{9,78}'::integer[])) AND (a.cid = 15077) AND (2 = ANY (a.default_event_result_ids)))
  • Rows Removed by Filter: 6140095
  • Buffers: local hit=1067 read=56280 written=22278
6. 0.768 2.020 ↑ 1.0 5,153 1

Hash (cost=240.59..240.59 rows=5,153 width=12) (actual time=2.020..2.020 rows=5,153 loops=1)

  • Output: cpw.id, cpw.end_date
  • Buckets: 8192 Batches: 1 Memory Usage: 286kB
  • Buffers: local hit=86
7. 1.252 1.252 ↑ 1.0 5,153 1

Seq Scan on pg_temp_23.property_weeks cpw (cost=0.00..240.59 rows=5,153 width=12) (actual time=0.005..1.252 rows=5,153 loops=1)

  • Output: cpw.id, cpw.end_date
  • Buffers: local hit=86
8. 4,645,677.000 4,648,298.724 ↑ 1.0 1 31,393

Index Scan using idx_temp_leads on pg_temp_23.leads l (cost=0.43..57,063.69 rows=1 width=32) (actual time=71.018..148.068 rows=1 loops=31,393)

  • Output: l.id, l.cid, l.property_id, l.property_name, l.lookup_code, l.application_id, l.lease_interval_id, l.application_stage_id, l.application_status_id, l.lease_interval_type_id, l.post_date, l.reporting_post_date, l.lease_approved_on, l.cancellation_list_type_id, l.application_completed_on, l.application_completed, l.cancelled_on, l.event_sub_type_id, l.first_event, l.default_event_result_ids, l.is_system_generated, l.property_week_id
  • Index Cond: ((l.cid = 15077) AND (l.lease_interval_id = a.lease_interval_id))
  • Filter: (((a.event_datetime <= l.lease_approved_on) OR (l.lease_approved_on IS NULL)) AND (a.property_week_id = l.property_week_id) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 36
  • Buffers: local hit=666256601 read=95722 written=35214
9.          

SubPlan (forIndex Scan)

10. 2,621.724 2,621.724 ↑ 5.0 1 31,211

Seq Scan on pg_temp_23.lease_interval_ids lid (cost=0.00..39.31 rows=5 width=4) (actual time=0.070..0.084 rows=1 loops=31,211)

  • Output: lid.lease_interval_id
  • Filter: (lid.property_week_id = l.property_week_id)
  • Rows Removed by Filter: 1010
  • Buffers: local hit=184878
Planning time : 0.641 ms
Execution time : 4,650,033.364 ms