explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gREY

Settings
# exclusive inclusive rows x rows loops node
1. 4.530 874,365.636 ↑ 1.0 1 1

Aggregate (cost=1,194.97..1,195.00 rows=1 width=8) (actual time=874,365.629..874,365.636 rows=1 loops=1)

2. 2,523.152 874,361.106 ↓ 735.0 735 1

Nested Loop Semi Join (cost=22.12..1,194.97 rows=1 width=4) (actual time=7,436.642..874,361.106 rows=735 loops=1)

  • Join Filter: ((e.lease_interval_id = ca.lease_interval_id) OR (e.customer_id = ap.customer_id))
  • Rows Removed by Join Filter: 9,150,641
3. 12.534 647.233 ↓ 4,131.0 4,131 1

Nested Loop Left Join (cost=21.55..1,191.09 rows=1 width=12) (actual time=1.055..647.233 rows=4,131 loops=1)

4. 14.605 618.175 ↓ 4,131.0 4,131 1

Nested Loop Left Join (cost=21.14..1,190.53 rows=1 width=20) (actual time=0.408..618.175 rows=4,131 loops=1)

5. 12.148 587.046 ↓ 4,131.0 4,131 1

Nested Loop Left Join (cost=20.72..1,188.17 rows=1 width=20) (actual time=0.363..587.046 rows=4,131 loops=1)

6. 13.330 508.802 ↓ 4,131.0 4,131 1

Nested Loop Left Join (cost=20.29..1,187.47 rows=1 width=28) (actual time=0.299..508.802 rows=4,131 loops=1)

7. 12.641 462.424 ↓ 4,131.0 4,131 1

Nested Loop (cost=19.87..1,185.00 rows=1 width=24) (actual time=0.263..462.424 rows=4,131 loops=1)

8. 14.810 363.032 ↓ 590.1 4,131 1

Nested Loop (cost=19.44..1,178.73 rows=7 width=28) (actual time=0.227..363.032 rows=4,131 loops=1)

9. 17.327 224.292 ↓ 59.9 4,131 1

Hash Join (cost=19.02..1,006.23 rows=69 width=20) (actual time=0.153..224.292 rows=4,131 loops=1)

  • Hash Cond: ((ca.lease_interval_type_id = ass.lease_interval_type_id) AND (ca.application_stage_id = ass.application_stage_id) AND (ca.application_status_id = ass.application_status_id))
10. 206.886 206.886 ↓ 43.5 4,131 1

Index Scan using idx_cached_applications_property_id on cached_applications ca (cost=0.43..980.23 rows=95 width=44) (actual time=0.059..206.886 rows=4,131 loops=1)

  • Index Cond: (property_id = 664,671)
  • Filter: (cid = 14,529)
11. 0.014 0.079 ↑ 1.0 68 1

Hash (cost=16.04..16.04 rows=68 width=12) (actual time=0.078..0.079 rows=68 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
12. 0.065 0.065 ↑ 1.0 68 1

Seq Scan on application_stage_statuses ass (cost=0.00..16.04 rows=68 width=12) (actual time=0.016..0.065 rows=68 loops=1)

13. 123.930 123.930 ↑ 1.0 1 4,131

Index Scan using idx_applicant_applications_cid_application_id on applicant_applications aa (cost=0.43..2.47 rows=1 width=12) (actual time=0.028..0.030 rows=1 loops=4,131)

  • Index Cond: ((cid = 14,529) AND (application_id = ca.id))
  • Filter: ((deleted_on IS NULL) AND (customer_type_id = 1))
  • Rows Removed by Filter: 0
14. 86.751 86.751 ↑ 1.0 1 4,131

Index Scan using idx_applicants_id on applicants ap (cost=0.43..0.89 rows=1 width=12) (actual time=0.021..0.021 rows=1 loops=4,131)

  • Index Cond: (id = aa.applicant_id)
  • Filter: (cid = 14,529)
15. 33.048 33.048 ↓ 0.0 0 4,131

Index Scan using idx_leases_id on leases l (cost=0.42..2.44 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=4,131)

  • Index Cond: (id = ca.lease_id)
  • Filter: ((cid = 14,529) AND (cid = ca.cid))
16. 66.096 66.096 ↓ 0.0 0 4,131

Index Scan using idx_lease_processes_cid_lease_id_customer_id on lease_processes lp (cost=0.42..0.66 rows=1 width=8) (actual time=0.015..0.016 rows=0 loops=4,131)

  • Index Cond: ((l.cid = cid) AND (cid = 14,529) AND (l.id = lease_id))
  • Filter: (customer_id IS NULL)
  • Rows Removed by Filter: 0
17. 16.524 16.524 ↓ 0.0 0 4,131

Index Scan using pk_property_units on property_units pu (cost=0.42..2.37 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=4,131)

  • Index Cond: ((cid = ca.cid) AND (cid = 14,529) AND (id = ca.property_unit_id))
  • Filter: (deleted_on IS NULL)
18. 16.524 16.524 ↓ 0.0 0 4,131

Index Only Scan using uk_unit_addresses_cid_property_unit_id_address_type_id on unit_addresses ua (cost=0.42..0.53 rows=1 width=8) (actual time=0.003..0.004 rows=0 loops=4,131)

  • Index Cond: ((cid = pu.cid) AND (cid = 14,529) AND (property_unit_id = pu.id))
  • Heap Fetches: 0
19. 871,190.721 871,190.721 ↓ 2,215.0 2,215 4,131

Index Scan using idx_events_event_datetime on events e (cost=0.56..3.84 rows=1 width=8) (actual time=0.548..210.891 rows=2,215 loops=4,131)

  • Index Cond: ((event_datetime >= '08/30/2020 00:00:00 MDT'::timestamp with time zone) AND (event_datetime <= '09/01/2020 23:59:59 MDT'::timestamp with time zone))
  • Filter: ((cid = 14,529) AND (property_id = 664,671))
  • Rows Removed by Filter: 290,505
Planning time : 13.833 ms
Execution time : 874,365.913 ms