explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CGne

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 1,993.759 ↑ 63.3 23 1

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

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

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

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

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

  • Sort Key: li.lease_id
  • Sort Method: quicksort Memory: 26kB
4. 6.674 6.674 ↑ 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=0.510..6.674 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. 186.500 1,893.549 ↓ 150.7 229,382 1

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

6. 202.374 1,707.049 ↓ 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,001.992..1,707.049 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. 357.720 1,504.675 ↓ 1.0 310,493 1

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

8. 448.639 1,146.955 ↓ 1.0 310,493 1

Sort (cost=84,370.41..85,131.55 rows=304,458 width=20) (actual time=1,001.981..1,146.955 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. 305.052 698.316 ↓ 1.2 359,729 1

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

  • Hash Cond: (li_1.property_id = load_prop_1.property_id)
10. 393.060 393.060 ↓ 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.006..393.060 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.100 0.204 ↑ 1.0 220 1

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

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

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

13. 0.101 0.211 ↑ 1.0 220 1

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

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

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

Planning time : 9.005 ms