explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S0cI

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 223.968 ↑ 1.0 1 1

Aggregate (cost=79.15..79.16 rows=1 width=8) (actual time=223.967..223.968 rows=1 loops=1)

2. 0.008 223.958 ↓ 3.0 3 1

Subquery Scan on a (cost=58.56..79.15 rows=1 width=0) (actual time=117.223..223.958 rows=3 loops=1)

  • Filter: (a.priority = ANY ('{1,2,3}'::integer[]))
3. 0.014 223.950 ↓ 3.0 3 1

Unique (cost=58.56..79.13 rows=1 width=2,559) (actual time=117.220..223.950 rows=3 loops=1)

4. 0.062 223.936 ↓ 13.0 13 1

Nested Loop Left Join (cost=58.56..79.13 rows=1 width=2,559) (actual time=117.218..223.936 rows=13 loops=1)

5. 0.029 223.185 ↓ 13.0 13 1

Nested Loop Left Join (cost=54.70..75.07 rows=1 width=121) (actual time=117.082..223.185 rows=13 loops=1)

6. 0.036 220.842 ↓ 13.0 13 1

Nested Loop Left Join (cost=52.04..72.39 rows=1 width=121) (actual time=115.253..220.842 rows=13 loops=1)

  • Filter: CASE WHEN ((e_1.id IS NULL) OR (ei_1.scheduled_datetime <> e_1.scheduled_datetime)) THEN (ass.id = ANY ('{25,26,31,32,43,44,27,28}'::integer[])) ELSE ((ca.application_stage_id = 4) AND (ca.lease_status_type_id = ANY ('{4,5}'::integer[]))) END
7. 0.032 176.125 ↓ 13.0 13 1

Nested Loop Left Join (cost=29.02..49.33 rows=1 width=149) (actual time=95.740..176.125 rows=13 loops=1)

8. 0.027 173.064 ↓ 13.0 13 1

Nested Loop Left Join (cost=23.81..44.10 rows=1 width=141) (actual time=94.368..173.064 rows=13 loops=1)

9. 0.029 172.543 ↓ 13.0 13 1

Nested Loop Left Join (cost=23.39..43.44 rows=1 width=145) (actual time=93.927..172.543 rows=13 loops=1)

10. 0.053 172.423 ↓ 13.0 13 1

Nested Loop Left Join (cost=22.97..42.78 rows=1 width=122) (actual time=93.912..172.423 rows=13 loops=1)

11. 0.039 171.759 ↓ 13.0 13 1

Nested Loop Left Join (cost=22.83..42.61 rows=1 width=60) (actual time=93.341..171.759 rows=13 loops=1)

12. 0.024 170.095 ↓ 13.0 13 1

Nested Loop Left Join (cost=22.54..42.27 rows=1 width=68) (actual time=93.336..170.095 rows=13 loops=1)

13. 0.012 127.192 ↓ 3.0 3 1

Nested Loop Left Join (cost=22.11..35.12 rows=1 width=64) (actual time=85.899..127.192 rows=3 loops=1)

14. 0.010 127.177 ↓ 3.0 3 1

Nested Loop Left Join (cost=21.82..34.80 rows=1 width=68) (actual time=85.894..127.177 rows=3 loops=1)

15. 0.012 122.790 ↓ 3.0 3 1

Nested Loop Left Join (cost=21.40..33.73 rows=1 width=72) (actual time=84.394..122.790 rows=3 loops=1)

16. 0.012 122.190 ↓ 3.0 3 1

Nested Loop (cost=21.26..33.26 rows=1 width=76) (actual time=83.871..122.190 rows=3 loops=1)

17. 0.286 119.010 ↓ 3.0 3 1

Nested Loop (cost=20.84..32.54 rows=1 width=76) (actual time=82.302..119.010 rows=3 loops=1)

  • Join Filter: CASE WHEN (e_1.id IS NULL) THEN (((ca.application_stage_id = 4) OR ((ca.application_stage_id = 3) AND (ca.lease_interval_type_id = 3))) AND (ca.lease_interval_type_id = ANY ('{3,4}'::integer[])) AND ((ca.application_stage_id <> 3) OR (ca.application_status_id <> 6)) AND ((ca.application_stage_id <> 4) OR (ca.application_status_id <> 6)) AND ((ca.application_stage_id <> 4) OR (ca.application_status_id <> 3)) AND ((ca.application_stage_id <> 4) OR (ca.application_status_id <> 4))) ELSE (ca.application_stage_id = 4) END
  • Rows Removed by Join Filter: 127
18. 0.186 50.734 ↓ 130.0 130 1

Nested Loop (cost=20.41..29.85 rows=1 width=32) (actual time=38.889..50.734 rows=130 loops=1)

19. 0.324 50.158 ↓ 130.0 130 1

Nested Loop Left Join (cost=20.13..27.04 rows=1 width=32) (actual time=38.882..50.158 rows=130 loops=1)

20. 0.120 37.874 ↓ 130.0 130 1

Subquery Scan on npe (cost=18.85..19.04 rows=1 width=20) (actual time=37.511..37.874 rows=130 loops=1)

  • Filter: (npe.cid = 11400)
21. 0.171 37.754 ↓ 11.8 130 1

Unique (cost=18.85..18.90 rows=11 width=1,451) (actual time=37.510..37.754 rows=130 loops=1)

22. 0.218 37.583 ↓ 11.8 130 1

Sort (cost=18.85..18.88 rows=11 width=1,451) (actual time=37.508..37.583 rows=130 loops=1)

  • Sort Key: e.lease_interval_id, e.scheduled_datetime DESC
  • Sort Method: quicksort Memory: 35kB
23. 0.235 37.365 ↓ 11.8 130 1

Nested Loop Anti Join (cost=0.96..18.66 rows=11 width=1,451) (actual time=33.640..37.365 rows=130 loops=1)

24. 0.127 35.458 ↓ 13.8 152 1

Nested Loop (cost=0.68..14.85 rows=11 width=32) (actual time=33.633..35.458 rows=152 loops=1)

25. 32.506 32.506 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=4) (actual time=32.505..32.506 rows=1 loops=1)

  • Filter: (is_disabled = 0)
26. 2.825 2.825 ↓ 13.8 152 1

Index Scan using idx_events_cid_etid_pid_event_datetime on events e (cost=0.43..13.87 rows=11 width=32) (actual time=1.126..2.825 rows=152 loops=1)

  • Index Cond: ((cid = 11400) AND (event_type_id = 275) AND (property_id = lp.property_id))
  • Filter: (NOT is_deleted)
27. 1.672 1.672 ↓ 0.0 0 152

Index Scan using idx_event_results on event_results er (cost=0.28..0.34 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=152)

  • Index Cond: (id = e.event_result_id)
  • Filter: (is_system AND (cid = 11400) AND (cid = e.cid) AND (2 = ANY (default_event_result_ids)))
  • Rows Removed by Filter: 1
28. 0.130 11.960 ↓ 0.0 0 130

Nested Loop (cost=1.28..7.98 rows=1 width=24) (actual time=0.092..0.092 rows=0 loops=130)

  • Join Filter: (e_1.data_reference_id = fa.file_id)
29. 0.130 11.830 ↓ 0.0 0 130

Nested Loop Left Join (cost=0.85..5.31 rows=1 width=24) (actual time=0.091..0.091 rows=0 loops=130)

  • Join Filter: (e_1.data_reference_id = ev.data_reference_id)
  • Filter: (ev.id IS NULL)
30. 11.700 11.700 ↓ 0.0 0 130

Index Scan using idx_events_lease_id on events e_1 (cost=0.43..2.65 rows=1 width=24) (actual time=0.090..0.090 rows=0 loops=130)

  • Index Cond: ((cid = npe.cid) AND (lease_id = npe.lease_id))
  • Filter: ((event_type_id = 309) AND (npe.property_id = property_id))
  • Rows Removed by Filter: 5
31. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_events_lease_id on events ev (cost=0.43..2.65 rows=1 width=8) (never executed)

  • Index Cond: ((cid = npe.cid) AND (lease_id = npe.lease_id))
  • Filter: (event_type_id = 310)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_file_associations_cid_customer_id on file_associations fa (cost=0.43..2.65 rows=1 width=12) (never executed)

  • Index Cond: ((cid = npe.cid) AND (customer_id = npe.customer_id))
  • Filter: ((file_signed_on IS NULL) AND (deleted_by IS NULL) AND (id IS NOT NULL) AND (lease_id = npe.lease_id))
33. 0.390 0.390 ↑ 1.0 1 130

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

  • Index Cond: (id = npe.property_id)
  • Filter: (cid = 11400)
34. 67.990 67.990 ↑ 1.0 1 130

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.42..2.65 rows=1 width=48) (actual time=0.504..0.523 rows=1 loops=130)

  • Index Cond: (lease_interval_id = npe.lease_interval_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
35. 3.168 3.168 ↑ 1.0 1 3

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..0.71 rows=1 width=12) (actual time=1.056..1.056 rows=1 loops=3)

  • Index Cond: ((cid = 11400) AND (id = ca.lease_id))
  • Filter: ((lease_status_type_id <> ALL ('{2,6}'::integer[])) AND (ca.property_id = property_id))
36. 0.588 0.588 ↑ 1.0 1 3

Index Scan using idx_application_stage_status_lease_interval_type_id on application_stage_statuses ass (cost=0.14..0.46 rows=1 width=16) (actual time=0.179..0.196 rows=1 loops=3)

  • Index Cond: (lease_interval_type_id = ca.lease_interval_type_id)
  • Filter: ((application_stage_id = ca.application_stage_id) AND (application_status_id = ca.application_status_id))
  • Rows Removed by Filter: 15
37. 4.377 4.377 ↑ 1.0 1 3

Index Scan using idx_applicants_id on applicants ap (cost=0.42..1.07 rows=1 width=8) (actual time=1.459..1.459 rows=1 loops=3)

  • Index Cond: (id = ca.primary_applicant_id)
  • Filter: ((cid = 11400) AND (cid = ca.cid))
38. 0.003 0.003 ↓ 0.0 0 3

Index Scan using idx_company_employees_id on company_employees ce (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3)

  • Index Cond: (id = ca.leasing_agent_id)
  • Filter: ((cid = 11400) AND (cid = ca.cid))
39. 42.879 42.879 ↓ 4.0 4 3

Index Scan using idx_ar_transactions_lease_id on ar_transactions at (cost=0.43..7.14 rows=1 width=12) (actual time=6.744..14.293 rows=4 loops=3)

  • Index Cond: (lease_id = ca.lease_id)
  • Filter: ((remote_primary_key IS NULL) AND (customer_id IS NOT NULL) AND (cid = 11400) AND (cid = ca.cid))
  • Rows Removed by Filter: 27
40. 1.625 1.625 ↑ 1.0 1 13

Index Scan using idx_ar_codes on ar_codes ac (cost=0.29..0.32 rows=1 width=8) (actual time=0.125..0.125 rows=1 loops=13)

  • Index Cond: (id = at.ar_code_id)
  • Filter: (((default_ar_code_id IS NULL) OR (default_ar_code_id <> 1101)) AND (cid = 11400) AND (cid = at.cid))
41. 0.611 0.611 ↑ 1.0 1 13

Index Scan using pk_dashboard_priorities on dashboard_priorities dp (cost=0.14..0.16 rows=1 width=66) (actual time=0.046..0.047 rows=1 loops=13)

  • Index Cond: ((cid = ca.cid) AND (cid = 11400))
42. 0.091 0.091 ↓ 0.0 0 13

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp1 (cost=0.42..0.54 rows=1 width=31) (actual time=0.007..0.007 rows=0 loops=13)

  • Index Cond: ((cid = ca.cid) AND (cid = 11400) AND (property_id = ca.property_id) AND ((key)::text = 'DO_NOT_CONSIDER_MESSAGE_CENTER_CORRESPONDENCE_AS_FOLLOW_UPS'::text))
  • Filter: (value IS NOT NULL)
43. 0.494 0.494 ↑ 1.0 1 13

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp2 (cost=0.42..0.54 rows=1 width=12) (actual time=0.038..0.038 rows=1 loops=13)

  • Index Cond: ((cid = ca.cid) AND (cid = 11400) AND (property_id = ca.property_id) AND ((key)::text = 'DO_NOT_CONSIDER_LEASING_CENTER_COMMUNICATION_AS_FOLLOW_UPS'::text))
  • Filter: (value IS NOT NULL)
44. 0.026 3.029 ↓ 0.0 0 13

Limit (cost=5.21..5.21 rows=1 width=24) (actual time=0.233..0.233 rows=0 loops=13)

45. 0.065 3.003 ↓ 0.0 0 13

Sort (cost=5.21..5.21 rows=1 width=24) (actual time=0.231..0.231 rows=0 loops=13)

  • Sort Key: ei.scheduled_datetime DESC, ei.id DESC
  • Sort Method: quicksort Memory: 25kB
46. 0.013 2.938 ↓ 0.0 0 13

Nested Loop Left Join (cost=0.71..5.20 rows=1 width=24) (actual time=0.226..0.226 rows=0 loops=13)

  • Filter: CASE WHEN (ei.event_type_id = ANY ('{1,17,173}'::integer[])) THEN true WHEN ((ei.event_type_id = 3) AND (ei.event_sub_type_id <> 22)) THEN true WHEN (ei.event_type_id = 4) THEN (2 = ANY (er_1.default_event_result_ids)) ELSE NULL::boolean END
47. 2.925 2.925 ↓ 0.0 0 13

Index Scan using idx_events_cid_lease_interval_id_event_datetime on events ei (cost=0.42..2.66 rows=1 width=28) (actual time=0.225..0.225 rows=0 loops=13)

  • Index Cond: ((cid = npe.cid) AND (lease_interval_id = npe.lease_interval_id))
  • Filter: ((NOT is_deleted) AND CASE WHEN (pp1.id IS NOT NULL) THEN (COALESCE(ps_product_id, 0) <> 46) ELSE true END AND CASE WHEN (pp2.id IS NOT NULL) THEN (COALESCE(ps_product_id, 0) <> 28) ELSE true END AND (event_type_id <> 15) AND (event_type_id = ANY ('{1,3,4,5,6,15,17,173}'::integer[])))
  • Rows Removed by Filter: 9
48. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_event_results on event_results er_1 (cost=0.28..2.50 rows=1 width=33) (never executed)

  • Index Cond: (id = ei.event_result_id)
  • Filter: ((cid = npe.cid) AND (cid = ei.cid))
49. 0.104 44.681 ↑ 1.0 1 13

Limit (cost=23.02..23.02 rows=1 width=52) (actual time=3.431..3.437 rows=1 loops=13)

50. 0.091 44.577 ↑ 2.0 1 13

Sort (cost=23.02..23.02 rows=2 width=52) (actual time=3.429..3.429 rows=1 loops=13)

  • Sort Key: ei_1.scheduled_datetime DESC, ei_1.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
51. 0.078 44.486 ↓ 2.5 5 13

Result (cost=6.32..23.01 rows=2 width=52) (actual time=2.486..3.422 rows=5 loops=13)

  • One-Time Filter: (npe.cid = 11400)
52. 2.652 44.408 ↓ 2.5 5 13

Hash Left Join (cost=6.32..23.01 rows=2 width=52) (actual time=2.484..3.416 rows=5 loops=13)

  • Hash Cond: ((ei_1.cid = fa_1.cid) AND (ei_1.data_reference_id = fa_1.file_id) AND (ei_1.lease_id = fa_1.lease_id))
  • Filter: CASE WHEN (ei_1.event_type_id = 309) THEN ((ei_1.lease_id = npe.lease_id) AND (fa_1.id IS NOT NULL)) ELSE (ei_1.lease_interval_id = npe.lease_interval_id) END
  • Rows Removed by Filter: 390
53. 13.481 13.481 ↓ 79.0 395 13

Index Scan using idx_events_cid_etid_pid_event_datetime on events ei_1 (cost=0.43..17.01 rows=5 width=32) (actual time=0.072..1.037 rows=395 loops=13)

  • Index Cond: ((cid = 11400) AND (event_type_id = ANY ('{13,14,25,119,86,309}'::integer[])) AND (property_id = npe.property_id))
  • Filter: ((event_sub_type_id IS NULL) AND (NOT is_deleted))
  • Rows Removed by Filter: 1
54. 0.130 28.275 ↓ 4.3 13 13

Hash (cost=5.84..5.84 rows=3 width=16) (actual time=2.175..2.175 rows=13 loops=13)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.156 28.145 ↓ 4.3 13 13

Result (cost=0.43..5.84 rows=3 width=16) (actual time=0.518..2.165 rows=13 loops=13)

  • One-Time Filter: (npe.cid = 11400)
56. 27.989 27.989 ↓ 4.3 13 13

Index Scan using idx_file_associations_cid_customer_id on file_associations fa_1 (cost=0.43..5.84 rows=3 width=16) (actual time=0.516..2.153 rows=13 loops=13)

  • Index Cond: ((cid = 11400) AND (customer_id = npe.customer_id))
  • Filter: ((file_signed_on IS NULL) AND (deleted_by IS NULL))
  • Rows Removed by Filter: 25
57. 0.013 2.314 ↓ 0.0 0 13

Limit (cost=2.66..2.67 rows=1 width=52) (actual time=0.178..0.178 rows=0 loops=13)

58. 0.052 2.301 ↓ 0.0 0 13

Sort (cost=2.66..2.67 rows=1 width=52) (actual time=0.177..0.177 rows=0 loops=13)

  • Sort Key: ei_2.scheduled_datetime DESC, ei_2.id DESC
  • Sort Method: quicksort Memory: 25kB
59. 0.013 2.249 ↓ 0.0 0 13

Result (cost=0.42..2.65 rows=1 width=52) (actual time=0.173..0.173 rows=0 loops=13)

  • One-Time Filter: (npe.cid = 11400)
60. 2.236 2.236 ↓ 0.0 0 13

Index Scan using idx_events_partial_dashboard_leads_applicants on events ei_2 (cost=0.42..2.65 rows=1 width=52) (actual time=0.172..0.172 rows=0 loops=13)

  • Index Cond: ((cid = 11400) AND (lease_interval_id = npe.lease_interval_id))
  • Filter: ((scheduled_datetime >= ei.scheduled_datetime) AND (event_type_id = 8) AND (property_id = npe.property_id))
  • Rows Removed by Filter: 2
61. 0.052 0.689 ↑ 1.0 1 13

Limit (cost=3.86..3.86 rows=1 width=24) (actual time=0.050..0.053 rows=1 loops=13)

62. 0.065 0.637 ↑ 1.0 1 13

Sort (cost=3.86..3.86 rows=1 width=24) (actual time=0.048..0.049 rows=1 loops=13)

  • Sort Key: ei_3.scheduled_datetime DESC, ei_3.id DESC
  • Sort Method: quicksort Memory: 25kB
63. 0.026 0.572 ↑ 1.0 1 13

Result (cost=0.42..3.85 rows=1 width=24) (actual time=0.031..0.044 rows=1 loops=13)

  • One-Time Filter: (npe.cid = 11400)
64. 0.546 0.546 ↑ 1.0 1 13

Index Scan using idx_events_cid_lease_interval_id_event_datetime on events ei_3 (cost=0.42..3.85 rows=1 width=24) (actual time=0.029..0.042 rows=1 loops=13)

  • Index Cond: ((cid = 11400) AND (lease_interval_id = npe.lease_interval_id))
  • Filter: ((NOT is_deleted) AND (event_type_id <> 15) AND CASE WHEN (pp2.id IS NULL) THEN true WHEN ((pp2.id IS NOT NULL) AND (COALESCE(ps_product_id, 0) <> 46)) THEN true ELSE NULL::boolean END AND (property_id = npe.property_id) AND CASE WHEN (pp1.id IS NULL) THEN true WHEN ((pp1.id IS NOT NULL) AND (pp1.value = '1'::text) AND (COALESCE(ps_product_id, 0) <> 28)) THEN true ELSE NULL::boolean END AND (to_char(scheduled_datetime, 'YYYY/MM/DD HH24:MI:SS'::text) >= to_char(COALESCE(ei.scheduled_datetime, ca.application_datetime), 'YYYY/MM/DD HH24:MI:SS'::text)) AND (event_type_id = ANY ('{78,17,9,15,10,238,18,5,1,33,6,7,4,2,3,16,173,25}'::integer[])))
  • Rows Removed by Filter: 8