explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OUoX

Settings
# exclusive inclusive rows x rows loops node
1. 95.065 168.871 ↑ 1.7 1,636 1

WindowAgg (cost=27,566.72..29,248.17 rows=2,723 width=1,253) (actual time=71.139..168.871 rows=1,636 loops=1)

2. 3.178 73.806 ↑ 1.7 1,636 1

WindowAgg (cost=27,566.72..27,627.99 rows=2,723 width=1,359) (actual time=69.793..73.806 rows=1,636 loops=1)

3. 3.119 70.628 ↑ 1.7 1,636 1

Sort (cost=27,566.72..27,573.53 rows=2,723 width=1,351) (actual time=69.780..70.628 rows=1,636 loops=1)

  • Sort Key: clp.lease_id, lus.slot_number
  • Sort Method: quicksort Memory: 892kB
4. 2.942 67.509 ↑ 1.7 1,636 1

Nested Loop Left Join (cost=148.88..27,411.36 rows=2,723 width=1,351) (actual time=3.135..67.509 rows=1,636 loops=1)

5. 2.662 56.387 ↑ 1.7 1,636 1

Nested Loop Left Join (cost=148.45..26,144.42 rows=2,723 width=1,346) (actual time=3.118..56.387 rows=1,636 loops=1)

6. 1.912 45.545 ↑ 1.7 1,636 1

Hash Left Join (cost=148.02..24,819.12 rows=2,723 width=276) (actual time=3.096..45.545 rows=1,636 loops=1)

  • Hash Cond: ((li.cid = lt.cid) AND (li.lease_term_id = lt.id))
7. 2.877 43.214 ↑ 1.5 1,636 1

Nested Loop (cost=128.13..24,785.93 rows=2,512 width=135) (actual time=2.661..43.214 rows=1,636 loops=1)

8. 1.879 32.157 ↑ 1.5 1,636 1

Hash Left Join (cost=127.71..23,286.99 rows=2,501 width=127) (actual time=2.640..32.157 rows=1,636 loops=1)

  • Hash Cond: ((li.cid = lsw.cid) AND (li.lease_start_window_id = lsw.id))
9. 2.920 28.242 ↑ 1.5 1,636 1

Nested Loop Left Join (cost=39.88..23,186.01 rows=2,501 width=123) (actual time=0.588..28.242 rows=1,636 loops=1)

10. 1.740 18.778 ↑ 1.5 1,636 1

Hash Left Join (cost=39.45..21,975.86 rows=2,501 width=107) (actual time=0.566..18.778 rows=1,636 loops=1)

  • Hash Cond: (cll.occupancy_type_id = ot_lease.id)
11. 3.397 17.020 ↑ 1.5 1,636 1

Nested Loop Left Join (cost=38.22..21,965.29 rows=2,501 width=98) (actual time=0.542..17.020 rows=1,636 loops=1)

12. 2.506 7.079 ↑ 1.5 1,636 1

Nested Loop (cost=37.79..12,980.20 rows=2,501 width=31) (actual time=0.510..7.079 rows=1,636 loops=1)

13. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on load_prop (cost=0.00..1.02 rows=2 width=5) (actual time=0.005..0.009 rows=2 loops=1)

14. 4.164 4.564 ↑ 1.5 818 2

Bitmap Heap Scan on cached_lease_periods clp (cost=37.79..6,477.09 rows=1,250 width=34) (actual time=0.308..2.282 rows=818 loops=2)

  • Recheck Cond: ((property_id = load_prop.property_id) AND (post_month = '2020-05-01'::date))
  • Filter: ((cid = 13531) AND (lease_status_type_id = ANY ('{3,4,5}'::integer[])))
  • Rows Removed by Filter: 798
  • Heap Blocks: exact=1732
15. 0.400 0.400 ↑ 1.1 1,616 2

Bitmap Index Scan on idx_cached_lease_periods_prop_post_month (cost=0.00..37.48 rows=1,705 width=0) (actual time=0.200..0.200 rows=1,616 loops=2)

  • Index Cond: ((property_id = load_prop.property_id) AND (post_month = '2020-05-01'::date))
16. 6.544 6.544 ↑ 1.0 1 1,636

Index Scan using pk_cached_lease_logs on cached_lease_logs cll (cost=0.43..3.59 rows=1 width=75) (actual time=0.004..0.004 rows=1 loops=1,636)

  • Index Cond: ((cid = clp.cid) AND (cid = 13531) AND (id = clp.cached_lease_log_id))
17. 0.008 0.018 ↑ 1.0 10 1

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

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

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

19. 6.544 6.544 ↑ 1.0 1 1,636

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=1,636)

  • Index Cond: (id = cll.active_lease_interval_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid) AND (lease_id = cll.lease_id))
20. 0.951 2.036 ↑ 1.0 1,799 1

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

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

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 269
22. 8.180 8.180 ↑ 1.0 1 1,636

Index Scan using idx_lease_unit_spaces_lease_id on lease_unit_spaces lus (cost=0.42..0.59 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1,636)

  • Index Cond: (lease_id = clp.lease_id)
  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (cid = 13531))
23. 0.190 0.419 ↑ 1.0 311 1

Hash (cost=15.23..15.23 rows=311 width=153) (actual time=0.418..0.419 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
24. 0.229 0.229 ↑ 1.0 311 1

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

  • Filter: (cid = 13531)
  • Rows Removed by Filter: 107
25. 8.180 8.180 ↑ 1.0 1 1,636

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=1,636)

  • Index Cond: (id = cll.primary_customer_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid))
26. 8.180 8.180 ↑ 1.0 1 1,636

Index Scan using idx_customer_details_customer_id on customer_details cd (cost=0.42..0.46 rows=1 width=17) (actual time=0.004..0.005 rows=1 loops=1,636)

  • Index Cond: (customer_id = cll.primary_customer_id)
  • Filter: ((cid = 13531) AND (cid = cll.cid))