explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qLaV

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 5,554.595 ↓ 9.0 9 1

Sort (cost=106,169.21..106,169.21 rows=1 width=688) (actual time=5,554.594..5,554.595 rows=9 loops=1)

  • Sort Key: sub.property_name
  • Sort Method: quicksort Memory: 27kB
2. 1.605 5,554.547 ↓ 9.0 9 1

GroupAggregate (cost=106,169.06..106,169.19 rows=1 width=688) (actual time=5,553.241..5,554.547 rows=9 loops=1)

  • Group Key: sub.cid, sub.lookup_code, sub.property_id, sub.property_name
  • Filter: ((count(sub.application_id) <> 0) OR (sum(CASE WHEN (sub.manual_contacts_attempted_in_first_24_hours_comparison = 'YES'::text) THEN 1 ELSE 0 END) <> 0) OR (sum(CASE WHEN (sub.manual_contacts_attempted_in_first_24_hours_comparison = 'NO'::text) THEN 1 ELSE 0 END) <> 0) OR (((sum(CASE WHEN (sub.manual_contacts_attempted_in_first_24_hours_comparison = 'NO'::text) THEN 1 ELSE 0 END))::numeric / (count(sub.application_id))::numeric(8,3)) <> '0'::numeric) OR (sum(CASE WHEN (sub.manual_contacts_attempted_in_first_48_hours_comparison = 'YES'::text) THEN 1 ELSE 0 END) <> 0) OR (sum(CASE WHEN (sub.manual_contacts_attempted_in_first_48_hours_comparison = 'NO'::text) THEN 1 ELSE 0 END) <> 0) OR (((sum(CASE WHEN (sub.manual_contacts_attempted_in_first_48_hours_comparison = 'NO'::text) THEN 1 ELSE 0 END))::numeric / (count(sub.application_id))::numeric(8,3)) <> '0'::numeric))
3. 0.611 5,552.942 ↓ 831.0 831 1

Sort (cost=106,169.06..106,169.06 rows=1 width=597) (actual time=5,552.801..5,552.942 rows=831 loops=1)

  • Sort Key: sub.lookup_code, sub.property_id, sub.property_name
  • Sort Method: quicksort Memory: 102kB
4. 0.337 5,552.331 ↓ 831.0 831 1

Hash Join (cost=106,167.73..106,169.05 rows=1 width=597) (actual time=5,552.034..5,552.331 rows=831 loops=1)

  • Hash Cond: (load_prop.property_id = sub.property_id)
5. 0.017 0.017 ↑ 1.0 22 1

Seq Scan on load_prop (cost=0.00..1.22 rows=22 width=4) (actual time=0.007..0.017 rows=22 loops=1)

6. 0.591 5,551.977 ↓ 831.0 831 1

Hash (cost=106,167.72..106,167.72 rows=1 width=597) (actual time=5,551.977..5,551.977 rows=831 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
7. 0.432 5,551.386 ↓ 831.0 831 1

Subquery Scan on sub (cost=106,166.53..106,167.72 rows=1 width=597) (actual time=5,511.949..5,551.386 rows=831 loops=1)

8. 35.624 5,550.954 ↓ 831.0 831 1

GroupAggregate (cost=106,166.53..106,167.71 rows=1 width=1,113) (actual time=5,511.932..5,550.954 rows=831 loops=1)

  • Group Key: cal.id, cal.cid, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, c.name_full, (COALESCE(concat_ws(' '::text, util_get_system_translated('stage_name'::text, (ass.stage_name)::text, ass.details, NULL::text), util_get_system_translated('status_name'::text, (ass.status_name)::text, ass.details, NULL::text)), ' - '::text)), (string_agg((ls.name)::text, ','::text)), (CASE WHEN ((e_1.event_type_id = ANY ('{1,2}'::integer[])) AND (util.util_to_bool((e_1.details ->> 'is_system_generated'::text)) IS DISTINCT FROM true)) THEN 'Email'::text WHEN ((e_1.event_type_id = ANY ('{3,405,4}'::integer[])) AND ((e_1.event_sub_type_id <> 22) OR (e_1.event_sub_type_id IS NULL))) THEN 'Call'::text WHEN (e_1.event_type_id = ANY ('{5,6}'::integer[])) THEN 'Text'::text WHEN ((e_1.event_type_id = 7) AND ((e_1.event_sub_type_id <> 106) OR (e_1.event_sub_type_id IS NULL))) THEN 'Chat'::text WHEN ((e_1.event_type_id = ANY ('{9,78}'::integer[])) AND (2 = ANY (COALESCE(ers_1.default_event_result_ids, '{}'::integer[])))) THEN 'Visit/Tour'::text WHEN (e_1.event_type_id = ANY ('{10,395}'::integer[])) THEN 'Online'::text ELSE 'Other'::text END), ce.name_first, ce.name_last, e_2.leasing_agent_activity
9. 85.127 5,515.330 ↓ 14,005.0 14,005 1

Sort (cost=106,166.53..106,166.53 rows=1 width=1,073) (actual time=5,511.732..5,515.330 rows=14,005 loops=1)

  • Sort Key: cal.id, load_prop_1.property_id, load_prop_1.property_name, load_prop_1.lookup_code, c.name_full, (COALESCE(concat_ws(' '::text, util_get_system_translated('stage_name'::text, (ass.stage_name)::text, ass.details, NULL::text), util_get_system_translated('status_name'::text, (ass.status_name)::text, ass.details, NULL::text)), ' - '::text)), (string_agg((ls.name)::text, ','::text)), (CASE WHEN ((e_1.event_type_id = ANY ('{1,2}'::integer[])) AND (util.util_to_bool((e_1.details ->> 'is_system_generated'::text)) IS DISTINCT FROM true)) THEN 'Email'::text WHEN ((e_1.event_type_id = ANY ('{3,405,4}'::integer[])) AND ((e_1.event_sub_type_id <> 22) OR (e_1.event_sub_type_id IS NULL))) THEN 'Call'::text WHEN (e_1.event_type_id = ANY ('{5,6}'::integer[])) THEN 'Text'::text WHEN ((e_1.event_type_id = 7) AND ((e_1.event_sub_type_id <> 106) OR (e_1.event_sub_type_id IS NULL))) THEN 'Chat'::text WHEN ((e_1.event_type_id = ANY ('{9,78}'::integer[])) AND (2 = ANY (COALESCE(ers_1.default_event_result_ids, '{}'::integer[])))) THEN 'Visit/Tour'::text WHEN (e_1.event_type_id = ANY ('{10,395}'::integer[])) THEN 'Online'::text ELSE 'Other'::text END), ce.name_first, ce.name_last, e_2.leasing_agent_activity
  • Sort Method: quicksort Memory: 10556kB
10. 4,150.224 5,430.203 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,035.96..106,166.52 rows=1 width=1,073) (actual time=435.925..5,430.203 rows=14,005 loops=1)

11. 26.778 1,027.889 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,034.31..106,158.82 rows=1 width=1,053) (actual time=432.809..1,027.889 rows=14,005 loops=1)

  • Filter: ((ls_1.id = ANY ('{0,96178,96247,96038,96036,96179,96180,96034,96181,96244,96182,96242,96248,96243,96184,96183,96030,96185,96186,96187,96188,96033,96241,96189,96190,96191,96192,96193,96039,96037,96031,96194,96195,96196,96197,96029,96198,96199,96040,96200,96035,96032,96249,96201,96202,96203,96028,96245,96246,96206,96205,100949,96204}'::integer[])) OR (ls_1.id IS NULL))
12. 27.936 959.096 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,034.03..106,151.11 rows=1 width=1,061) (actual time=432.793..959.096 rows=14,005 loops=1)

13. 17.417 763.100 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.99..106,134.03 rows=1 width=1,029) (actual time=432.657..763.100 rows=14,005 loops=1)

14. 18.137 689.663 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.70..106,126.39 rows=1 width=1,020) (actual time=432.636..689.663 rows=14,005 loops=1)

15. 11.934 643.516 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.42..106,126.05 rows=1 width=1,003) (actual time=432.622..643.516 rows=14,005 loops=1)

16. 1.471 609.976 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,016.13..106,117.45 rows=1 width=646) (actual time=432.603..609.976 rows=831 loops=1)

17. 2.862 583.575 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,015.84..106,108.84 rows=1 width=632) (actual time=432.514..583.575 rows=831 loops=1)

  • Join Filter: (c.cid = cal.cid)
18. 1.847 570.741 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,015.42..106,103.99 rows=1 width=621) (actual time=432.467..570.741 rows=831 loops=1)

19. 1.250 558.922 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,014.99..106,095.53 rows=1 width=621) (actual time=432.434..558.922 rows=831 loops=1)

20. 1.362 537.728 ↓ 831.0 831 1

Hash Join (cost=29,014.28..106,086.45 rows=1 width=589) (actual time=432.351..537.728 rows=831 loops=1)

  • Hash Cond: (cal.property_id = load_prop_1.property_id)
21. 119.038 536.343 ↓ 138.5 831 1

Bitmap Heap Scan on cached_application_logs cal (cost=29,012.78..106,084.92 rows=6 width=68) (actual time=432.304..536.343 rows=831 loops=1)

  • Recheck Cond: ((cid = 16033) AND (apply_through_post_date >= '2020-12-31'::date))
  • Filter: ((NOT is_post_date_ignored) AND (reporting_post_date < '2021-01-01 00:00:00'::timestamp without time zone) AND CASE WHEN (cancellation_list_item_id IS NULL) THEN true WHEN (hashed SubPlan 1) THEN false ELSE true END AND (occupancy_type_id <> 4) AND (lease_interval_type_id = 1) AND ((application_datetime)::date >= '2020-01-01'::date) AND ((application_datetime)::date < '2021-01-01 00:00:00'::timestamp without time zone) AND ((COALESCE(leasing_agent_id, 0) = ANY ('{0,444768,444730,514079,514078,514352,514503,514339,444707,514681,514114,513891,513892,514080,444729,514630,444537,444792,444722,514637,444706,514147,514138,444748,514552,514582,444837,444721,514629,513893,513907,514113,514568,514115,444725,444727,514200,514243,444726,514120,514276,514505,514494,514150,514181,444854,514532,444539,445106,444723,444746,444724,513902,445108,514141,444861,444728,514056,514595,445107}'::integer[])) OR (COALESCE(leasing_agent_id, 0) IS NULL)))
  • Rows Removed by Filter: 9110
  • Heap Blocks: exact=9577
22. 39.950 416.929 ↓ 0.0 0 1

BitmapAnd (cost=28,650.34..28,650.34 rows=19,828 width=0) (actual time=416.929..416.929 rows=0 loops=1)

23. 105.956 105.956 ↑ 1.0 419,355 1

Bitmap Index Scan on idx_cached_application_logs_temp2 (cost=0.00..8,075.20 rows=437,169 width=0) (actual time=105.956..105.956 rows=419,355 loops=1)

  • Index Cond: (cid = 16033)
24. 271.023 271.023 ↑ 1.2 941,845 1

Bitmap Index Scan on idx_cached_application_logs_apply_through_post_date (cost=0.00..20,574.88 rows=1,113,926 width=0) (actual time=271.023..271.023 rows=941,845 loops=1)

  • Index Cond: (apply_through_post_date >= '2020-12-31'::date)
25.          

SubPlan (for Bitmap Heap Scan)

26. 0.328 0.376 ↑ 1.3 3 1

Bitmap Heap Scan on list_items li (cost=5.46..362.43 rows=4 width=4) (actual time=0.069..0.376 rows=3 loops=1)

  • Recheck Cond: (cid = 16033)
  • Filter: (default_list_item_id = ANY ('{66,90}'::integer[]))
  • Rows Removed by Filter: 152
  • Heap Blocks: exact=33
27. 0.048 0.048 ↑ 1.0 155 1

Bitmap Index Scan on idx_list_items_cid_list_type_id (cost=0.00..5.46 rows=157 width=0) (actual time=0.048..0.048 rows=155 loops=1)

  • Index Cond: (cid = 16033)
28. 0.012 0.023 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=525) (actual time=0.023..0.023 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
29. 0.011 0.011 ↑ 1.0 22 1

Seq Scan on load_prop load_prop_1 (cost=0.00..1.22 rows=22 width=525) (actual time=0.004..0.011 rows=22 loops=1)

30. 4.998 19.944 ↑ 1.0 1 831

Nested Loop Left Join (cost=0.71..9.07 rows=1 width=36) (actual time=0.022..0.024 rows=1 loops=831)

31. 12.465 12.465 ↑ 1.0 1 831

Index Scan using idx_events_id on events e_1 (cost=0.43..8.45 rows=1 width=298) (actual time=0.015..0.015 rows=1 loops=831)

  • Index Cond: (id = cal.first_event_id)
  • Filter: (cid = 16033)
32. 2.481 2.481 ↑ 1.0 1 827

Index Scan using idx_event_results on event_results ers_1 (cost=0.28..0.32 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=827)

  • Index Cond: (id = e_1.event_result_id)
  • Filter: ((cid = 16033) AND (cid = e_1.cid))
33. 9.972 9.972 ↑ 1.0 1 831

Index Scan using idx_applicants_id on applicants app (cost=0.42..8.45 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=831)

  • Index Cond: (id = cal.primary_applicant_id)
  • Filter: ((cid = 16033) AND (cal.cid = cid))
34. 9.972 9.972 ↑ 1.0 1 831

Index Scan using idx_customers_id on customers c (cost=0.42..4.84 rows=1 width=23) (actual time=0.012..0.012 rows=1 loops=831)

  • Index Cond: (app.customer_id = id)
  • Filter: (cid = 16033)
35. 24.930 24.930 ↑ 1.0 1 831

Index Scan using idx_temp_leads_lease_interval_id on events_temp e_2 (cost=0.29..8.59 rows=1 width=26) (actual time=0.027..0.030 rows=1 loops=831)

  • Index Cond: (lease_interval_id = cal.lease_interval_id)
  • Filter: (((leasing_agent_activity)::text <> 'Not Assigned'::text) AND (cid = 16033) AND (row_number = 1) AND ((event_datetime <= cal.lease_approved_on) OR (cal.lease_approved_on IS NULL)))
  • Rows Removed by Filter: 16
36. 21.606 21.606 ↓ 4.2 17 831

Index Scan using idx_temp_leads_lease_interval_id on events_temp e (cost=0.29..8.56 rows=4 width=373) (actual time=0.003..0.026 rows=17 loops=831)

  • Index Cond: (lease_interval_id = cal.lease_interval_id)
  • Filter: ((cid = 16033) AND ((event_datetime <= cal.lease_approved_on) OR (cal.lease_approved_on IS NULL)) AND (cid = cal.cid))
  • Rows Removed by Filter: 0
37. 28.010 28.010 ↓ 0.0 0 14,005

Index Scan using idx_event_results on event_results ers (cost=0.28..0.32 rows=1 width=33) (actual time=0.002..0.002 rows=0 loops=14,005)

  • Index Cond: (id = e.event_result_id)
  • Filter: ((cid = 16033) AND (cid = e.cid))
38. 56.020 56.020 ↑ 1.0 1 14,005

Index Scan using idx_company_employees_id on company_employees ce (cost=0.28..7.64 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=14,005)

  • Index Cond: (id = cal.leasing_agent_id)
  • Filter: ((cid = 16033) AND (cid = cal.cid))
39. 28.010 168.060 ↑ 1.0 1 14,005

Aggregate (cost=17.04..17.05 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=14,005)

40. 28.691 140.050 ↑ 1.0 1 14,005

Nested Loop (cost=0.71..17.04 rows=1 width=14) (actual time=0.009..0.010 rows=1 loops=14,005)

41. 70.025 70.025 ↑ 1.0 1 14,005

Index Scan using idx_application_lead_sources_application_id on application_lead_sources als (cost=0.42..8.45 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=14,005)

  • Index Cond: (cal.application_id = application_id)
  • Filter: ((cid = 16033) AND CASE WHEN (cal.originating_lead_source_id <> cal.converting_lead_source_id) THEN (cal.originating_lead_source_id <> lead_source_id) ELSE true END)
  • Rows Removed by Filter: 0
42. 41.334 41.334 ↑ 1.0 1 13,778

Index Scan using idx_lead_sources_id on lead_sources ls (cost=0.28..8.30 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=13,778)

  • Index Cond: (id = als.lead_source_id)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = 16033))
43. 42.015 42.015 ↑ 1.0 1 14,005

Index Scan using idx_lead_sources_id on lead_sources ls_1 (cost=0.28..7.64 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=14,005)

  • Index Cond: (id = COALESCE(cal.converting_lead_source_id, cal.originating_lead_source_id))
  • Filter: ((cid = 16033) AND (cid = cal.cid))
44. 182.065 252.090 ↑ 1.0 1 14,005

Bitmap Heap Scan on application_stage_statuses ass (cost=1.65..7.19 rows=1 width=1,119) (actual time=0.013..0.018 rows=1 loops=14,005)

  • Recheck Cond: ((lease_interval_type_id = cal.lease_interval_type_id) AND (lease_interval_type_id = 1))
  • Filter: ((application_stage_id = cal.application_stage_id) AND (application_status_id = cal.application_status_id))
  • Rows Removed by Filter: 17
  • Heap Blocks: exact=98035
45. 70.025 70.025 ↑ 1.0 18 14,005

Bitmap Index Scan on idx_application_stage_status_lease_interval_type_id (cost=0.00..1.65 rows=18 width=0) (actual time=0.005..0.005 rows=18 loops=14,005)

  • Index Cond: ((lease_interval_type_id = cal.lease_interval_type_id) AND (lease_interval_type_id = 1))