explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TVBu

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

Limit (cost=2,753,885.96..2,753,931.96 rows=18,403 width=810) (actual rows= loops=)

2.          

CTE tx_active_master_provider_file_dcm_provider_lookup_combined

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,737,170.06..2,739,010.33 rows=184,027 width=778) (actual rows= loops=)

  • Group Key: b_tpi.base_tpi_record, b_tpi.provider_id_base, b_tpi.name_classification, (CASE WHEN (b_tpi.name_classification = 'I'::text) THEN 'Individual'::text WHEN (b_tpi.name_classification = 'O'::text) THEN 'Organization/Group'::text ELSE b_tpi.name_classification END), b_tpi.provider_name, (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi.provider_name, ' '::text, 2)) ELSE split_part(b_tpi.provider_name, ' '::text, 1) END), (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 3) ELSE split_part(b_tpi.provider_name, ' '::text, 2) END), (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 4) ELSE split_part(b_tpi.provider_name, ' '::text, 3) END), ("left"((pfp.medicaid_id)::text, 7)), p.id, p.npi, npi_api.npi_api, (CASE WHEN ((pfp.id IS NOT NULL) AND (p.id IS NOT NULL) AND (upper(CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi.provider_name, ' '::text, 2)) ELSE split_part(b_tpi.provider_name, ' '::text, 1) END) = upper((p.lname)::text)) AND (upper(CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 3) ELSE split_part(b_tpi.provider_name, ' '::text, 2) END) = upper((p.fname)::text)) AND (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 4) ELSE split_part(b_tpi.provider_name, ' '::text, 3) END = upper((p.mname)::text))) THEN 'Medicaid ID Match and DT Provider Name Match'::text WHEN ((pfp.id IS NOT NULL) AND (p.id IS NOT NULL)) THEN 'Medicaid ID Match and DT Provider Name Not Match'::text ELSE 'Provider Not in DT'::text END), p.lname, p.fname, p.mname, ('Active'::text), v."Active Load Date", (now()), ('2019-02-11'::date), (CASE WHEN (cardinality(v."TX TPI") < 10) THEN array_to_string(v."TX TPI", ', '::text) ELSE ('Too many to list count = '::text || (cardinality(v."TX TPI"))::text) END)
4.          

CTE hot_date

5. 0.000 0.000 ↓ 0.0

Result (cost=102.65..102.66 rows=1 width=72) (actual rows= loops=)

6.          

Initplan (for Result)

7. 0.000 0.000 ↓ 0.0

Result (cost=0.46..0.47 rows=1 width=4) (actual rows= loops=)

8.          

Initplan (for Result)

9. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..0.46 rows=1 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using tx_base_tpi_trans_date on tx_base_tpi (cost=0.43..196,893.08 rows=7,278,163 width=4) (actual rows= loops=)

  • Index Cond: ((trans_date IS NOT NULL) AND (trans_date <= '2019-02-11 00:00:00'::timestamp without time zone))
11. 0.000 0.000 ↓ 0.0

Result (cost=0.46..0.47 rows=1 width=4) (actual rows= loops=)

12.          

Initplan (for Result)

13. 0.000 0.000 ↓ 0.0

Limit (cost=0.44..0.46 rows=1 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using tx_base_tpi_excluded_trans_date on tx_base_tpi_excluded (cost=0.44..154,728.20 rows=5,741,288 width=4) (actual rows= loops=)

  • Index Cond: ((trans_date IS NOT NULL) AND (trans_date <= '2019-02-11 00:00:00'::timestamp without time zone))
15. 0.000 0.000 ↓ 0.0

Aggregate (cost=5.20..5.22 rows=1 width=32) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on tx_dept_health_counties (cost=0.00..4.56 rows=256 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Aggregate (cost=96.48..96.49 rows=1 width=32) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on taxonomy_codes (cost=0.00..96.47 rows=1 width=10) (actual rows= loops=)

  • Filter: ((("Grouping")::text = 'Dental Providers'::text) AND ((("Specialization")::text = ANY ('{Dentist,"Dentist Anesthesiologist",Denturist,Endodontics,"General Practice","Oral and Maxillofacial Pathology","Oral and Maxillofacial Radiology","Oral and Maxillofacial Surgery","Oral Medicinist","Orthodontics and Dentofacial Orthopedics","Pediatric Dentistry",Periodontics,Prosthodontics}'::text[])) OR (("Classification")::text = ANY ('{Dentist,"Dentist Anesthesiologist",Denturist,Endodontics,"General Practice","Oral and Maxillofacial Pathology","Oral and Maxillofacial Radiology","Oral and Maxillofacial Surgery","Oral Medicinist","Orthodontics and Dentofacial Orthopedics","Pediatric Dentistry",Periodontics,Prosthodontics}'::text[]))))
19.          

CTE hot_tpi0

20. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.03..0.12 rows=10 width=32) (actual rows= loops=)

  • Group Key: provider_id_base
21. 0.000 0.000 ↓ 0.0

Append (cost=0.00..0.00 rows=10 width=32) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
24. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
25. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
26. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
27. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
28. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
29. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
30. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
31. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=8) (actual rows= loops=)

  • One-Time Filter: false
32.          

CTE hot_tpi1

33. 0.000 0.000 ↓ 0.0

HashAggregate (cost=172,156.15..173,555.71 rows=139,956 width=32) (actual rows= loops=)

  • Group Key: hot_tpi0_2.provider_id_base
34. 0.000 0.000 ↓ 0.0

Append (cost=48.00..171,806.26 rows=139,956 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

HashAggregate (cost=48.00..48.10 rows=10 width=32) (actual rows= loops=)

  • Group Key: hot_tpi0_2.provider_id_base
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..47.87 rows=51 width=32) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..47.35 rows=50 width=36) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi0 hot_tpi0_2 (cost=0.00..0.20 rows=10 width=32) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Only Scan using tx_taxonomy_trans_date_provider_id_base_suffix on tx_taxonomy tax (cost=0.56..4.66 rows=5 width=12) (actual rows= loops=)

  • Index Cond: ((trans_date = hd."Active Load Date") AND (provider_id_base = hot_tpi0_2.provider_id_base))
41. 0.000 0.000 ↓ 0.0

HashAggregate (cost=27.12..27.22 rows=10 width=32) (actual rows= loops=)

  • Group Key: hot_tpi0_3.provider_id_base
42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..27.04 rows=32 width=32) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_1 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..26.72 rows=30 width=36) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi0 hot_tpi0_3 (cost=0.00..0.20 rows=10 width=32) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Index Only Scan using tx_taxonomy_excluded_trans_date_provider_id_base_suffix on tx_taxonomy_excluded tax_e (cost=0.56..2.62 rows=3 width=12) (actual rows= loops=)

  • Index Cond: ((trans_date = hd_1."Excluded Load Date") AND (provider_id_base = hot_tpi0_3.provider_id_base))
47. 0.000 0.000 ↓ 0.0

HashAggregate (cost=126,408.43..127,054.38 rows=64,595 width=8) (actual rows= loops=)

  • Group Key: tax_1.provider_id_base
48.          

Initplan (for HashAggregate)

49. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.23..0.24 rows=1 width=8) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi0 hot_tpi0_1 (cost=0.00..0.20 rows=10 width=0) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Result (cost=0.57..125,992.89 rows=166,121 width=8) (actual rows= loops=)

  • One-Time Filter: ($9 = 0)
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..125,992.89 rows=166,121 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_2 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using tx_taxonomy_trans_date on tx_taxonomy tax_1 (cost=0.56..124,331.66 rows=166,121 width=30) (actual rows= loops=)

  • Index Cond: (trans_date = hd_2."Active Load Date")
  • Filter: CASE WHEN (cardinality(hd_2.taxonomy_codes_filter) > 0) THEN ((taxonomy_1 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_2 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_4 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_5 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_6 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_7 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_8 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_9 = ANY (hd_2.taxonomy_codes_filter)) OR (taxonomy_10 = ANY (hd_2.taxonomy_codes_filter))) ELSE true END
55. 0.000 0.000 ↓ 0.0

HashAggregate (cost=42,523.59..43,277.00 rows=75,341 width=8) (actual rows= loops=)

  • Group Key: tax_e_1.provider_id_base
56.          

Initplan (for HashAggregate)

57. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.23..0.24 rows=1 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi0 (cost=0.00..0.20 rows=10 width=0) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Result (cost=0.44..42,226.68 rows=118,669 width=8) (actual rows= loops=)

  • One-Time Filter: ($8 = 0)
60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.44..42,226.68 rows=118,669 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_3 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Scan using tx_taxonomy_excluded_trans_date on tx_taxonomy_excluded tax_e_1 (cost=0.44..41,039.97 rows=118,669 width=31) (actual rows= loops=)

  • Index Cond: (trans_date = hd_3."Excluded Load Date")
  • Filter: CASE WHEN (cardinality(hd_3.taxonomy_codes_filter) > 0) THEN ((taxonomy_1 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_2 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_4 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_5 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_6 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_7 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_8 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_9 = ANY (hd_3.taxonomy_codes_filter)) OR (taxonomy_10 = ANY (hd_3.taxonomy_codes_filter))) ELSE true END
63.          

CTE hot_tpi

64. 0.000 0.000 ↓ 0.0

HashAggregate (cost=446,799.24..450,279.78 rows=348,054 width=32) (actual rows= loops=)

  • Group Key: suffix.provider_id_base
65. 0.000 0.000 ↓ 0.0

Append (cost=155,911.60..445,929.10 rows=348,054 width=32) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

HashAggregate (cost=155,911.60..156,549.92 rows=63,832 width=8) (actual rows= loops=)

  • Group Key: suffix.provider_id_base
67.          

Initplan (for HashAggregate)

68. 0.000 0.000 ↓ 0.0

Aggregate (cost=3,149.01..3,149.02 rows=1 width=8) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 hot_tpi1_3 (cost=0.00..2,799.12 rows=139,956 width=0) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Result (cost=124,769.55..151,852.50 rows=364,032 width=8) (actual rows= loops=)

  • One-Time Filter: ($22 > 0)
71. 0.000 0.000 ↓ 0.0

Hash Join (cost=124,769.55..151,852.50 rows=364,032 width=8) (actual rows= loops=)

  • Hash Cond: (tpi1.provider_id_base = suffix.provider_id_base)
72. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 tpi1 (cost=0.00..2,799.12 rows=139,956 width=32) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=122,694.17..122,694.17 rows=166,030 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..122,694.17 rows=166,030 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_4 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Index Scan using tx_suffix_trans_date on tx_suffix suffix (cost=0.56..121,033.85 rows=166,030 width=16) (actual rows= loops=)

  • Index Cond: (trans_date = hd_4."Active Load Date")
  • Filter: CASE WHEN (cardinality(hd_4.county_codes) > 0) THEN (tdh_county_code = ANY (hd_4.county_codes)) ELSE true END
77. 0.000 0.000 ↓ 0.0

HashAggregate (cost=91,858.06..92,960.01 rows=110,195 width=8) (actual rows= loops=)

  • Group Key: suffix_e.provider_id_base
78.          

Initplan (for HashAggregate)

79. 0.000 0.000 ↓ 0.0

Aggregate (cost=3,149.01..3,149.02 rows=1 width=8) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 hot_tpi1_2 (cost=0.00..2,799.12 rows=139,956 width=0) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Result (cost=64,621.55..87,810.31 rows=359,492 width=8) (actual rows= loops=)

  • One-Time Filter: ($21 > 0)
82. 0.000 0.000 ↓ 0.0

Hash Join (cost=64,621.55..87,810.31 rows=359,492 width=8) (actual rows= loops=)

  • Hash Cond: (tpi1_1.provider_id_base = suffix_e.provider_id_base)
83. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 tpi1_1 (cost=0.00..2,799.12 rows=139,956 width=32) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=61,083.45..61,083.45 rows=283,048 width=8) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..61,083.45 rows=283,048 width=8) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_5 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Index Scan using tx_suffix_excluded_trans_date on tx_suffix_excluded suffix_e (cost=0.56..58,252.95 rows=283,048 width=16) (actual rows= loops=)

  • Index Cond: (trans_date = hd_5."Excluded Load Date")
  • Filter: CASE WHEN (cardinality(hd_5.county_codes) > 0) THEN (tdh_county_code = ANY (hd_5.county_codes)) ELSE true END
88. 0.000 0.000 ↓ 0.0

HashAggregate (cost=126,258.27..126,896.59 rows=63,832 width=8) (actual rows= loops=)

  • Group Key: suffix_1.provider_id_base
89.          

Initplan (for HashAggregate)

90. 0.000 0.000 ↓ 0.0

Aggregate (cost=3,149.01..3,149.02 rows=1 width=8) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 hot_tpi1_1 (cost=0.00..2,799.12 rows=139,956 width=0) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Result (cost=0.57..122,694.17 rows=166,030 width=8) (actual rows= loops=)

  • One-Time Filter: ($20 = 0)
93. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..122,694.17 rows=166,030 width=8) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_6 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Index Scan using tx_suffix_trans_date on tx_suffix suffix_1 (cost=0.56..121,033.85 rows=166,030 width=16) (actual rows= loops=)

  • Index Cond: (trans_date = hd_6."Active Load Date")
  • Filter: CASE WHEN (cardinality(hd_6.county_codes) > 0) THEN (tdh_county_code = ANY (hd_6.county_codes)) ELSE true END
96. 0.000 0.000 ↓ 0.0

HashAggregate (cost=64,940.09..66,042.04 rows=110,195 width=8) (actual rows= loops=)

  • Group Key: suffix_e_1.provider_id_base
97.          

Initplan (for HashAggregate)

98. 0.000 0.000 ↓ 0.0

Aggregate (cost=3,149.01..3,149.02 rows=1 width=8) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi1 (cost=0.00..2,799.12 rows=139,956 width=0) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Result (cost=0.57..61,083.45 rows=283,048 width=8) (actual rows= loops=)

  • One-Time Filter: ($19 = 0)
101. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..61,083.45 rows=283,048 width=8) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date hd_7 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Index Scan using tx_suffix_excluded_trans_date on tx_suffix_excluded suffix_e_1 (cost=0.56..58,252.95 rows=283,048 width=16) (actual rows= loops=)

  • Index Cond: (trans_date = hd_7."Excluded Load Date")
  • Filter: CASE WHEN (cardinality(hd_7.county_codes) > 0) THEN (tdh_county_code = ANY (hd_7.county_codes)) ELSE true END
104.          

CTE vars

105. 0.000 0.000 ↓ 0.0

CTE Scan on hot_date (cost=7,831.23..7,831.25 rows=1 width=40) (actual rows= loops=)

106.          

Initplan (for CTE Scan)

107. 0.000 0.000 ↓ 0.0

Aggregate (cost=7,831.22..7,831.23 rows=1 width=32) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

CTE Scan on hot_tpi (cost=0.00..6,961.08 rows=348,054 width=32) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Append (cost=648,414.68..2,095,739.12 rows=184,027 width=778) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Unique (cost=648,414.68..648,808.52 rows=8,752 width=343) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Sort (cost=648,414.68..648,436.56 rows=8,752 width=343) (actual rows= loops=)

  • Sort Key: b_tpi.base_tpi_record, b_tpi.provider_id_base, b_tpi.name_classification, (CASE WHEN (b_tpi.name_classification = 'I'::text) THEN 'Individual'::text WHEN (b_tpi.name_classification = 'O'::text) THEN 'Organization/Group'::text ELSE b_tpi.name_classification END), b_tpi.provider_name, (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi.provider_name, ' '::text, 2)) ELSE split_part(b_tpi.provider_name, ' '::text, 1) END), (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 3) ELSE split_part(b_tpi.provider_name, ' '::text, 2) END), (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 4) ELSE split_part(b_tpi.provider_name, ' '::text, 3) END), p.id, p.npi, npi_api.npi_api, (CASE WHEN ((pfp.id IS NOT NULL) AND (p.id IS NOT NULL) AND (upper(CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi.provider_name, ' '::text, 2)) ELSE split_part(b_tpi.provider_name, ' '::text, 1) END) = upper((p.lname)::text)) AND (upper(CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 3) ELSE split_part(b_tpi.provider_name, ' '::text, 2) END) = upper((p.fname)::text)) AND (CASE WHEN (split_part(b_tpi.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi.provider_name, ' '::text, 4) ELSE split_part(b_tpi.provider_name, ' '::text, 3) END = upper((p.mname)::text))) THEN 'Medicaid ID Match and DT Provider Name Match'::text WHEN ((pfp.id IS NOT NULL) AND (p.id IS NOT NULL)) THEN 'Medicaid ID Match and DT Provider Name Not Match'::text ELSE 'Provider Not in DT'::text END), p.lname, p.fname, p.mname, v."Active Load Date", (CASE WHEN (cardinality(v."TX TPI") < 10) THEN array_to_string(v."TX TPI", ', '::text) ELSE ('Too many to list count = '::text || (cardinality(v."TX TPI"))::text) END)
112. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=640,719.83..647,841.63 rows=8,752 width=343) (actual rows= loops=)

  • Hash Cond: ((p.npi)::text = npi_api.npi_api)
113. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=630,745.94..636,359.48 rows=1,711 width=110) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Hash Join (cost=630,745.65..635,783.01 rows=1,711 width=85) (actual rows= loops=)

  • Hash Cond: ("left"((pfp.medicaid_id)::text, 7) = b_tpi.provider_id_base)
115. 0.000 0.000 ↓ 0.0

Seq Scan on provider_facilities_providers pfp (cost=0.00..4,007.50 rows=202,550 width=18) (actual rows= loops=)

  • Filter: (id IS NOT NULL)
116. 0.000 0.000 ↓ 0.0

Hash (cost=630,731.35..630,731.35 rows=1,144 width=67) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..630,731.35 rows=1,144 width=67) (actual rows= loops=)

  • Join Filter: (b_tpi.provider_id_base = ANY (v."TX TPI"))
118. 0.000 0.000 ↓ 0.0

CTE Scan on vars v (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

  • Filter: ("Active Load Date" IS NOT NULL)
119. 0.000 0.000 ↓ 0.0

Seq Scan on tx_base_tpi b_tpi (cost=0.00..281,992.87 rows=15,499,487 width=31) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Index Scan using providers_pkey on providers p (cost=0.29..0.33 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (id = pfp.provider_id)
121. 0.000 0.000 ↓ 0.0

Hash (cost=5,659.51..5,659.51 rows=345,151 width=11) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Seq Scan on tx_npi_api npi_api (cost=0.00..5,659.51 rows=345,151 width=11) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

Unique (cost=1,437,202.95..1,445,090.33 rows=175,275 width=343) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

Sort (cost=1,437,202.95..1,437,641.14 rows=175,275 width=343) (actual rows= loops=)

  • Sort Key: b_tpi_e.base_tpi_record, b_tpi_e.provider_id_base, b_tpi_e.name_classification, (CASE WHEN (b_tpi_e.name_classification = 'I'::text) THEN 'Individual'::text WHEN (b_tpi_e.name_classification = 'O'::text) THEN 'Organization/Group'::text ELSE b_tpi_e.name_classification END), b_tpi_e.provider_name, (CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi_e.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi_e.provider_name, ' '::text, 2)) ELSE split_part(b_tpi_e.provider_name, ' '::text, 1) END), (CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi_e.provider_name, ' '::text, 3) ELSE split_part(b_tpi_e.provider_name, ' '::text, 2) END), (CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi_e.provider_name, ' '::text, 4) ELSE split_part(b_tpi_e.provider_name, ' '::text, 3) END), p_1.id, p_1.npi, npi_api_e.npi_api, (CASE WHEN ((pfp_1.id IS NOT NULL) AND (p_1.id IS NOT NULL) AND (upper(CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN ((split_part(b_tpi_e.provider_name, ' '::text, 1) || ' '::text) || split_part(b_tpi_e.provider_name, ' '::text, 2)) ELSE split_part(b_tpi_e.provider_name, ' '::text, 1) END) = upper((p_1.lname)::text)) AND (upper(CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi_e.provider_name, ' '::text, 3) ELSE split_part(b_tpi_e.provider_name, ' '::text, 2) END) = upper((p_1.fname)::text)) AND (CASE WHEN (split_part(b_tpi_e.provider_name, ' '::text, 4) <> ''::text) THEN split_part(b_tpi_e.provider_name, ' '::text, 4) ELSE split_part(b_tpi_e.provider_name, ' '::text, 3) END = upper((p_1.mname)::text))) THEN 'Medicaid ID Match and DT Provider Name Match'::text WHEN ((pfp_1.id IS NOT NULL) AND (p_1.id IS NOT NULL)) THEN 'Medicaid ID Match and DT Provider Name Not Match'::text ELSE 'Provider Not in DT'::text END), p_1.lname, p_1.fname, p_1.mname, v_1."Excluded Load Date", (CASE WHEN (cardinality(v_1."TX TPI") < 10) THEN array_to_string(v_1."TX TPI", ', '::text) ELSE ('Too many to list count = '::text || (cardinality(v_1."TX TPI"))::text) END)
125. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=876,909.74..1,421,937.15 rows=175,275 width=343) (actual rows= loops=)

  • Hash Cond: (npi_api_e.npi_api = (p_1.npi)::text)
126. 0.000 0.000 ↓ 0.0

Seq Scan on tx_npi_api_excluded npi_api_e (cost=0.00..305,883.22 rows=17,273,622 width=11) (actual rows= loops=)

127. 0.000 0.000 ↓ 0.0

Hash (cost=876,900.88..876,900.88 rows=709 width=110) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=871,634.95..876,900.88 rows=709 width=110) (actual rows= loops=)

129. 0.000 0.000 ↓ 0.0

Hash Join (cost=871,634.66..876,662.00 rows=709 width=85) (actual rows= loops=)

  • Hash Cond: ("left"((pfp_1.medicaid_id)::text, 7) = b_tpi_e.provider_id_base)
130. 0.000 0.000 ↓ 0.0

Seq Scan on provider_facilities_providers pfp_1 (cost=0.00..4,007.50 rows=202,550 width=18) (actual rows= loops=)

  • Filter: (id IS NOT NULL)
131. 0.000 0.000 ↓ 0.0

Hash (cost=871,623.85..871,623.85 rows=865 width=67) (actual rows= loops=)

132. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..871,623.85 rows=865 width=67) (actual rows= loops=)

  • Join Filter: (b_tpi_e.provider_id_base = ANY (v_1."TX TPI"))
133. 0.000 0.000 ↓ 0.0

CTE Scan on vars v_1 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

  • Filter: ("Excluded Load Date" IS NOT NULL)
134. 0.000 0.000 ↓ 0.0

Seq Scan on tx_base_tpi_excluded b_tpi_e (cost=0.00..390,151.64 rows=21,398,764 width=31) (actual rows= loops=)

135. 0.000 0.000 ↓ 0.0

Index Scan using providers_pkey on providers p_1 (cost=0.29..0.33 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (id = pfp_1.provider_id)
136. 0.000 0.000 ↓ 0.0

Sort (cost=14,875.63..14,921.64 rows=18,403 width=810) (actual rows= loops=)

  • Sort Key: tx_active_master_provider_file_dcm_provider_lookup_combined."Load Date" DESC
137. 0.000 0.000 ↓ 0.0

HashAggregate (cost=13,341.96..13,572.00 rows=18,403 width=810) (actual rows= loops=)

  • Group Key: tx_active_master_provider_file_dcm_provider_lookup_combined."Load Date", tx_active_master_provider_file_dcm_provider_lookup_combined."Record Type", tx_active_master_provider_file_dcm_provider_lookup_combined."TPI ID", tx_active_master_provider_file_dcm_provider_lookup_combined."Name classification", tx_active_master_provider_file_dcm_provider_lookup_combined."Name Classification Description", tx_active_master_provider_file_dcm_provider_lookup_combined."Raw Provider Name", tx_active_master_provider_file_dcm_provider_lookup_combined."Last Name", tx_active_master_provider_file_dcm_provider_lookup_combined."First Name", tx_active_master_provider_file_dcm_provider_lookup_combined."MI", tx_active_master_provider_file_dcm_provider_lookup_combined."MCNA Provider ID", tx_active_master_provider_file_dcm_provider_lookup_combined."Base Medicaid ID", tx_active_master_provider_file_dcm_provider_lookup_combined."MCNA Provider Last Name", tx_active_master_provider_file_dcm_provider_lookup_combined."MCNA Provider First Name", tx_active_master_provider_file_dcm_provider_lookup_combined."MCNA Provider MI Name", tx_active_master_provider_file_dcm_provider_lookup_combined."TX NPI", tx_active_master_provider_file_dcm_provider_lookup_combined."MCNA NPI", tx_active_master_provider_file_dcm_provider_lookup_combined."DT Provider Base Medicare ID and Provider Name Test", tx_active_master_provider_file_dcm_provider_lookup_combined."Data Source", tx_active_master_provider_file_dcm_provider_lookup_combined."Report_Run_Date", tx_active_master_provider_file_dcm_provider_lookup_combined."TX MPF as of date filter", tx_active_master_provider_file_dcm_provider_lookup_combined."Any TX TPI Filter
138. 0.000 0.000 ↓ 0.0

CTE Scan on tx_active_master_provider_file_dcm_provider_lookup_combined (cost=0.00..3,680.54 rows=184,027 width=778) (actual rows= loops=)