explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BOWj

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=52,571,950.26..52,579,867.52 rows=121,804 width=266) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=52,571,950.26..52,572,254.77 rows=121,804 width=266) (actual rows= loops=)

  • Sort Key: twi.work_item_id, cbc.case_id, cp.application_dt, cln.first_name, cln.last_name, twi.status_cd, fll1.lookup_short_val, fll2.lookup_short_val, twi.create_user_id, twi.create_dt, twi.assigned_dt, twi.update_dt, twi.work_item_description, twi.assigned_to, twi.work_item_type_cd, twi.work_item_sub_type_cd, mu.firstname, mu.lastname, twi.work_item_due_dt, twi.disposition_source, cbc.case_status_cd, twi.disposition_dt, cvo.due_dt, crd.redetermination_status_cd, crd.redetermination_status_dt
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,273.27..52,561,661.35 rows=121,804 width=266) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,272.98..52,520,070.59 rows=120,815 width=256) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,272.70..52,482,962.01 rows=120,815 width=242) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,272.28..52,406,513.91 rows=120,815 width=235) (actual rows= loops=)

  • Hash Cond: (cim.individual_id = cln.individual_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,267.95..52,406,056.50 rows=120,815 width=226) (actual rows= loops=)

  • Join Filter: (upper(ltrim(rtrim((twi.work_item_type_cd)::text))) = upper(ltrim(rtrim((fll2.lkp_desc)::text))))
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,267.95..52,370,058.72 rows=120,815 width=202) (actual rows= loops=)

  • Join Filter: (upper(ltrim(rtrim((twi.work_item_sub_type_cd)::text))) = upper(ltrim(rtrim((fll1.lkp_desc)::text))))
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,267.95..52,302,697.25 rows=120,815 width=178) (actual rows= loops=)

  • Hash Cond: ((twi.benefits_case_id = cvo.benefits_case_id) AND ((SubPlan 2) = cvo.create_dt))
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.00..99,266.24 rows=120,815 width=178) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.71..57,355.40 rows=120,815 width=174) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using t_work_item_upper_work_item_type_cd_idx on t_work_item twi (cost=0.42..15,840.64 rows=120,815 width=159) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using cs_benefits_case_pk on cs_benefits_case cbc (cost=0.29..0.33 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (benefits_case_id = twi.benefits_case_id)
14. 0.000 0.000 ↓ 0.0

Index Scan using cs_individual_membership_benefits_case_id_fkey_idx on cs_individual_membership cim (cost=0.29..0.34 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (twi.benefits_case_id = benefits_case_id)
  • Filter: ((delete_ind = 'N'::bpchar) AND ((head_of_household_ind)::text = 'Y'::text))
15. 0.000 0.000 ↓ 0.0

Hash (cost=1,612.38..1,612.38 rows=43,638 width=16) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on cl_verification_outs cvo (cost=0.00..1,612.38 rows=43,638 width=16) (actual rows= loops=)

17.          

SubPlan (for Hash Left Join)

18. 0.000 0.000 ↓ 0.0

Result (cost=864.13..864.14 rows=1 width=8) (actual rows= loops=)

19.          

Initplan (for Result)

20. 0.000 0.000 ↓ 0.0

Limit (cost=0.29..864.13 rows=1 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using cl_verification_outs_create_dt on cl_verification_outs (cost=0.29..6,047.14 rows=7 width=8) (actual rows= loops=)

  • Index Cond: (create_dt IS NOT NULL)
  • Filter: (benefits_case_id = cbc.benefits_case_id)
22. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..611.22 rows=17 width=27) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on fw_localized_lookup1 fll1 (cost=0.00..611.14 rows=17 width=27) (actual rows= loops=)

  • Filter: ((lookup_type_cd)::text = 'WorkItemSubType'::text)
24. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..659.41 rows=9 width=27) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on fw_localized_lookup1 fll2 (cost=0.00..659.37 rows=9 width=27) (actual rows= loops=)

  • Filter: (((lookup_type_cd)::text = 'WorkItemType'::text) AND ((locale_cd)::text = 'en_US'::text))
26. 0.000 0.000 ↓ 0.0

Hash (cost=4.32..4.32 rows=1 width=17) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Index Scan using cl_name_id_2 on cl_name cln (cost=0.29..4.32 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (effective_end_dt = '2019-08-08'::date)
  • Filter: ((delete_ind = 'N'::bpchar) AND ((primary_ind)::text = 'Y'::text))
28. 0.000 0.000 ↓ 0.0

Index Scan using cs_redetermination_detail_id_fkey_idx on cs_redetermination_detail crd (cost=0.42..0.62 rows=1 width=11) (actual rows= loops=)

  • Index Cond: (twi.benefits_case_id = benefits_case_id)
  • Filter: ((effective_end_dt = '9999-12-31'::date) AND (delete_ind = 'N'::bpchar))
29. 0.000 0.000 ↓ 0.0

Index Scan using mdt_users_pk on mdt_users mu (cost=0.28..0.30 rows=1 width=45) (actual rows= loops=)

  • Index Cond: ((twi.assigned_to)::text = (uniqueid)::text)
30. 0.000 0.000 ↓ 0.0

Index Scan using cs_program_benefits_case_id_fkey_idx on cs_program cp (cost=0.29..0.33 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (twi.benefits_case_id = benefits_case_id)