explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wYaP

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 152.927 ↓ 5.0 5 1

Subquery Scan on inner_query (cost=4,925.38..4,945.21 rows=1 width=28) (actual time=145.441..152.927 rows=5 loops=1)

  • Filter: (inner_query.row_num = 1)
2. 0.030 142.755 ↓ 5.0 5 1

WindowAgg (cost=4,925.38..4,925.41 rows=1 width=28) (actual time=142.735..142.755 rows=5 loops=1)

3. 0.019 142.725 ↓ 5.0 5 1

Sort (cost=4,925.38..4,925.39 rows=1 width=16) (actual time=142.723..142.725 rows=5 loops=1)

  • Sort Key: e.lease_id, e.event_datetime DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.076 142.706 ↓ 5.0 5 1

Nested Loop (cost=0.56..4,925.37 rows=1 width=16) (actual time=40.395..142.706 rows=5 loops=1)

5. 0.030 0.030 ↑ 1.0 23 1

Seq Scan on load_prop (cost=0.00..4.23 rows=23 width=4) (actual time=0.004..0.030 rows=23 loops=1)

6. 142.600 142.600 ↓ 0.0 0 23

Index Scan using idx_events_cid_etid_pid_event_datetime on events e (cost=0.56..213.95 rows=1 width=20) (actual time=6.174..6.200 rows=0 loops=23)

  • Index Cond: ((cid = 8,132) AND (event_type_id = 21) AND (property_id = load_prop.property_id))
  • Filter: ((lease_id IS NOT NULL) AND (old_status_id = ANY ('{6,5}'::integer[])) AND (new_status_id = 4) AND ((created_on)::date >= '2020-09-01'::date) AND ((created_on)::date <= '2020-09-30'::date))
  • Rows Removed by Filter: 1,751
7.          

SubPlan (for Subquery Scan)

8. 0.020 10.150 ↑ 1.0 1 5

Aggregate (cost=19.78..19.79 rows=1 width=8) (actual time=2.030..2.030 rows=1 loops=5)

9. 0.045 10.130 ↓ 2.0 2 5

Nested Loop (cost=0.56..19.77 rows=1 width=8) (actual time=1.261..2.026 rows=2 loops=5)

  • Join Filter: (e_1.property_id = load_prop_1.property_id)
  • Rows Removed by Join Filter: 40
10. 10.040 10.040 ↓ 2.0 2 5

Index Scan using idx_events_cid_lease_id on events e_1 (cost=0.56..15.26 rows=1 width=12) (actual time=1.254..2.008 rows=2 loops=5)

  • Index Cond: ((cid = inner_query.cid) AND (lease_id = inner_query.lease_id))
  • Filter: ((event_datetime < inner_query.notice_cancelled_date) AND CASE WHEN ((old_status_id = ANY ('{4,6}'::integer[])) AND (new_status_id = 5)) THEN true ELSE NULL::boolean END)
  • Rows Removed by Filter: 565
11. 0.045 0.045 ↑ 1.0 23 9

Seq Scan on load_prop load_prop_1 (cost=0.00..4.23 rows=23 width=4) (actual time=0.002..0.005 rows=23 loops=9)

Planning time : 0.919 ms