explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pXJ2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=187.58..188.08 rows=200 width=168) (actual rows= loops=)

  • Sort Key: lid.period_type, lid.start_date
2.          

CTE load_prop

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.53..3.51 rows=1 width=63) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
5. 0.000 0.000 ↓ 0.0

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

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

CTE date_intervals

7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

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

9. 0.000 0.000 ↓ 0.0

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

10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

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

12. 0.000 0.000 ↓ 0.0

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

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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

16.          

CTE leasing_intelligence_dashboard_data

17. 0.000 0.000 ↓ 0.0

Append (cost=65.81..106.01 rows=1,005 width=152) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=65.81..65.84 rows=1 width=152) (actual rows= loops=)

  • Group Key: di.column_name, di.period_type, di.start_date, di.end_date
19. 0.000 0.000 ↓ 0.0

Sort (cost=65.81..65.81 rows=1 width=84) (actual rows= loops=)

  • Sort Key: di.column_name, di.period_type, di.start_date, di.end_date
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.79..65.80 rows=1 width=84) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.36..63.07 rows=1 width=88) (actual rows= loops=)

  • 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))
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.36..7.85 rows=1 width=24) (actual rows= loops=)

  • Join Filter: ((cal_prior.id <> cal.id) AND (cal.application_id = cal_prior.application_id))
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.93..5.19 rows=1 width=28) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_application_logs_temp on cached_application_logs cal (cost=2.93..5.16 rows=1 width=24) (actual rows= loops=)

  • 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))
26.          

SubPlan (forIndex Scan)

27. 0.000 0.000 ↓ 0.0

Index Scan using idx_list_items_cid_list_type_id on list_items li (cost=0.28..2.50 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (cid = 224)
  • Filter: (default_list_item_id = ANY ('{65,66}'::integer[]))
28. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_application_logs_temp on cached_application_logs cal_prior (cost=0.43..2.65 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((cid = 224) AND (property_id = load_prop.property_id))
  • Filter: (NOT is_post_date_ignored)
29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.43..2.70 rows=1 width=48) (actual rows= loops=)

  • Group Key: cal_inner.cid, cal_inner.property_id, cal_inner.application_id
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..2.69 rows=1 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_application_logs_cid_app_opening_log on cached_application_logs cal_inner (cost=0.43..2.66 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((cid = 224) AND (application_id = cal.application_id))
  • Filter: ((property_id = cal.property_id) AND (post_date <= (di.end_date)::date))
33. 0.000 0.000 ↓ 0.0

CTE Scan on load_prop load_prop_1 (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

  • Filter: (property_id = cal.property_id)
34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

36. 0.000 0.000 ↓ 0.0

HashAggregate (cost=35.17..37.67 rows=200 width=168) (actual rows= loops=)

  • Group Key: lid.period_type, lid.start_date, lid.dummy_column, lid.column_key, lid.column_name
37. 0.000 0.000 ↓ 0.0

CTE Scan on leasing_intelligence_dashboard_data lid (cost=0.00..20.10 rows=1,005 width=144) (actual rows= loops=)