explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D2bD

Settings
# exclusive inclusive rows x rows loops node
1. 6,638.059 19,771.749 ↑ 1.9 84,745 1

WindowAgg (cost=990,902.78..1,088,433.82 rows=157,945 width=1,253) (actual time=12,881.790..19,771.749 rows=84,745 loops=1)

2. 194.270 13,133.690 ↑ 1.9 84,745 1

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

3. 635.575 12,939.420 ↑ 1.9 84,745 1

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

  • Sort Key: clp.lease_id, lus.slot_number
  • Sort Method: quicksort Memory: 46539kB
4. 147.847 12,303.845 ↑ 1.9 84,745 1

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

  • Hash Cond: ((cll.cid = cd.cid) AND (cll.primary_customer_id = cd.customer_id))
5. 150.005 11,354.231 ↑ 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,754.705..11,354.231 rows=84,745 loops=1)

6. 96.258 10,780.501 ↑ 1.9 84,745 1

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

  • Hash Cond: ((li.cid = lt.cid) AND (li.lease_term_id = lt.id))
7. 141.797 10,683.834 ↑ 1.7 84,745 1

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

  • Hash Cond: (clp.lease_id = lus.lease_id)
8. 103.403 10,044.484 ↑ 1.7 84,360 1

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

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

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

10. 89.254 9,391.782 ↑ 1.7 84,360 1

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

  • Hash Cond: (cll.occupancy_type_id = ot_lease.id)
11. 725.044 9,302.506 ↑ 1.7 84,360 1

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

  • Hash Cond: ((clp.cid = cll.cid) AND (clp.cached_lease_log_id = cll.id))
12. 146.222 447.129 ↑ 1.7 84,360 1

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

  • Hash Cond: (clp.property_id = load_prop.property_id)
13. 202.665 300.712 ↑ 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=111.875..300.712 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
14. 98.047 98.047 ↓ 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=98.047..98.047 rows=217,595 loops=1)

  • Index Cond: (post_month = '2020-05-01'::date)
15. 0.093 0.195 ↑ 1.0 190 1

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

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

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

17. 3,672.445 8,130.333 ↓ 1.0 4,665,400 1

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

  • Buckets: 8388608 Batches: 2 Memory Usage: 299684kB
18. 4,457.888 4,457.888 ↓ 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.016..4,457.888 rows=4,665,400 loops=1)

  • Filter: (cid = 13531)
19. 0.009 0.022 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.013 0.013 ↑ 1.0 10 1

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

21. 421.800 421.800 ↑ 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.005..0.005 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))
22. 0.944 2.019 ↑ 1.0 1,799 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
23. 1.075 1.075 ↑ 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.075 rows=1,799 loops=1)

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 269
24. 253.981 497.553 ↓ 1.0 420,946 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 23076kB
25. 243.572 243.572 ↓ 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.013..243.572 rows=420,946 loops=1)

  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (cid = 13531))
  • Rows Removed by Filter: 358
26. 0.177 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
27. 0.232 0.232 ↑ 1.0 311 1

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

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 107
28. 423.725 423.725 ↑ 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.005..0.005 rows=1 loops=84,745)

  • Index Cond: (id = cll.primary_customer_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid))
29. 426.187 801.767 ↑ 1.0 686,255 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 35408kB
30. 375.580 375.580 ↑ 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.009..375.580 rows=686,255 loops=1)

  • Filter: (cid = 13531)