explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ild

Settings
# exclusive inclusive rows x rows loops node
1. 145.005 18,985.779 ↑ 1.9 84,745 1

Merge Left Join (cost=990,903.20..1,094,873.43 rows=157,945 width=1,257) (actual time=12,044.624..18,985.779 rows=84,745 loops=1)

  • Merge Cond: (clp.lease_id = cl_prior.transfer_lease_id)
  • Join Filter: (cl_prior.cid = clp.cid)
2. 6,495.936 18,781.639 ↑ 1.9 84,745 1

WindowAgg (cost=990,902.78..1,088,433.82 rows=157,945 width=1,253) (actual time=12,044.594..18,781.639 rows=84,745 loops=1)

3. 184.642 12,285.703 ↑ 1.9 84,745 1

WindowAgg (cost=990,902.78..994,456.54 rows=157,945 width=1,359) (actual time=12,044.565..12,285.703 rows=84,745 loops=1)

4. 178.136 12,101.061 ↑ 1.9 84,745 1

Sort (cost=990,902.78..991,297.64 rows=157,945 width=1,351) (actual time=12,044.553..12,101.061 rows=84,745 loops=1)

  • Sort Key: clp.lease_id, lus.slot_number
  • Sort Method: quicksort Memory: 46539kB
5. 142.946 11,922.925 ↑ 1.9 84,745 1

Hash Left Join (cost=468,435.44..977,264.97 rows=157,945 width=1,351) (actual time=9,335.092..11,922.925 rows=84,745 loops=1)

  • Hash Cond: ((cll.cid = cd.cid) AND (cll.primary_customer_id = cd.customer_id))
6. 195.567 10,980.619 ↑ 1.9 84,745 1

Nested Loop Left Join (cost=442,381.43..948,446.92 rows=157,945 width=1,346) (actual time=8,531.227..10,980.619 rows=84,745 loops=1)

7. 91.666 10,446.072 ↑ 1.9 84,745 1

Hash Left Join (cost=442,381.00..871,574.16 rows=157,945 width=276) (actual time=8,531.203..10,446.072 rows=84,745 loops=1)

  • Hash Cond: ((li.cid = lt.cid) AND (li.lease_term_id = lt.id))
8. 133.396 10,353.997 ↑ 1.7 84,745 1

Hash Join (cost=442,361.11..870,783.10 rows=145,705 width=135) (actual time=8,530.777..10,353.997 rows=84,745 loops=1)

  • Hash Cond: (clp.lease_id = lus.lease_id)
9. 96.099 9,769.676 ↑ 1.7 84,360 1

Hash Left Join (cost=427,672.29..849,198.53 rows=145,032 width=127) (actual time=8,079.191..9,769.676 rows=84,360 loops=1)

  • Hash Cond: ((li.cid = lsw.cid) AND (li.lease_start_window_id = lsw.id))
10. 170.591 9,671.612 ↑ 1.7 84,360 1

Nested Loop Left Join (cost=427,584.45..848,348.79 rows=145,032 width=123) (actual time=8,077.201..9,671.612 rows=84,360 loops=1)

11. 83.841 9,163.581 ↑ 1.7 84,360 1

Hash Left Join (cost=427,584.02..778,172.57 rows=145,032 width=107) (actual time=8,077.176..9,163.581 rows=84,360 loops=1)

  • Hash Cond: (cll.occupancy_type_id = ot_lease.id)
12. 696.166 9,079.719 ↑ 1.7 84,360 1

Hash Left Join (cost=427,582.80..777,629.29 rows=145,032 width=98) (actual time=8,077.133..9,079.719 rows=84,360 loops=1)

  • Hash Cond: ((clp.cid = cll.cid) AND (clp.cached_lease_log_id = cll.id))
13. 138.942 415.463 ↑ 1.7 84,360 1

Hash Join (cost=73,904.74..361,980.81 rows=145,032 width=31) (actual time=98.409..415.463 rows=84,360 loops=1)

  • Hash Cond: (clp.property_id = load_prop.property_id)
14. 190.016 276.336 ↑ 1.7 84,360 1

Bitmap Heap Scan on cached_lease_periods clp (cost=73,896.47..359,978.35 rows=145,032 width=34) (actual time=98.205..276.336 rows=84,360 loops=1)

  • Recheck Cond: (post_month = '2020-05-01'::date)
  • Filter: ((cid = 13531) AND (lease_status_type_id = ANY ('{3,4,5}'::integer[])))
  • Rows Removed by Filter: 96583
  • Heap Blocks: exact=64440
15. 86.320 86.320 ↓ 1.1 217,595 1

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

  • Index Cond: (post_month = '2020-05-01'::date)
16. 0.088 0.185 ↑ 1.0 190 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
17. 0.097 0.097 ↑ 1.0 190 1

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

18. 3,584.841 7,968.090 ↓ 1.0 4,665,400 1

Hash (cost=224,476.66..224,476.66 rows=4,665,093 width=75) (actual time=7,968.090..7,968.090 rows=4,665,400 loops=1)

  • Buckets: 8388608 Batches: 2 Memory Usage: 299684kB
19. 4,383.249 4,383.249 ↓ 1.0 4,665,400 1

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

  • Filter: (cid = 13531)
20. 0.009 0.021 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.012 0.012 ↑ 1.0 10 1

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

22. 337.440 337.440 ↑ 1.0 1 84,360

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

  • Index Cond: (id = cll.active_lease_interval_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid) AND (lease_id = cll.lease_id))
23. 0.924 1.965 ↑ 1.0 1,799 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
24. 1.041 1.041 ↑ 1.0 1,799 1

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

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 269
25. 224.920 450.925 ↓ 1.0 420,946 1

Hash (cost=9,431.30..9,431.30 rows=420,602 width=16) (actual time=450.924..450.925 rows=420,946 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 23076kB
26. 226.005 226.005 ↓ 1.0 420,946 1

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

  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (cid = 13531))
  • Rows Removed by Filter: 358
27. 0.184 0.409 ↑ 1.0 311 1

Hash (cost=15.23..15.23 rows=311 width=153) (actual time=0.408..0.409 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
28. 0.225 0.225 ↑ 1.0 311 1

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

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 107
29. 338.980 338.980 ↑ 1.0 1 84,745

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

  • Index Cond: (id = cll.primary_customer_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid))
30. 432.185 799.360 ↑ 1.0 686,255 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 35408kB
31. 367.175 367.175 ↑ 1.0 686,255 1

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

  • Filter: (cid = 13531)
32. 59.135 59.135 ↑ 16.8 24,960 1

Index Scan using idx_cached_leases_transfer_lease_id on cached_leases cl_prior (cost=0.42..70,006.10 rows=420,280 width=16) (actual time=0.024..59.135 rows=24,960 loops=1)

  • Filter: (cid = 13531)