explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UBi1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=990,903.20..1,094,873.43 rows=157,945 width=1,257) (actual rows= loops=)

  • Merge Cond: (clp.lease_id = cl_prior.transfer_lease_id)
  • Join Filter: (cl_prior.cid = clp.cid)
  • -> Index Scan using idx_cached_leases_transfer_lease_id on cached_leases cl_prior (cost=0.42..70006.10 rows=420280 width=16)" Filter: (cid = 13531)
2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=990,902.78..1,088,433.82 rows=157,945 width=1,253) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=990,902.78..994,456.54 rows=157,945 width=1,359) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=990,902.78..991,297.64 rows=157,945 width=1,351) (actual rows= loops=)

  • Sort Key: clp.lease_id, lus.slot_number
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=468,435.44..977,264.97 rows=157,945 width=1,351) (actual rows= loops=)

  • Hash Cond: ((cll.cid = cd.cid) AND (cll.primary_customer_id = cd.customer_id))
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=442,381.43..948,446.92 rows=157,945 width=1,346) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=442,381.00..871,574.16 rows=157,945 width=276) (actual rows= loops=)

  • Hash Cond: ((li.cid = lt.cid) AND (li.lease_term_id = lt.id))
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=442,361.11..870,783.10 rows=145,705 width=135) (actual rows= loops=)

  • Hash Cond: (clp.lease_id = lus.lease_id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=427,672.29..849,198.53 rows=145,032 width=127) (actual rows= loops=)

  • Hash Cond: ((li.cid = lsw.cid) AND (li.lease_start_window_id = lsw.id))
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=427,584.45..848,348.79 rows=145,032 width=123) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=427,584.02..778,172.57 rows=145,032 width=107) (actual rows= loops=)

  • Hash Cond: (cll.occupancy_type_id = ot_lease.id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=427,582.80..777,629.29 rows=145,032 width=98) (actual rows= loops=)

  • Hash Cond: ((clp.cid = cll.cid) AND (clp.cached_lease_log_id = cll.id))
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=73,904.74..361,980.81 rows=145,032 width=31) (actual rows= loops=)

  • Hash Cond: (clp.property_id = load_prop.property_id)
14. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on cached_lease_periods clp (cost=73,896.47..359,978.35 rows=145,032 width=34) (actual rows= loops=)

  • Recheck Cond: (post_month = '2020-05-01'::date)
  • Filter: ((cid = 13531) AND (lease_status_type_id = ANY ('{3,4,5}'::integer[])))
15. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cached_lease_periods_prop_post_month (cost=0.00..73,860.21 rows=197,788 width=0) (actual rows= loops=)

  • Index Cond: (post_month = '2020-05-01'::date)
16. 0.000 0.000 ↓ 0.0

Hash (cost=5.90..5.90 rows=190 width=5) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on load_prop (cost=0.00..5.90 rows=190 width=5) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=224,476.66..224,476.66 rows=4,665,093 width=75) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on cached_lease_logs cll (cost=0.00..224,476.66 rows=4,665,093 width=75) (actual rows= loops=)

  • Filter: (cid = 13531)
20. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=13) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on occupancy_types ot_lease (cost=0.00..1.10 rows=10 width=13) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.43..0.47 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = cll.active_lease_interval_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid) AND (lease_id = cll.lease_id))
23. 0.000 0.000 ↓ 0.0

Hash (cost=60.85..60.85 rows=1,799 width=16) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on lease_start_windows lsw (cost=0.00..60.85 rows=1,799 width=16) (actual rows= loops=)

  • Filter: (cid = 13531)
25. 0.000 0.000 ↓ 0.0

Hash (cost=9,431.30..9,431.30 rows=420,602 width=16) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on lease_unit_spaces lus (cost=0.00..9,431.30 rows=420,602 width=16) (actual rows= loops=)

  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (cid = 13531))
27. 0.000 0.000 ↓ 0.0

Hash (cost=15.23..15.23 rows=311 width=153) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on lease_terms lt (cost=0.00..15.23 rows=311 width=153) (actual rows= loops=)

  • Filter: (cid = 13531)
29. 0.000 0.000 ↓ 0.0

Index Scan using idx_customers_id on customers c (cost=0.42..0.48 rows=1 width=1,078) (actual rows= loops=)

  • Index Cond: (id = cll.primary_customer_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid))
30. 0.000 0.000 ↓ 0.0

Hash (cost=15,760.19..15,760.19 rows=686,255 width=17) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on customer_details cd (cost=0.00..15,760.19 rows=686,255 width=17) (actual rows= loops=)

  • Filter: (cid = 13531)