explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y1jL

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 2,012.019 ↑ 63.3 23 1

Hash Join (cost=93,365.88..104,818.11 rows=1,457 width=24) (actual time=1,655.613..2,012.019 rows=23 loops=1)

  • Hash Cond: (li.property_id = load_prop.property_id)
2. 93.998 2,011.767 ↑ 63.3 23 1

Merge Left Join (cost=93,353.93..104,786.13 rows=1,457 width=28) (actual time=1,655.391..2,011.767 rows=23 loops=1)

  • Merge Cond: (li.lease_id = old_lease_interval.lease_id)
  • Join Filter: (li.cid = old_lease_interval.cid)
3. 0.036 8.918 ↑ 63.3 23 1

Sort (cost=8,983.53..8,987.17 rows=1,457 width=20) (actual time=8.907..8.918 rows=23 loops=1)

  • Sort Key: li.lease_id
  • Sort Method: quicksort Memory: 26kB
4. 8.882 8.882 ↑ 63.3 23 1

Index Scan using idx_lease_intervals_start_date on lease_intervals li (cost=0.43..8,906.97 rows=1,457 width=20) (actual time=2.341..8.882 rows=23 loops=1)

  • Index Cond: ((lease_start_date >= '2020-09-01'::date) AND (lease_start_date < '2020-10-01 00:00:00'::timestamp without time zone))
  • Filter: ((lease_status_type_id <> 2) AND (cid = 13,531) AND (lease_interval_type_id = 3))
  • Rows Removed by Filter: 8,538
5. 187.792 1,908.851 ↓ 150.7 229,382 1

Materialize (cost=84,370.41..95,791.39 rows=1,522 width=16) (actual time=1,012.465..1,908.851 rows=229,382 loops=1)

6. 203.745 1,721.059 ↓ 150.7 229,382 1

Subquery Scan on old_lease_interval (cost=84,370.41..95,787.58 rows=1,522 width=16) (actual time=1,012.461..1,721.059 rows=229,382 loops=1)

  • Filter: ((old_lease_interval.row_number = 1) AND (old_lease_interval.cid = 13,531))
  • Rows Removed by Filter: 81,111
7. 359.010 1,517.314 ↓ 1.0 310,493 1

WindowAgg (cost=84,370.41..91,220.71 rows=304,458 width=32) (actual time=1,012.459..1,517.314 rows=310,493 loops=1)

8. 451.024 1,158.304 ↓ 1.0 310,493 1

Sort (cost=84,370.41..85,131.55 rows=304,458 width=20) (actual time=1,012.449..1,158.304 rows=310,493 loops=1)

  • Sort Key: li_1.lease_id, li_1.lease_start_date DESC, li_1.id DESC
  • Sort Method: quicksort Memory: 38,857kB
9. 306.683 707.280 ↓ 1.2 359,729 1

Hash Join (cost=11.95..56,640.55 rows=304,458 width=20) (actual time=0.222..707.280 rows=359,729 loops=1)

  • Hash Cond: (li_1.property_id = load_prop_1.property_id)
10. 400.390 400.390 ↓ 1.2 361,139 1

Seq Scan on lease_intervals li_1 (cost=0.00..52,442.30 rows=304,458 width=24) (actual time=0.008..400.390 rows=361,139 loops=1)

  • Filter: ((lease_status_type_id <> 2) AND (lease_start_date < '2020-09-30'::date) AND (lease_end_date < '2020-09-30'::date) AND (cid = 13,531))
  • Rows Removed by Filter: 1,087,276
11. 0.101 0.207 ↑ 1.0 220 1

Hash (cost=9.20..9.20 rows=220 width=4) (actual time=0.206..0.207 rows=220 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
12. 0.106 0.106 ↑ 1.0 220 1

Seq Scan on load_prop load_prop_1 (cost=0.00..9.20 rows=220 width=4) (actual time=0.004..0.106 rows=220 loops=1)

13. 0.103 0.214 ↑ 1.0 220 1

Hash (cost=9.20..9.20 rows=220 width=4) (actual time=0.213..0.214 rows=220 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
14. 0.111 0.111 ↑ 1.0 220 1

Seq Scan on load_prop (cost=0.00..9.20 rows=220 width=4) (actual time=0.006..0.111 rows=220 loops=1)

Planning time : 0.803 ms