explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BNxy

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 167,826.050 ↑ 20.0 10 1

Sort (cost=73,204.20..73,204.70 rows=200 width=168) (actual time=167,826.049..167,826.050 rows=10 loops=1)

  • Sort Key: lid.period_type, lid.start_date
  • Sort Method: quicksort Memory: 26kB
2.          

CTE load_prop

3. 0.004 182.717 ↑ 1.0 1 1

Nested Loop (cost=0.53..3.51 rows=1 width=66) (actual time=182.716..182.717 rows=1 loops=1)

4. 182.709 182.709 ↑ 1.0 1 1

Function Scan on load_properties lp_set (cost=0.25..1.00 rows=1 width=12) (actual time=182.709..182.709 rows=1 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
5. 0.004 0.004 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.28..2.50 rows=1 width=62) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = lp_set.property_id)
  • Filter: (lp_set.cid = cid)
6.          

CTE date_intervals

7. 0.022 0.239 ↑ 100.4 10 1

Append (cost=0.00..32.73 rows=1,004 width=80) (actual time=0.091..0.239 rows=10 loops=1)

8. 0.003 0.093 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.06 rows=1 width=80) (actual time=0.091..0.093 rows=1 loops=1)

9. 0.090 0.090 ↑ 1.0 1 1

Result (cost=0.00..0.04 rows=1 width=76) (actual time=0.090..0.090 rows=1 loops=1)

10. 0.019 0.019 ↑ 1.0 1 1

Result (cost=0.00..0.05 rows=1 width=80) (actual time=0.019..0.019 rows=1 loops=1)

11. 0.003 0.011 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.04 rows=1 width=80) (actual time=0.009..0.011 rows=1 loops=1)

12. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=76) (actual time=0.008..0.008 rows=1 loops=1)

13. 0.002 0.010 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0.00..0.04 rows=1 width=80) (actual time=0.009..0.010 rows=1 loops=1)

14. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=76) (actual time=0.008..0.008 rows=1 loops=1)

15. 0.084 0.084 ↑ 166.7 6 1

Function Scan on generate_series trail_five_months (cost=0.02..17.52 rows=1,000 width=80) (actual time=0.031..0.084 rows=6 loops=1)

16.          

CTE leasing_intelligence_dashboard_data

17. 0.004 167,825.979 ↑ 60.2 20 1

Append (cost=73,051.49..73,115.67 rows=1,204 width=152) (actual time=167,824.560..167,825.979 rows=20 loops=1)

18. 1.308 167,825.969 ↑ 20.0 10 1

GroupAggregate (cost=73,051.49..73,072.51 rows=200 width=152) (actual time=167,824.559..167,825.969 rows=10 loops=1)

  • Group Key: di.column_name, di.period_type, di.start_date, di.end_date
19. 3.241 167,824.661 ↓ 4.0 4,938 1

Sort (cost=73,051.49..73,054.58 rows=1,235 width=84) (actual time=167,824.416..167,824.661 rows=4,938 loops=1)

  • Sort Key: di.column_name, di.period_type, di.start_date, di.end_date
  • Sort Method: quicksort Memory: 703kB
20. 2.093 167,821.420 ↓ 4.0 4,938 1

Nested Loop (cost=75.98..72,988.07 rows=1,235 width=84) (actual time=15,630.301..167,821.420 rows=4,938 loops=1)

21. 2,644.818 166,787.285 ↓ 4.0 4,938 1

Nested Loop (cost=75.42..31,528.67 rows=1,235 width=88) (actual time=15,628.103..166,787.285 rows=4,938 loops=1)

  • Join Filter: (((di.end_date)::date >= cal.reporting_post_date) AND ((di.end_date)::date <= cal.apply_through_post_date) AND (((di.start_date)::date - '1 day'::interval) >= cal_prior.reporting_post_date) AND (((di.start_date)::date - '1 day'::interval) <= cal_prior.apply_through_post_date))
  • Rows Removed by Join Filter: 18149012
22. 0.257 0.257 ↑ 100.4 10 1

CTE Scan on date_intervals di (cost=0.00..20.08 rows=1,004 width=80) (actual time=0.093..0.257 rows=10 loops=1)

23. 1,194.847 164,142.210 ↓ 18,154.0 1,815,395 10

Materialize (cost=75.42..27,743.84 rows=100 width=24) (actual time=19.040..16,414.221 rows=1,815,395 loops=10)

24. 342.675 162,947.363 ↓ 18,154.0 1,815,395 1

Nested Loop (cost=75.42..27,743.34 rows=100 width=24) (actual time=190.389..162,947.363 rows=1,815,395 loops=1)

  • Join Filter: (load_prop.property_id = cal_prior.property_id)
25. 13.621 15,280.032 ↓ 43.1 73,552 1

Nested Loop (cost=74.85..9,872.09 rows=1,708 width=28) (actual time=186.805..15,280.032 rows=73,552 loops=1)

26. 182.718 182.718 ↑ 1.0 1 1

CTE Scan on load_prop (cost=0.00..0.02 rows=1 width=4) (actual time=182.717..182.718 rows=1 loops=1)

27. 15,068.054 15,083.693 ↓ 43.1 73,552 1

Index Scan using idx_cached_application_logs_temp on cached_application_logs cal (cost=74.85..9,854.99 rows=1,708 width=24) (actual time=4.085..15,083.693 rows=73,552 loops=1)

  • Index Cond: ((cid = 224) AND (property_id = load_prop.property_id))
  • Filter: ((NOT is_post_date_ignored) AND CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 3) THEN false ELSE true END AND (occupancy_type_id <> 4) AND (lease_interval_type_id = 1))
  • Rows Removed by Filter: 197823
28.          

SubPlan (forIndex Scan)

29. 15.639 15.639 ↓ 1.5 3 1

Index Scan using idx_list_items_cid_list_type_id on list_items li (cost=0.28..74.29 rows=2 width=4) (actual time=6.711..15.639 rows=3 loops=1)

  • Index Cond: (cid = 224)
  • Filter: (default_list_item_id = ANY ('{65,66}'::integer[]))
  • Rows Removed by Filter: 97
30. 147,324.656 147,324.656 ↓ 25.0 25 73,552

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_prior (cost=0.56..10.45 rows=1 width=24) (actual time=0.266..2.003 rows=25 loops=73,552)

  • Index Cond: ((cid = 224) AND (application_id = cal.application_id))
  • Filter: ((NOT is_post_date_ignored) AND (id <> cal.id) AND (cal.property_id = property_id))
  • Rows Removed by Filter: 144
31. 49.380 1,032.042 ↑ 1.0 1 4,938

GroupAggregate (cost=0.56..33.55 rows=1 width=48) (actual time=0.209..0.209 rows=1 loops=4,938)

  • Group Key: cal_inner.cid, cal_inner.property_id, cal_inner.application_id
32. 222.210 982.662 ↓ 110.0 110 4,938

Nested Loop (cost=0.56..33.53 rows=1 width=12) (actual time=0.008..0.199 rows=110 loops=4,938)

33. 760.452 760.452 ↓ 110.0 110 4,938

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_inner (cost=0.56..33.50 rows=1 width=12) (actual time=0.007..0.154 rows=110 loops=4,938)

  • Index Cond: ((cid = 224) AND (application_id = cal.application_id))
  • Filter: ((property_id = cal.property_id) AND (post_date <= (di.end_date)::date))
  • Rows Removed by Filter: 13
34. 0.000 0.000 ↑ 1.0 1 541,981

CTE Scan on load_prop load_prop_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=541,981)

  • Filter: (property_id = cal.property_id)
35. 0.002 0.006 ↑ 100.4 10 1

Subquery Scan on *SELECT* 2 (cost=0.00..35.14 rows=1,004 width=152) (actual time=0.003..0.006 rows=10 loops=1)

36. 0.004 0.004 ↑ 100.4 10 1

CTE Scan on date_intervals di_1 (cost=0.00..22.59 rows=1,004 width=148) (actual time=0.002..0.004 rows=10 loops=1)

37. 0.024 167,826.013 ↑ 20.0 10 1

HashAggregate (cost=42.14..44.64 rows=200 width=168) (actual time=167,826.009..167,826.013 rows=10 loops=1)

  • Group Key: lid.period_type, lid.start_date, lid.dummy_column, lid.column_key, lid.column_name
38. 167,825.989 167,825.989 ↑ 60.2 20 1

CTE Scan on leasing_intelligence_dashboard_data lid (cost=0.00..24.08 rows=1,204 width=144) (actual time=167,824.562..167,825.989 rows=20 loops=1)

Planning time : 418.812 ms
Execution time : 167,851.890 ms