explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NFIb

Settings
# exclusive inclusive rows x rows loops node
1. 585.159 5,963.131 ↓ 831.0 831 1

GroupAggregate (cost=104,672.49..104,676.53 rows=1 width=1,002) (actual time=5,367.675..5,963.131 rows=831 loops=1)

  • Group Key: cal.id, cal.cid, load_prop.property_id, load_prop.property_name, load_prop.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
2. 113.346 5,377.972 ↓ 14,005.0 14,005 1

Sort (cost=104,672.49..104,672.50 rows=1 width=2,361) (actual time=5,366.423..5,377.972 rows=14,005 loops=1)

  • Sort Key: cal.id, load_prop.property_id, load_prop.property_name, load_prop.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: 30096kB
3. 4,092.355 5,264.626 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,034.60..104,672.48 rows=1 width=2,361) (actual time=413.131..5,264.626 rows=14,005 loops=1)

4. 29.227 990.206 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,034.46..104,669.06 rows=1 width=2,341) (actual time=412.202..990.206 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))
5. 24.163 918.964 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,034.18..104,663.00 rows=1 width=2,349) (actual time=412.178..918.964 rows=14,005 loops=1)

6. 25.922 726.741 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,017.14..104,645.93 rows=1 width=2,317) (actual time=412.084..726.741 rows=14,005 loops=1)

7. 18.496 658.804 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.86..104,639.51 rows=1 width=2,304) (actual time=412.055..658.804 rows=14,005 loops=1)

8. 17.716 598.293 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.71..104,639.34 rows=1 width=1,035) (actual time=412.013..598.293 rows=14,005 loops=1)

9. 12.292 552.567 ↓ 14,005.0 14,005 1

Nested Loop Left Join (cost=29,016.43..104,639.01 rows=1 width=1,018) (actual time=411.993..552.567 rows=14,005 loops=1)

10. 0.934 521.162 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,016.14..104,630.40 rows=1 width=657) (actual time=411.969..521.162 rows=831 loops=1)

11. 2.125 498.622 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,015.85..104,621.80 rows=1 width=643) (actual time=411.886..498.622 rows=831 loops=1)

  • Join Filter: (c.cid = cal.cid)
12. 1.678 490.680 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,015.42..104,616.94 rows=1 width=632) (actual time=411.850..490.680 rows=831 loops=1)

13. 1.952 483.185 ↓ 831.0 831 1

Nested Loop Left Join (cost=29,015.00..104,608.48 rows=1 width=632) (actual time=411.815..483.185 rows=831 loops=1)

14. 1.426 466.275 ↓ 831.0 831 1

Hash Join (cost=29,014.29..104,599.40 rows=1 width=600) (actual time=411.717..466.275 rows=831 loops=1)

  • Hash Cond: (cal.property_id = load_prop.property_id)
15. 66.770 464.822 ↓ 43.7 831 1

Bitmap Heap Scan on cached_application_logs cal (cost=29,012.79..104,597.83 rows=19 width=79) (actual time=411.667..464.822 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))
  • Rows Removed by Filter: 9110
  • Heap Blocks: exact=9577
16. 40.582 397.888 ↓ 0.0 0 1

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

17. 104.035 104.035 ↑ 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=104.035..104.035 rows=419,355 loops=1)

  • Index Cond: (cid = 16033)
18. 253.271 253.271 ↑ 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=253.271..253.271 rows=941,845 loops=1)

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

SubPlan (for Bitmap Heap Scan)

20. 0.133 0.164 ↑ 1.3 3 1

Bitmap Heap Scan on list_items li (cost=5.46..362.43 rows=4 width=4) (actual time=0.053..0.164 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
21. 0.031 0.031 ↑ 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.031..0.031 rows=155 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.015 0.015 ↑ 1.0 22 1

Seq Scan on load_prop (cost=0.00..1.22 rows=22 width=525) (actual time=0.009..0.015 rows=22 loops=1)

24. 3.336 14.958 ↑ 1.0 1 831

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

25. 9.141 9.141 ↑ 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.010..0.011 rows=1 loops=831)

  • Index Cond: (id = cal.first_event_id)
  • Filter: (cid = 16033)
26. 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))
27. 5.817 5.817 ↑ 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.007..0.007 rows=1 loops=831)

  • Index Cond: (id = cal.primary_applicant_id)
  • Filter: ((cid = 16033) AND (cal.cid = cid))
28. 5.817 5.817 ↑ 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.007..0.007 rows=1 loops=831)

  • Index Cond: (app.customer_id = id)
  • Filter: (cid = 16033)
29. 21.606 21.606 ↑ 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.022..0.026 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
30. 19.113 19.113 ↓ 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=377) (actual time=0.003..0.023 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
31. 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))
32. 42.015 42.015 ↑ 1.0 1 14,005

Index Scan using pk_event_types on event_types et (cost=0.15..0.17 rows=1 width=1,273) (actual time=0.003..0.003 rows=1 loops=14,005)

  • Index Cond: (id = e.event_type_id)
33. 42.015 42.015 ↑ 1.0 1 14,005

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

  • Index Cond: (id = cal.leasing_agent_id)
  • Filter: ((cid = 16033) AND (cid = cal.cid))
34. 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)

35. 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)

36. 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
37. 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))
38. 42.015 42.015 ↑ 1.0 1 14,005

Index Scan using idx_lead_sources_id on lead_sources ls_1 (cost=0.28..5.99 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))
39. 182.065 182.065 ↑ 1.0 1 14,005

Index Scan using idx_application_stage_status_lease_interval_type_id on application_stage_statuses ass (cost=0.14..2.91 rows=1 width=1,119) (actual time=0.007..0.013 rows=1 loops=14,005)

  • Index 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