explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YW4I

Settings
# exclusive inclusive rows x rows loops node
1. 1,520.662 4,228.030 ↓ 5.2 330,963 1

Hash Join (cost=103,732.67..677,679.19 rows=63,218 width=192) (actual time=795.328..4,228.030 rows=330,963 loops=1)

  • Hash Cond: (cal.property_id = load_prop.property_id)
2. 373.317 2,707.206 ↓ 5.2 331,375 1

Hash Join (cost=103,727.27..660,683.95 rows=63,218 width=935) (actual time=794.917..2,707.206 rows=331,375 loops=1)

  • Hash Cond: ((cal.application_stage_id = ass.application_stage_id) AND (cal.application_status_id = ass.application_status_id))
3. 1,610.850 2,333.830 ↓ 4.4 331,382 1

Bitmap Heap Scan on cached_application_logs cal (cost=103,722.28..657,787.09 rows=75,323 width=931) (actual time=794.843..2,333.830 rows=331,382 loops=1)

  • Recheck Cond: (apply_through_post_date >= '2020-01-31'::date)
  • Filter: ((NOT is_post_date_ignored) AND CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 1) THEN false ELSE true END AND (reporting_post_date < '2020-02-01 00:00:00'::timestamp without time zone) AND (occupancy_type_id <> 4) AND (lease_interval_type_id = 1) AND (cid = 15077))
  • Rows Removed by Filter: 42772
  • Heap Blocks: exact=350281
4. 29.049 722.888 ↓ 0.0 0 1

BitmapAnd (cost=103,709.83..103,709.83 rows=175,376 width=0) (actual time=722.887..722.888 rows=0 loops=1)

5. 53.515 53.515 ↑ 1.2 374,177 1

Bitmap Index Scan on idx_cached_application_logs_apply_through_post_date (cost=0.00..8,314.54 rows=450,414 width=0) (actual time=53.515..53.515 rows=374,177 loops=1)

  • Index Cond: (apply_through_post_date >= '2020-01-31'::date)
6. 640.324 640.324 ↓ 1.0 5,175,512 1

Bitmap Index Scan on idx_cached_application_logs_is_post_date_ignored (cost=0.00..95,357.38 rows=5,166,526 width=0) (actual time=640.324..640.324 rows=5,175,512 loops=1)

  • Index Cond: (is_post_date_ignored = false)
7.          

SubPlan (for Bitmap Heap Scan)

8. 0.092 0.092 ↑ 1.5 2 1

Seq Scan on list_items li (cost=0.00..12.44 rows=3 width=4) (actual time=0.089..0.092 rows=2 loops=1)

  • Filter: ((default_list_item_id = ANY ('{66,90}'::integer[])) AND (cid = 15077))
  • Rows Removed by Filter: 294
9. 0.014 0.059 ↑ 1.0 18 1

Hash (cost=4.72..4.72 rows=18 width=16) (actual time=0.059..0.059 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.045 0.045 ↑ 1.0 18 1

Seq Scan on application_stage_statuses ass (cost=0.00..4.72 rows=18 width=16) (actual time=0.011..0.045 rows=18 loops=1)

  • Filter: (lease_interval_type_id = 1)
  • Rows Removed by Filter: 40
11. 0.089 0.162 ↑ 1.0 151 1

Hash (cost=3.51..3.51 rows=151 width=28) (actual time=0.162..0.162 rows=151 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
12. 0.073 0.073 ↑ 1.0 151 1

Seq Scan on load_prop (cost=0.00..3.51 rows=151 width=28) (actual time=0.005..0.073 rows=151 loops=1)