explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UUcC

Settings
# exclusive inclusive rows x rows loops node
1. 0.289 4,882.309 ↑ 1.0 1 1

Aggregate (cost=361,030.83..361,030.86 rows=1 width=12) (actual time=4,882.309..4,882.309 rows=1 loops=1)

2. 0.545 4,882.020 ↓ 511.0 511 1

Sort (cost=361,030.79..361,030.80 rows=1 width=20) (actual time=4,881.798..4,882.020 rows=511 loops=1)

  • Sort Key: subq.priority DESC
  • Sort Method: quicksort Memory: 48kB
3. 0.488 4,881.475 ↓ 511.0 511 1

Subquery Scan on subq (cost=361,030.69..361,030.78 rows=1 width=20) (actual time=4,880.269..4,881.475 rows=511 loops=1)

  • Filter: (subq.cid = 11400)
4. 0.497 4,880.987 ↓ 255.5 511 1

Unique (cost=361,030.69..361,030.72 rows=2 width=20) (actual time=4,880.266..4,880.987 rows=511 loops=1)

5. 0.551 4,880.490 ↓ 255.5 511 1

Sort (cost=361,030.69..361,030.69 rows=2 width=20) (actual time=4,880.265..4,880.490 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())::timestamp 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)
  • Sort Method: quicksort Memory: 53kB
6. 0.431 4,879.939 ↓ 255.5 511 1

Append (cost=360,410.51..361,030.68 rows=2 width=20) (actual time=4,742.602..4,879.939 rows=511 loops=1)

7. 0.349 4,743.104 ↓ 360.0 360 1

Unique (cost=360,410.51..360,410.52 rows=1 width=20) (actual time=4,742.601..4,743.104 rows=360 loops=1)

8. 8.072 4,742.755 ↓ 360.0 360 1

Sort (cost=360,410.51..360,410.51 rows=1 width=20) (actual time=4,742.597..4,742.755 rows=360 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 41kB
9. 6.739 4,734.683 ↓ 360.0 360 1

Nested Loop Anti Join (cost=2.21..360,410.50 rows=1 width=20) (actual time=31.305..4,734.683 rows=360 loops=1)

10. 2.487 4,724.362 ↓ 597.0 597 1

Nested Loop Left Join (cost=1.93..360,409.58 rows=1 width=150) (actual time=31.121..4,724.362 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. 2.419 4,702.771 ↓ 597.0 597 1

Nested Loop (cost=1.51..360,407.11 rows=1 width=162) (actual time=31.065..4,702.771 rows=597 loops=1)

12. 2.596 4,699.158 ↓ 597.0 597 1

Nested Loop (cost=1.23..360,406.77 rows=1 width=166) (actual time=31.032..4,699.158 rows=597 loops=1)

13. 2.543 4,654.487 ↓ 14.2 1,275 1

Nested Loop (cost=0.81..360,198.68 rows=90 width=162) (actual time=30.973..4,654.487 rows=1,275 loops=1)

14. 4.707 4.707 ↑ 50.0 1 1

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

15. 4,647.237 4,647.237 ↓ 637.5 1,275 1

Index Scan using idx_events_s1314 on events e (cost=0.56..7,203.88 rows=2 width=166) (actual time=26.262..4,647.237 rows=1,275 loops=1)

  • Index Cond: ((cid = 11400) AND (property_id = lp.property_id))
  • Filter: ((lease_id IS NOT NULL) AND (NOT is_deleted) 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) AND ((event_sub_type_id = ANY ('{2,17,18,19,5,7,20,21,1}'::integer[])) OR ((event_type_id = 1) AND (ps_product_id = 28) AND is_resident) OR ((event_sub_type_id = ANY ('{23,9,10,11,111,112,113,114,115,70,136,135,68,69,153,154,155,156,157}'::integer[])) AND (ps_product_id = 28)) OR ((event_type_id = 316) AND (event_sub_type_id IS NULL))))
  • Rows Removed by Filter: 433730
16. 42.075 42.075 ↓ 0.0 0 1,275

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

  • Index Cond: ((cid = 11400) AND (id = e.lease_id))
  • Filter: (((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
17. 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
18. 19.104 19.104 ↑ 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.031..0.032 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))
19. 3.582 3.582 ↓ 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.006..0.006 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
20. 0.153 136.404 ↓ 151.0 151 1

Subquery Scan on subq1 (cost=620.06..620.10 rows=1 width=20) (actual time=135.854..136.404 rows=151 loops=1)

  • Filter: (subq1.lease_id = subq1.lease_customer_lease_id)
  • Rows Removed by Filter: 7
21. 0.245 136.251 ↓ 158.0 158 1

Unique (cost=620.06..620.07 rows=1 width=28) (actual time=135.851..136.251 rows=158 loops=1)

22. 0.425 136.006 ↓ 332.0 332 1

Sort (cost=620.06..620.06 rows=1 width=28) (actual time=135.849..136.006 rows=332 loops=1)

  • Sort Key: cs.id
  • Sort Method: quicksort Memory: 50kB
23. 12.884 135.581 ↓ 332.0 332 1

WindowAgg (cost=619.90..620.05 rows=1 width=28) (actual time=122.729..135.581 rows=332 loops=1)

24. 0.554 122.697 ↓ 332.0 332 1

Sort (cost=619.90..619.90 rows=1 width=77) (actual time=122.529..122.697 rows=332 loops=1)

  • Sort Key: cs.customer_id, lc.id DESC
  • Sort Method: quicksort Memory: 50kB
25. 0.606 122.143 ↓ 332.0 332 1

Nested Loop Left Join (cost=112.16..619.89 rows=1 width=77) (actual time=36.661..122.143 rows=332 loops=1)

26. 1.245 117.778 ↓ 179.0 179 1

Nested Loop Left Join (cost=111.74..617.02 rows=1 width=89) (actual time=36.621..117.778 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
27. 0.812 111.891 ↓ 422.0 422 1

Nested Loop Left Join (cost=111.32..614.79 rows=1 width=105) (actual time=32.135..111.891 rows=422 loops=1)

  • Join Filter: (cs.cid = lps.cid)
28. 0.920 105.814 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.89..613.96 rows=1 width=101) (actual time=32.094..105.814 rows=405 loops=1)

  • Join Filter: (li.cid = cs.cid)
29. 0.812 86.669 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.47..612.63 rows=1 width=101) (actual time=31.965..86.669 rows=405 loops=1)

30. 1.079 82.617 ↓ 405.0 405 1

Nested Loop Left Join (cost=110.05..611.69 rows=1 width=85) (actual time=31.944..82.617 rows=405 loops=1)

31. 1.092 80.323 ↓ 405.0 405 1

Nested Loop (cost=109.90..611.43 rows=1 width=36) (actual time=31.926..80.323 rows=405 loops=1)

32. 1.128 70.377 ↓ 466.0 466 1

Nested Loop (cost=109.48..608.21 rows=1 width=24) (actual time=31.775..70.377 rows=466 loops=1)

33. 0.535 68.317 ↓ 466.0 466 1

Nested Loop (cost=109.35..607.86 rows=1 width=28) (actual time=31.744..68.317 rows=466 loops=1)

  • Join Filter: (lp_1.property_id = cs.property_id)
34. 0.006 25.153 ↑ 1.0 1 1

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

35. 25.142 25.142 ↑ 1.0 1 1

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

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

Index Scan using idx_properties_id on properties p (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)
37. 10.903 42.629 ↓ 155.3 466 1

Bitmap Heap Scan on contact_submissions cs (cost=108.82..603.28 rows=3 width=24) (actual time=6.570..42.629 rows=466 loops=1)

  • Recheck Cond: ((property_id = p.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
38. 0.040 5.182 ↓ 0.0 0 1

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

39. 0.260 0.260 ↓ 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.260..0.260 rows=1,995 loops=1)

  • Index Cond: (property_id = p.id)
40. 4.882 4.882 ↑ 1.0 9,170 1

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

  • Index Cond: (cid = 11400)
41.          

SubPlan (forBitmap Heap Scan)

42. 0.948 26.544 ↓ 0.0 0 474

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

43. 0.948 25.596 ↓ 0.0 0 474

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

  • Sort Key: e_1.data_reference_id
  • Sort Method: quicksort Memory: 25kB
44. 2.370 24.648 ↓ 0.0 0 474

Result (cost=8.51..9.55 rows=1 width=4) (actual time=0.052..0.052 rows=0 loops=474)

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

Bitmap Heap Scan on events e_1 (cost=8.51..9.55 rows=1 width=4) (actual time=0.047..0.047 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
46. 1.896 21.330 ↓ 0.0 0 474

BitmapAnd (cost=8.51..8.51 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=474)

47. 3.792 3.792 ↑ 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.008..0.008 rows=8 loops=474)

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

Bitmap Index Scan on idx_events_customer_id (cost=0.00..4.91 rows=313 width=0) (actual time=0.033..0.033 rows=81 loops=474)

  • Index Cond: (customer_id = cs.customer_id)
49. 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.time_zone_id)
  • Heap Fetches: 466
50. 8.854 8.854 ↑ 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.008..0.019 rows=1 loops=466)

  • Index Cond: (customer_id = cs.customer_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
51. 1.215 1.215 ↑ 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.003 rows=1 loops=405)

  • Index Cond: ((cid = cs.cid) AND (cid = 11400))
52. 3.240 3.240 ↑ 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.008..0.008 rows=1 loops=405)

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

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.42..1.30 rows=1 width=12) (actual time=0.045..0.045 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
54. 5.265 5.265 ↑ 1.0 1 405

Index Scan using idx_lease_processes_lease_id on lease_processes lps (cost=0.42..0.80 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=405)

  • Index Cond: (cl_1.id = lease_id)
  • Filter: (cid = 11400)
  • Rows Removed by Filter: 0
55. 4.642 4.642 ↑ 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.011..0.011 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))
56. 3.759 3.759 ↓ 2.0 2 179

Index Scan using idx_cached_applications_lease_id on cached_applications ca_1 (cost=0.42..2.83 rows=1 width=16) (actual time=0.017..0.021 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