explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 18KO : Optimization for: plan #AF9r

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.280 88.468 ↑ 1.0 1 1

Aggregate (cost=1,564.99..1,565.02 rows=1 width=12) (actual time=88.467..88.468 rows=1 loops=1)

2. 0.506 88.188 ↓ 511.0 511 1

Sort (cost=1,564.95..1,564.96 rows=1 width=20) (actual time=87.968..88.188 rows=511 loops=1)

  • Sort Key: subq.priority DESC
  • Sort Method: quicksort Memory: 48kB
  • estamp without time zone > (e.scheduled_datetime)::timestamp without time zone) AND (COALESCE((date_part('day'::text, ((e.scheduled_datetime)::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0) <> 0)) THEN 3 WHEN ((COALESCE((date_part('day'::text, ((e.scheduled_datetime)::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0) = 0) AND ((abs(COALESCE((date_part('hour'::text, ((e.scheduled_datetime)::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0)) > 0) OR (abs(COALESCE((date_part('minute'::text, ((e.scheduled_datetime)::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0)) > 0))) THEN 2 ELSE 1 END END)
3. 0.476 87.682 ↓ 511.0 511 1

Subquery Scan on subq (cost=1,564.85..1,564.94 rows=1 width=20) (actual time=86.502..87.682 rows=511 loops=1)

  • Filter: (subq.cid = 11400)
4. 0.487 87.206 ↓ 255.5 511 1

Unique (cost=1,564.85..1,564.88 rows=2 width=20) (actual time=86.500..87.206 rows=511 loops=1)

5. 0.545 86.719 ↓ 255.5 511 1

Sort (cost=1,564.85..1,564.85 rows=2 width=20) (actual time=86.498..86.719 rows=511 loops=1)

  • Sort Key: e.id, e.cid, e.lease_id, (NULL::integer), (CASE WHEN (e.details ? 'overdue_on'::text) THEN CASE WHEN (btrim(((e.details)::json ->> 'overdue_on'::text)) ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'::text) THEN CASE WHEN (((now())::timestamp without time zone > ('01/01/1970 00:00:00'::timestamp without time zone + (((btrim(((e.details)::json ->> 'overdue_on'::text)))::integer)::double precision * '00:00:01'::interval))) AND (COALESCE((date_part('day'::text, (('01/01/1970 00:00:00'::timestamp without time zone + (((btrim(((e.details)::json ->> 'overdue_on'::text)))::integer)::double precision * '00:00:01'::interval)) - (now())::timestamp without time zone)))::integer, 0) <> 0)) THEN 3 WHEN ((COALESCE((date_part('day'::text, (('01/01/1970 00:00:00'::timestamp without time zone + (((btrim(((e.details)::json ->> 'overdue_on'::text)))::integer)::double precision * '00:00:01'::interval)) - (now())::timestamp without time zone)))::integer, 0) = 0) AND ((abs(COALESCE((date_part('hour'::text, (('01/01/1970 00:00:00'::timestamp without time zone + (((btrim(((e.details)::json ->> 'overdue_on'::text)))::integer)::double precision * '00:00:01'::interval)) - (now())::timestamp without time zone)))::integer, 0)) > 0) OR (abs(COALESCE((date_part('minute'::text, (('01/01/1970 00:00:00'::timestamp without time zone + (((btrim(((e.details)::json ->> 'overdue_on'::text)))::integer)::double precision * '00:00:01'::interval)) - (now())::timestamp without time zone)))::integer, 0)) > 0))) THEN 2 ELSE 1 END ELSE CASE WHEN (((now())::timestamp without time zone > (btrim(((e.details)::json ->> 'overdue_on'::text)))::timestamp without time zone) AND (COALESCE((date_part('day'::text, ((btrim(((e.details)::json ->> 'overdue_on'::text)))::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0) <> 0)) THEN 3 WHEN ((COALESCE((date_part('day'::text, ((btrim(((e.details)::json ->> 'overdue_on'::text)))::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0) = 0) AND ((abs(COALESCE((date_part('hour'::text, ((btrim(((e.details)::json ->> 'overdue_on'::text)))::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0)) > 0) OR (abs(COALESCE((date_part('minute'::text, ((btrim(((e.details)::json ->> 'overdue_on'::text)))::timestamp without time zone - (now())::timestamp without time zone)))::integer, 0)) > 0))) THEN 2 ELSE 1 END END ELSE CASE WHEN (((now())::tim
  • Sort Method: quicksort Memory: 53kB
6. 0.433 86.174 ↓ 255.5 511 1

Append (cost=944.40..1,564.84 rows=2 width=20) (actual time=27.904..86.174 rows=511 loops=1)

7. 0.352 28.412 ↓ 360.0 360 1

Unique (cost=944.40..944.41 rows=1 width=20) (actual time=27.903..28.412 rows=360 loops=1)

8. 0.442 28.060 ↓ 360.0 360 1

Sort (cost=944.40..944.41 rows=1 width=20) (actual time=27.902..28.060 rows=360 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 41kB
9. 3.708 27.618 ↓ 360.0 360 1

Nested Loop Anti Join (cost=5.26..944.39 rows=1 width=20) (actual time=7.779..27.618 rows=360 loops=1)

10. 2.235 22.119 ↓ 597.0 597 1

Nested Loop Left Join (cost=4.98..943.47 rows=1 width=150) (actual time=3.204..22.119 rows=597 loops=1)

  • Join Filter: (cl.id = ca.lease_id)
  • Filter: CASE WHEN (ca.mute_followups_until IS NOT NULL) THEN (CURRENT_DATE >= ca.mute_followups_until) ELSE true END
11. 0.705 17.496 ↓ 597.0 597 1

Nested Loop (cost=4.55..941.00 rows=1 width=162) (actual time=3.187..17.496 rows=597 loops=1)

12. 1.816 15.597 ↓ 597.0 597 1

Nested Loop (cost=4.28..940.66 rows=1 width=166) (actual time=3.177..15.597 rows=597 loops=1)

  • Join Filter: (lp.property_id = cl.property_id)
13. 1.338 8.681 ↓ 1,275.0 1,275 1

Nested Loop (cost=3.86..938.34 rows=1 width=170) (actual time=2.104..8.681 rows=1,275 loops=1)

14. 0.006 2.083 ↑ 1.0 1 1

Merge Join (cost=3.44..10.22 rows=1 width=12) (actual time=2.080..2.083 rows=1 loops=1)

  • Merge Cond: (lp.property_id = p.id)
15. 0.009 2.066 ↑ 50.0 1 1

Sort (cost=3.16..3.29 rows=50 width=4) (actual time=2.065..2.066 rows=1 loops=1)

  • Sort Key: lp.property_id
  • Sort Method: quicksort Memory: 25kB
16. 2.057 2.057 ↑ 50.0 1 1

Function Scan on load_properties lp (cost=0.25..1.75 rows=50 width=4) (actual time=2.056..2.057 rows=1 loops=1)

17. 0.011 0.011 ↑ 6.5 2 1

Index Only Scan using idx_properties_cid_id_is_disabled_remote_primary_key on properties p (cost=0.28..6.75 rows=13 width=8) (actual time=0.010..0.011 rows=2 loops=1)

  • Index Cond: (cid = 11400)
  • Heap Fetches: 1
18. 5.260 5.260 ↓ 637.5 1,275 1

Index Scan using idx_events_partial_dashboard_contact_needed on events e (cost=0.42..928.06 rows=2 width=166) (actual time=0.022..5.260 rows=1,275 loops=1)

  • Index Cond: (property_id = lp.property_id)
  • Filter: ((cid = 11400) AND (((event_type_id = 316) AND (event_sub_type_id IS NULL)) OR (event_sub_type_id <> ALL ('{20,85}'::integer[]))) AND (CURRENT_DATE >= (scheduled_datetime)::date))
  • Rows Removed by Filter: 2350
19. 5.100 5.100 ↓ 0.0 0 1,275

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..2.28 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=1,275)

  • Index Cond: ((cid = 11400) AND (id = e.lease_id))
  • Filter: ((e.property_id = property_id) AND ((lease_status_type_id = ANY ('{3,4,5}'::integer[])) OR (lease_status_type_id = 1)) AND (occupancy_type_id = ANY ('{1,2,3,4,6,9,10,11}'::integer[])) AND ((lease_status_type_id = ANY ('{3,4,5}'::integer[])) OR (((lease_interval_type_id = 5) OR (e.event_type_id = 316)) AND (lease_status_type_id = 1))))
  • Rows Removed by Filter: 1
20. 1.194 1.194 ↑ 1.0 1 597

Index Only Scan using pk_event_types on event_types et (cost=0.27..0.34 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=597)

  • Index Cond: (id = e.event_type_id)
  • Heap Fetches: 0
21. 2.388 2.388 ↑ 1.0 1 597

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.42..2.43 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=597)

  • Index Cond: (lease_interval_id = e.lease_interval_id)
  • Filter: ((cid = 11400) AND (e.cid = cid) AND (property_id = e.property_id))
22. 1.791 1.791 ↓ 0.0 0 597

Index Scan using idx_event_results on event_results er (cost=0.28..0.46 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=597)

  • Index Cond: (id = e.event_result_id)
  • Filter: (is_system AND (cid = 11400) AND (cid = e.cid) AND ((2 = ANY (default_event_result_ids)) OR (4 = ANY (default_event_result_ids)) OR (65 = ANY (default_event_result_ids))))
  • Rows Removed by Filter: 4
23. 0.151 57.329 ↓ 151.0 151 1

Subquery Scan on subq1 (cost=620.33..620.37 rows=1 width=20) (actual time=56.796..57.329 rows=151 loops=1)

  • Filter: (subq1.lease_id = subq1.lease_customer_lease_id)
  • Rows Removed by Filter: 7
24. 0.233 57.178 ↓ 158.0 158 1

Unique (cost=620.33..620.33 rows=1 width=28) (actual time=56.792..57.178 rows=158 loops=1)

25. 0.419 56.945 ↓ 332.0 332 1

Sort (cost=620.33..620.33 rows=1 width=28) (actual time=56.791..56.945 rows=332 loops=1)

  • Sort Key: cs.id
  • Sort Method: quicksort Memory: 50kB
26. 12.283 56.526 ↓ 332.0 332 1

WindowAgg (cost=620.16..620.32 rows=1 width=28) (actual time=44.173..56.526 rows=332 loops=1)

27. 0.482 44.243 ↓ 332.0 332 1

Sort (cost=620.16..620.17 rows=1 width=77) (actual time=44.092..44.243 rows=332 loops=1)

  • Sort Key: cs.customer_id, lc.id DESC
  • Sort Method: quicksort Memory: 50kB
28. 0.634 43.761 ↓ 332.0 332 1

Nested Loop Left Join (cost=112.16..620.15 rows=1 width=77) (actual time=6.041..43.761 rows=332 loops=1)

29. 0.000 41.516 ↓ 179.0 179 1

Nested Loop Left Join (cost=111.74..617.32 rows=1 width=89) (actual time=6.024..41.516 rows=179 loops=1)

  • Filter: CASE WHEN ((cl_1.lease_status_type_id = 6) AND ((COALESCE(lps.move_out_date, li.lease_end_date) + COALESCE((pp.value)::integer, 60)) > CURRENT_DATE)) THEN ((cl_1.id IS NULL) OR (cl_1.lease_status_type_id = ANY ('{3,5,6,4}'::integer[]))) ELSE ((cl_1.id IS NULL) OR (cl_1.lease_status_type_id = ANY ('{3,4,5}'::integer[]))) END
  • Rows Removed by Filter: 243
30. 0.805 38.474 ↓ 422.0 422 1

Nested Loop Left Join (cost=111.32..615.09 rows=1 width=105) (actual time=5.161..38.474 rows=422 loops=1)

  • Join Filter: (cs.cid = lps.cid)
31. 0.935 34.834 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.89..614.29 rows=1 width=101) (actual time=5.142..34.834 rows=405 loops=1)

  • Join Filter: (li.cid = cs.cid)
32. 0.770 31.469 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.47..612.98 rows=1 width=101) (actual time=5.124..31.469 rows=405 loops=1)

33. 1.149 28.269 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.05..612.05 rows=1 width=85) (actual time=5.104..28.269 rows=405 loops=1)

34. 0.856 26.310 ↓ 405.0 405 1

Nested Loop (cost=109.90..611.78 rows=1 width=36) (actual time=5.092..26.310 rows=405 loops=1)

35. 0.781 23.124 ↓ 466.0 466 1

Nested Loop (cost=109.48..608.56 rows=1 width=24) (actual time=5.015..23.124 rows=466 loops=1)

36. 0.492 21.411 ↓ 466.0 466 1

Nested Loop (cost=109.35..608.21 rows=1 width=28) (actual time=5.004..21.411 rows=466 loops=1)

  • Join Filter: (lp_1.property_id = cs.property_id)
37. 0.003 3.910 ↑ 1.0 1 1

Nested Loop (cost=0.53..4.48 rows=1 width=16) (actual time=3.908..3.910 rows=1 loops=1)

38. 3.902 3.902 ↑ 1.0 1 1

Function Scan on load_properties lp_1 (cost=0.25..1.88 rows=1 width=4) (actual time=3.900..3.902 rows=1 loops=1)

  • Filter: (is_disabled = 0)
39. 0.005 0.005 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p_1 (cost=0.28..2.32 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = lp_1.property_id)
  • Filter: (cid = 11400)
40. 3.286 17.009 ↓ 155.3 466 1

Bitmap Heap Scan on contact_submissions cs (cost=108.82..603.63 rows=3 width=24) (actual time=1.088..17.009 rows=466 loops=1)

  • Recheck Cond: ((property_id = p_1.id) AND (cid = 11400))
  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (customer_message_id IS NULL) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 1529
  • Heap Blocks: exact=839
41. 0.035 0.925 ↓ 0.0 0 1

BitmapAnd (cost=108.82..108.82 rows=47 width=0) (actual time=0.925..0.925 rows=0 loops=1)

42. 0.168 0.168 ↓ 8.0 1,995 1

Bitmap Index Scan on idx_contact_submissions_property_id (cost=0.00..3.17 rows=250 width=0) (actual time=0.168..0.168 rows=1,995 loops=1)

  • Index Cond: (property_id = p_1.id)
43. 0.722 0.722 ↑ 1.0 9,173 1

Bitmap Index Scan on pk_contact_submissions (cost=0.00..105.24 rows=9,193 width=0) (actual time=0.722..0.722 rows=9,173 loops=1)

  • Index Cond: (cid = 11400)
44.          

SubPlan (forBitmap Heap Scan)

45. 0.474 12.798 ↓ 0.0 0 474

Unique (cost=9.57..9.58 rows=1 width=4) (actual time=0.027..0.027 rows=0 loops=474)

46. 0.948 12.324 ↓ 0.0 0 474

Sort (cost=9.57..9.58 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=474)

  • Sort Key: e_1.data_reference_id
  • Sort Method: quicksort Memory: 25kB
47. 0.948 11.376 ↓ 0.0 0 474

Result (cost=8.52..9.56 rows=1 width=4) (actual time=0.023..0.024 rows=0 loops=474)

  • One-Time Filter: (cs.cid = 11400)
48. 0.948 10.428 ↓ 0.0 0 474

Bitmap Heap Scan on events e_1 (cost=8.52..9.56 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=474)

  • Recheck Cond: ((cid = 11400) AND (event_type_id = ANY ('{339,340}'::integer[])) AND (property_id = cs.property_id) AND (customer_id = cs.customer_id))
  • Filter: (data_reference_id IS NOT NULL)
  • Heap Blocks: exact=17
49. 1.422 9.480 ↓ 0.0 0 474

BitmapAnd (cost=8.52..8.52 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=474)

50. 3.318 3.318 ↑ 2.2 8 474

Bitmap Index Scan on idx_events_cid_etid_pid_event_datetime (cost=0.00..3.35 rows=18 width=0) (actual time=0.007..0.007 rows=8 loops=474)

  • Index Cond: ((cid = 11400) AND (event_type_id = ANY ('{339,340}'::integer[])) AND (property_id = cs.property_id))
51. 4.740 4.740 ↑ 3.9 81 474

Bitmap Index Scan on idx_events_customer_id (cost=0.00..4.92 rows=314 width=0) (actual time=0.010..0.010 rows=81 loops=474)

  • Index Cond: (customer_id = cs.customer_id)
52. 0.932 0.932 ↑ 1.0 1 466

Index Only Scan using pk_time_zones on time_zones tz (cost=0.14..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=466)

  • Index Cond: (id = p_1.time_zone_id)
  • Heap Fetches: 466
53. 2.330 2.330 ↑ 1.0 1 466

Index Scan using idx_lease_customers_customer_id on lease_customers lc (cost=0.42..3.19 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=466)

  • Index Cond: (customer_id = cs.customer_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
54. 0.810 0.810 ↑ 1.0 1 405

Index Scan using pk_dashboard_priorities on dashboard_priorities dp (cost=0.14..0.23 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=405)

  • Index Cond: ((cid = cs.cid) AND (cid = 11400))
55. 2.430 2.430 ↑ 1.0 1 405

Index Scan using pk_cached_leases on cached_leases cl_1 (cost=0.42..0.93 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=405)

  • Index Cond: ((cid = lc.cid) AND (cid = 11400) AND (id = lc.lease_id))
56. 2.430 2.430 ↑ 1.0 1 405

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.42..1.28 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=405)

  • Index Cond: (id = cl_1.active_lease_interval_id)
  • Filter: ((lease_status_type_id <> 2) AND (cid = 11400))
  • Rows Removed by Filter: 0
57. 2.835 2.835 ↑ 1.0 1 405

Index Scan using idx_lease_processes_lease_id on lease_processes lps (cost=0.42..0.78 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=405)

  • Index Cond: (cl_1.id = lease_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
58. 0.000 2.110 ↑ 1.0 1 422

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..2.18 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=422)

  • One-Time Filter: (cs.cid = 11400)
59. 0.948 10.428 ↓ 0.0 0 474

Bitmap Heap Scan on events e_1 (cost=8.52..9.56 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=474)

  • Recheck Cond: ((cid = 11400) AND (event_type_id = ANY ('{339,340}'::integer[])) AND (property_id = cs.property_id) AND (customer_id = cs.customer_id))
  • Filter: (data_reference_id IS NOT NULL)
  • Heap Blocks: exact=17
60. 1.422 9.480 ↓ 0.0 0 474

BitmapAnd (cost=8.52..8.52 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=474)

61. 3.318 3.318 ↑ 2.2 8 474

Bitmap Index Scan on idx_events_cid_etid_pid_event_datetime (cost=0.00..3.35 rows=18 width=0) (actual time=0.007..0.007 rows=8 loops=474)

  • Index Cond: ((cid = 11400) AND (event_type_id = ANY ('{339,340}'::integer[])) AND (property_id = cs.property_id))
62. 4.740 4.740 ↑ 3.9 81 474

Bitmap Index Scan on idx_events_customer_id (cost=0.00..4.92 rows=314 width=0) (actual time=0.010..0.010 rows=81 loops=474)

  • Index Cond: (customer_id = cs.customer_id)
63. 0.932 0.932 ↑ 1.0 1 466

Index Only Scan using pk_time_zones on time_zones tz (cost=0.14..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=466)

  • Index Cond: (id = p_1.time_zone_id)
  • Heap Fetches: 466
64. 2.330 2.330 ↑ 1.0 1 466

Index Scan using idx_lease_customers_customer_id on lease_customers lc (cost=0.42..3.19 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=466)

  • Index Cond: (customer_id = cs.customer_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
65. 0.810 0.810 ↑ 1.0 1 405

Index Scan using pk_dashboard_priorities on dashboard_priorities dp (cost=0.14..0.23 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=405)

  • Index Cond: ((cid = cs.cid) AND (cid = 11400))
66. 2.430 2.430 ↑ 1.0 1 405

Index Scan using pk_cached_leases on cached_leases cl_1 (cost=0.42..0.93 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=405)

  • Index Cond: ((cid = lc.cid) AND (cid = 11400) AND (id = lc.lease_id))
67. 2.430 2.430 ↑ 1.0 1 405

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.42..1.28 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=405)

  • Index Cond: (id = cl_1.active_lease_interval_id)
  • Filter: ((lease_status_type_id <> 2) AND (cid = 11400))
  • Rows Removed by Filter: 0
68. 2.835 2.835 ↑ 1.0 1 405

Index Scan using idx_lease_processes_lease_id on lease_processes lps (cost=0.42..0.78 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=405)

  • Index Cond: (cl_1.id = lease_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
69. 2.110 2.110 ↑ 1.0 1 422

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..2.18 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=422)

  • Index Cond: ((cs.cid = cid) AND (cid = 11400) AND (cs.property_id = property_id) AND ((key)::text = 'PAST_RESIDENT_LOGIN_TOLERANCE_DAYS'::text))
70. 1.611 1.611 ↓ 2.0 2 179

Index Scan using idx_cached_applications_lease_id on cached_applications ca_1 (cost=0.42..2.80 rows=1 width=16) (actual time=0.007..0.009 rows=2 loops=179)

  • Index Cond: (cl_1.id = lease_id)
  • Filter: ((cid = 11400) AND (cl_1.cid = cid) AND (property_id = cl_1.property_id))
  • Rows Removed by Filter: 0