explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iJme

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

GroupAggregate (cost=4,727,657.96..4,727,658.02 rows=1 width=444) (actual rows= loops=)

  • Group Key: mj.id, hue.original_bill_date, hue.most_recent_bill_date, ds."dead status", hrp.resolved_masterdata_dict_id, rrd.rr_date, bd.billable_date, tfd.tf_date, d.name, hm.ub92code, lct2."last client", hm.svcamt
  • Filter: ((COALESCE(max(hc.most_recent_bill_date), hue.most_recent_bill_date) < mj.first_viewed_date) OR (COALESCE(max(hc.most_recent_bill_date), hue.most_recent_bill_date) > mj.timely_limit) OR (COALESCE(max(hc.most_recent_bill_date), hue.most_recent_bill_date) IS NULL) OR ((COALESCE(max(hc.most_recent_bill_date), hue.most_recent_bill_date) >= mj.first_viewed_date) AND (COALESCE(max(hc.most_recent_bill_date), hue.most_recent_bill_date) <= mj.timely_limit) AND (hrp.resolved_masterdata_dict_id IS NULL)))
2.          

CTE rev_review_date

3. 0.000 0.000 ↓ 0.0

Unique (cost=4,708,093.67..4,708,093.68 rows=1 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=4,708,093.67..4,708,093.68 rows=1 width=24) (actual rows= loops=)

  • Sort Key: msh.mcj_id, msh.org_name, (min(msh.change_date))
5. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4,708,093.64..4,708,093.66 rows=1 width=24) (actual rows= loops=)

  • Group Key: msh.mcj_id, msh.org_name
6. 0.000 0.000 ↓ 0.0

Sort (cost=4,708,093.64..4,708,093.64 rows=1 width=24) (actual rows= loops=)

  • Sort Key: msh.mcj_id, msh.org_name
7. 0.000 0.000 ↓ 0.0

Gather (cost=1,141.99..4,708,093.63 rows=1 width=24) (actual rows= loops=)

  • Workers Planned: 9
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=142.00..4,707,093.53 rows=1 width=24) (actual rows= loops=)

  • Join Filter: (a."timestamp" < mj_1.timely_limit)
  • Filter: (a.id IS NULL)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=141.43..4,660,535.78 rows=2,565 width=28) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=140.86..4,649,859.79 rows=2,565 width=24) (actual rows= loops=)

  • Hash Cond: ((msh.org_name)::text = (o_1.name)::text)
11. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on missing_status_history msh (cost=0.00..4,649,680.80 rows=14,388 width=24) (actual rows= loops=)

  • Filter: ((current_status)::text = 'RevReview'::text)
12. 0.000 0.000 ↓ 0.0

Hash (cost=139.90..139.90 rows=77 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on organizations o_1 (cost=0.00..139.90 rows=77 width=12) (actual rows= loops=)

  • Filter: ((hospital IS TRUE) AND (is_operational IS TRUE) AND ((active_flag)::text = 'Y'::text))
14. 0.000 0.000 ↓ 0.0

Index Scan using missing_charges_jess_pkx on missing_charges_jess mj_1 (cost=0.56..4.16 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = msh.mcj_id)
15. 0.000 0.000 ↓ 0.0

Index Scan using audit_idx4 on audit a (cost=0.57..18.14 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (msh.mcj_id = missingchargeid)
  • Filter: (((tostatus)::text ~~ 'NB%'::text) AND ((tostatus)::text <> 'NB - Timely Filing'::text))
16.          

CTE dead_status1

17. 0.000 0.000 ↓ 0.0

Unique (cost=4.64..4.65 rows=1 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=4.64..4.64 rows=1 width=12) (actual rows= loops=)

  • Sort Key: mj_2.id, mj_2.timely_limit
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.59..4.63 rows=1 width=12) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual rows= loops=)

  • Group Key: rev_review_date.mcj_id
21. 0.000 0.000 ↓ 0.0

CTE Scan on rev_review_date (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using missing_charges_jess_pkx on missing_charges_jess mj_2 (cost=0.56..4.59 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = rev_review_date.mcj_id)
  • Filter: (timely_limit > '2016-01-01'::date)
23.          

CTE dead_status2

24. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=65.11..65.41 rows=15 width=16) (actual rows= loops=)

  • Group Key: msh_1.mcj_id
25. 0.000 0.000 ↓ 0.0

Sort (cost=65.11..65.15 rows=15 width=20) (actual rows= loops=)

  • Sort Key: msh_1.mcj_id
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..64.82 rows=15 width=20) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

CTE Scan on dead_status1 ds1 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_1 (cost=0.57..64.65 rows=15 width=20) (actual rows= loops=)

  • Index Cond: ((mcj_id = ds1.id) AND (current_status IS NOT NULL))
  • Filter: (change_date < ds1.timely_limit)
29.          

CTE dead_status3

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,217.52 rows=4 width=17) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

CTE Scan on dead_status2 ds2 (cost=0.00..0.30 rows=15 width=16) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_2 (cost=0.57..81.14 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (mcj_id = ds2.mcj_id)
  • Filter: (ds2.max = (change_date + change_time))
33.          

CTE last_client_touch1

34. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,312.03..1,316.19 rows=208 width=16) (actual rows= loops=)

  • Group Key: msh_3.mcj_id
35. 0.000 0.000 ↓ 0.0

Sort (cost=1,312.03..1,312.55 rows=208 width=20) (actual rows= loops=)

  • Sort Key: msh_3.mcj_id
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..1,304.02 rows=208 width=20) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,218.04 rows=289 width=33) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on dead_status2 ds2_1 (cost=0.00..0.30 rows=15 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_3 (cost=0.57..80.99 rows=19 width=33) (actual rows= loops=)

  • Index Cond: (mcj_id = ds2_1.mcj_id)
  • Filter: (change_date <= ds2_1.max)
40. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users u (cost=0.28..0.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((username)::text = (msh_3.update_user)::text)
  • Filter: (staff IS FALSE)
41.          

CTE last_client_touch2

42. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=16,887.95..16,888.82 rows=35 width=48) (actual rows= loops=)

  • Group Key: lct1.mcj_id, lct1.max
43. 0.000 0.000 ↓ 0.0

Sort (cost=16,887.95..16,888.04 rows=35 width=29) (actual rows= loops=)

  • Sort Key: lct1.mcj_id, lct1.max
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16,887.05 rows=35 width=29) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16,872.47 rows=49 width=29) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

CTE Scan on last_client_touch1 lct1 (cost=0.00..4.16 rows=208 width=16) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_4 (cost=0.57..81.09 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (mcj_id = lct1.mcj_id)
  • Filter: ((update_user IS NOT NULL) AND (lct1.max = (change_date + change_time)))
48. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users u_1 (cost=0.28..0.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((username)::text = (msh_4.update_user)::text)
  • Filter: (staff IS FALSE)
49.          

CTE timely_filing_date

50. 0.000 0.000 ↓ 0.0

Unique (cost=10.21..10.26 rows=5 width=24) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=10.21..10.23 rows=5 width=24) (actual rows= loops=)

  • Sort Key: msh_5.mcj_id, msh_5.org_name, (min(msh_5.change_date))
52. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10.06..10.16 rows=5 width=24) (actual rows= loops=)

  • Group Key: msh_5.mcj_id, msh_5.org_name
53. 0.000 0.000 ↓ 0.0

Sort (cost=10.06..10.07 rows=5 width=24) (actual rows= loops=)

  • Sort Key: msh_5.mcj_id, msh_5.org_name
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.59..10.00 rows=5 width=24) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual rows= loops=)

  • Group Key: rev_review_date_1.mcj_id
56. 0.000 0.000 ↓ 0.0

CTE Scan on rev_review_date rev_review_date_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_5 (cost=0.57..9.91 rows=5 width=24) (actual rows= loops=)

  • Index Cond: ((mcj_id = rev_review_date_1.mcj_id) AND ((current_status)::text = 'NB - Timely Filing'::text))
58.          

CTE billable_date

59. 0.000 0.000 ↓ 0.0

Unique (cost=23.27..23.28 rows=1 width=24) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Sort (cost=23.27..23.27 rows=1 width=24) (actual rows= loops=)

  • Sort Key: msh_6.mcj_id, msh_6.org_name, (min(msh_6.change_date))
61. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=23.23..23.26 rows=1 width=24) (actual rows= loops=)

  • Group Key: msh_6.mcj_id, msh_6.org_name
62. 0.000 0.000 ↓ 0.0

Sort (cost=23.23..23.24 rows=1 width=24) (actual rows= loops=)

  • Sort Key: msh_6.mcj_id, msh_6.org_name
63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.69..23.22 rows=1 width=24) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.11..0.16 rows=5 width=8) (actual rows= loops=)

  • Group Key: timely_filing_date.mcj_id
65. 0.000 0.000 ↓ 0.0

CTE Scan on timely_filing_date (cost=0.00..0.10 rows=5 width=8) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_6 (cost=0.57..4.59 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((mcj_id = timely_filing_date.mcj_id) AND ((current_status)::text = 'Billable'::text))
67.          

CTE billed

68. 0.000 0.000 ↓ 0.0

Unique (cost=6.24..6.25 rows=1 width=20) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Sort (cost=6.24..6.24 rows=1 width=20) (actual rows= loops=)

  • Sort Key: mj_3.id, msh_7.org_name
70. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.16..6.23 rows=1 width=20) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.59..4.63 rows=1 width=20) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual rows= loops=)

  • Group Key: rev_review_date_2.mcj_id
73. 0.000 0.000 ↓ 0.0

CTE Scan on rev_review_date rev_review_date_2 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Index Scan using missing_charges_jess_pkx on missing_charges_jess mj_3 (cost=0.56..4.58 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = rev_review_date_2.mcj_id)
75. 0.000 0.000 ↓ 0.0

Index Scan using msh_index3 on missing_status_history msh_7 (cost=0.57..1.59 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((mcj_id = mj_3.id) AND ((current_status)::text = ANY ('{Billed,"Billed - Aged"}'::text[])))
  • Filter: (change_date <= mj_3.timely_limit)
76. 0.000 0.000 ↓ 0.0

Sort (cost=31.89..31.89 rows=1 width=444) (actual rows= loops=)

  • Sort Key: mj.id, hue.original_bill_date, hue.most_recent_bill_date, ds."dead status", hrp.resolved_masterdata_dict_id, rrd.rr_date, bd.billable_date, tfd.tf_date, d.name, hm.ub92code, lct2."last client", hm.svcamt
77. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..31.88 rows=1 width=444) (actual rows= loops=)

  • Join Filter: (mj.id = lct2.mcj_id)
78. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..30.74 rows=1 width=412) (actual rows= loops=)

  • Join Filter: ((hrp.resolved_masterdata_dict_id = 2) AND ((hm.organization_name)::text = (mj.organization_name)::text))
79. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..26.54 rows=1 width=410) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=1.13..20.95 rows=1 width=394) (actual rows= loops=)

  • Join Filter: (mj.id = b.id)
81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..20.91 rows=1 width=394) (actual rows= loops=)

  • Join Filter: (mj.id = ds.mcj_id)
82. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.13..20.79 rows=1 width=304) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..17.30 rows=1 width=292) (actual rows= loops=)

  • Join Filter: (((mj.organization_name)::text = (bd.org_name)::text) AND (mj.id = bd.mcj_id))
84. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..17.26 rows=1 width=288) (actual rows= loops=)

  • Join Filter: (((mj.organization_name)::text = (tfd.org_name)::text) AND (mj.id = tfd.mcj_id))
85. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..17.14 rows=1 width=284) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..12.74 rows=1 width=280) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..8.92 rows=1 width=272) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..4.62 rows=1 width=272) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

CTE Scan on rev_review_date rrd (cost=0.00..0.02 rows=1 width=110) (actual rows= loops=)

  • Filter: ((org_name)::text = 'OHSUHOSPITAL'::text)
90. 0.000 0.000 ↓ 0.0

Index Scan using missing_charges_jess_pkx on missing_charges_jess mj (cost=0.56..4.60 rows=1 width=260) (actual rows= loops=)

  • Index Cond: (id = rrd.mcj_id)
  • Filter: (((status)::text <> ALL ('{Invoiced,"Payment Activity"}'::text[])) AND (service_date >= '2019-01-01'::date) AND (service_date <= '2020-08-31'::date) AND ((organization_name)::text = 'OHSUHOSPITAL'::text) AND (timely_limit <= CURRENT_DATE))
91. 0.000 0.000 ↓ 0.0

Index Only Scan using organizations_name_idx1 on organizations o (cost=0.27..4.29 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (name = 'OHSUHOSPITAL'::text)
92. 0.000 0.000 ↓ 0.0

Append (cost=0.00..3.80 rows=2 width=29) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on hospital_uhc_encounter hue (cost=0.00..0.00 rows=1 width=138) (actual rows= loops=)

  • Filter: (((organization_name)::text = 'OHSUHOSPITAL'::text) AND ((mj.acctno)::text = (acctno)::text))
94. 0.000 0.000 ↓ 0.0

Index Scan using hospital_account_business_key_idx on hospital_account hue_1 (cost=0.56..3.80 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (((organization_name)::text = 'OHSUHOSPITAL'::text) AND ((acctno)::text = (mj.acctno)::text))
95. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.37 rows=3 width=25) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Seq Scan on hospital_claim hc (cost=0.00..0.00 rows=1 width=134) (actual rows= loops=)

  • Filter: (((organization_name)::text = 'OHSUHOSPITAL'::text) AND ((mj.organization_name)::text = (organization_name)::text) AND ((mj.acctno)::text = (acctno)::text))
97. 0.000 0.000 ↓ 0.0

Index Scan using hospital_claim_acctno_idx on hospital_claim hc_1 (cost=0.43..4.37 rows=2 width=25) (actual rows= loops=)

  • Index Cond: (((mj.organization_name)::text = (organization_name)::text) AND ((organization_name)::text = 'OHSUHOSPITAL'::text) AND ((mj.acctno)::text = (acctno)::text))
98. 0.000 0.000 ↓ 0.0

CTE Scan on timely_filing_date tfd (cost=0.00..0.11 rows=1 width=110) (actual rows= loops=)

  • Filter: ((org_name)::text = 'OHSUHOSPITAL'::text)
99. 0.000 0.000 ↓ 0.0

CTE Scan on billable_date bd (cost=0.00..0.02 rows=1 width=110) (actual rows= loops=)

  • Filter: ((org_name)::text = 'OHSUHOSPITAL'::text)
100. 0.000 0.000 ↓ 0.0

Index Scan using department_pk1 on department d (cost=0.29..3.48 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (mj.department_id = id)
101. 0.000 0.000 ↓ 0.0

CTE Scan on dead_status3 ds (cost=0.00..0.12 rows=1 width=106) (actual rows= loops=)

  • Filter: (("dead status")::text <> ALL ('{Invoiced,InternalReview,"FSR Cleanup",Missing,Matched,"NB - Timely Filing",Pending}'::text[]))
102. 0.000 0.000 ↓ 0.0

CTE Scan on billed b (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Index Only Scan using rule_hospital_resolved_prediction_uidx1 on rule_hospital_resolved_prediction hrp (cost=0.42..5.56 rows=3 width=24) (actual rows= loops=)

  • Index Cond: (prediction_id = mj.id)
104. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.17 rows=2 width=31) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Seq Scan on hospital_master hm (cost=0.00..0.00 rows=1 width=156) (actual rows= loops=)

  • Filter: (((organization_name)::text = 'OHSUHOSPITAL'::text) AND (id = hrp.resolved_master_id))
106. 0.000 0.000 ↓ 0.0

Index Scan using hospital_master_pkey on hospital_master hm_1 (cost=0.56..4.17 rows=1 width=31) (actual rows= loops=)

  • Index Cond: (id = hrp.resolved_master_id)
  • Filter: ((organization_name)::text = 'OHSUHOSPITAL'::text)
107. 0.000 0.000 ↓ 0.0

CTE Scan on last_client_touch2 lct2 (cost=0.00..0.70 rows=35 width=40) (actual rows= loops=)