explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0jXh : Optimization for: test_1; plan #SSFw

Settings

Optimization path:

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

GroupAggregate (cost=2,119,496,537,757,188,352.00..2,119,496,537,757,188,352.00 rows=200 width=126) (actual rows= loops=)

  • Group Key: a.mktdef_nm
2. 0.000 0.000 ↓ 0.0

Sort (cost=2,119,496,537,757,188,352.00..2,119,496,537,757,188,352.00 rows=3,570 width=126) (actual rows= loops=)

  • Sort Key: a.mktdef_nm
3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,119,496,537,757,188,096.00..2,119,496,537,757,188,096.00 rows=3,570 width=126) (actual rows= loops=)

  • Group Key: a.mktdef_nm, drug_exposure_f.person_id
4. 0.000 0.000 ↓ 0.0

Append (cost=2,119,485,370,112,586,752.00..2,119,496,537,757,188,096.00 rows=3,570 width=126) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Unique (cost=2,119,485,370,112,586,752.00..2,119,485,370,112,586,752.00 rows=200 width=126) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Unique (cost=2,119,485,370,112,586,752.00..2,119,485,370,112,586,752.00 rows=200 width=126) (actual rows= loops=)

7.          

CTE cte_descendant_concept_product

8. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,119,485,370,112,585,472.00..2,119,485,370,112,585,728.00 rows=19,600 width=122) (actual rows= loops=)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
9. 0.000 0.000 ↓ 0.0

Append (cost=2,110,808,031,137,758,464.00..2,119,485,370,112,585,472.00 rows=19,600 width=122) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,110,808,031,137,758,464.00..2,110,808,031,137,758,464.00 rows=10,000 width=19) (actual rows= loops=)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
11. 0.000 0.000 ↓ 0.0

Merge Join (cost=50,186,400,094,231,296.00..469,146,269,000,505,344.00 rows=328,332,352,427,450,630,144 width=19) (actual rows= loops=)

  • Merge Cond: (concept_f_1.concept_id = concept_ancestor_f.ancestor_concept_id)
12. 0.000 0.000 ↓ 0.0

Sort (cost=3,757,406.20..3,786,811.73 rows=11,762,210 width=12) (actual rows= loops=)

  • Sort Key: concept_f_1.concept_id
13. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,700,259.39..1,974,041.50 rows=11,762,210 width=12) (actual rows= loops=)

  • Merge Cond: (concept_f.concept_id = concept_ancestor_f_1.descendant_concept_id)
14. 0.000 0.000 ↓ 0.0

Unique (cost=937,874.06..998,859.69 rows=2,217,659 width=240) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Sort (cost=937,874.06..943,418.21 rows=2,217,659 width=240) (actual rows= loops=)

  • Sort Key: concept_f.concept_id, concept_f.concept_name, concept_f.domain_id, concept_f.vocabulary_id, concept_f.concept_class_id, concept_f.standard_concept, concept_f.concept_code, concept_f.valid_start_date, concept_f.valid_end_date, concept_f.invalid_reason
16. 0.000 0.000 ↓ 0.0

Append (cost=0.00..203,850.09 rows=2,217,659 width=240) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f (cost=0.00..166,083.70 rows=2,217,658 width=115) (actual rows= loops=)

  • Filter: ((standard_concept)::text = 'S'::text)
18. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..4,501.51 rows=1 width=117) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on concept_amend (cost=0.00..4,501.50 rows=1 width=115) (actual rows= loops=)

  • Filter: ((standard_concept)::text = 'S'::text)
20. 0.000 0.000 ↓ 0.0

Materialize (cost=762,385.32..779,670.53 rows=3,457,041 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=762,385.32..771,027.93 rows=3,457,041 width=12) (actual rows= loops=)

  • Sort Key: concept_ancestor_f_1.descendant_concept_id
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=134,455.04..268,764.55 rows=3,457,041 width=12) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

HashAggregate (cost=134,454.47..134,473.99 rows=1,952 width=240) (actual rows= loops=)

  • Group Key: concept_f_1.concept_id, concept_f_1.concept_name, concept_f_1.domain_id, concept_f_1.vocabulary_id, concept_f_1.concept_class_id, concept_f_1.standard_concept, concept_f_1.concept_code, concept_f_1.valid_start_date, concept_f_1.valid_end_date, concept_f_1.invalid_reason
24. 0.000 0.000 ↓ 0.0

Append (cost=1,000.00..134,405.67 rows=1,952 width=240) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..134,371.95 rows=1,951 width=115) (actual rows= loops=)

  • Workers Planned: 2
26. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on concept_f concept_f_1 (cost=0.00..133,176.85 rows=813 width=115) (actual rows= loops=)

  • Filter: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=0.42..4.45 rows=1 width=117) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using idx_concept_class_id on concept_amend concept_amend_1 (cost=0.42..4.44 rows=1 width=115) (actual rows= loops=)

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Filter: ((vocabulary_id)::text = 'RxNorm'::text)
29. 0.000 0.000 ↓ 0.0

Index Only Scan using concept_ancestor_idx on concept_ancestor_f concept_ancestor_f_1 (cost=0.57..51.08 rows=1,771 width=8) (actual rows= loops=)

  • Index Cond: (ancestor_concept_id = concept_f_1.concept_id)
30. 0.000 0.000 ↓ 0.0

Materialize (cost=50,186,400,090,473,888.00..50,666,508,383,761,520.00 rows=96,021,658,657,527,008 width=19) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=50,186,400,090,473,888.00..50,426,454,237,117,704.00 rows=96,021,658,657,527,008 width=19) (actual rows= loops=)

  • Sort Key: concept_ancestor_f.ancestor_concept_id
32. 0.000 0.000 ↓ 0.0

Merge Join (cost=9,450,663,223.80..1,440,334,421,605,282.25 rows=96,021,658,657,527,008 width=19) (actual rows= loops=)

  • Merge Cond: (concept_f_2.concept_id = concept_relationship_f.concept_id_2)
33. 0.000 0.000 ↓ 0.0

Merge Join (cost=16,065,825.80..25,382,435.00 rows=594,738,384 width=12) (actual rows= loops=)

  • Merge Cond: (concept_f_2.concept_id = concept_ancestor_f.descendant_concept_id)
34. 0.000 0.000 ↓ 0.0

Unique (cost=2,673,814.94..2,822,585.98 rows=5,409,856 width=240) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=2,673,814.94..2,687,339.58 rows=5,409,856 width=240) (actual rows= loops=)

  • Sort Key: concept_f_2.concept_id, concept_f_2.concept_name, concept_f_2.domain_id, concept_f_2.vocabulary_id, concept_f_2.concept_class_id, concept_f_2.standard_concept, concept_f_2.concept_code, concept_f_2.valid_start_date, concept_f_2.valid_end_date, concept_f_2.invalid_reason
36. 0.000 0.000 ↓ 0.0

Append (cost=0.00..238,208.40 rows=5,409,856 width=240) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f concept_f_2 (cost=0.00..152,920.96 rows=5,265,096 width=115) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_2 (cost=0.00..5,587.20 rows=144,760 width=117) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on concept_amend concept_amend_2 (cost=0.00..4,139.60 rows=144,760 width=115) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Materialize (cost=13,392,010.86..13,750,289.26 rows=71,655,680 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=13,392,010.86..13,571,150.06 rows=71,655,680 width=8) (actual rows= loops=)

  • Sort Key: concept_ancestor_f.descendant_concept_id
42. 0.000 0.000 ↓ 0.0

Seq Scan on concept_ancestor_f (cost=0.00..1,103,884.80 rows=71,655,680 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Materialize (cost=9,434,597,398.01..9,596,049,325.80 rows=32,290,385,558 width=19) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=9,434,597,398.01..9,515,323,361.90 rows=32,290,385,558 width=19) (actual rows= loops=)

  • Sort Key: concept_relationship_f.concept_id_2
45. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,704,651.71..487,222,475.38 rows=32,290,385,558 width=19) (actual rows= loops=)

  • Merge Cond: (b.concept_id = concept_relationship_f.concept_id_1)
46. 0.000 0.000 ↓ 0.0

Sort (cost=563,357.04..566,196.57 rows=1,135,813 width=19) (actual rows= loops=)

  • Sort Key: b.concept_id
47. 0.000 0.000 ↓ 0.0

Hash Join (cost=363,528.49..425,824.97 rows=1,135,813 width=19) (actual rows= loops=)

  • Hash Cond: ((b.concept_code)::text = (a_2.ndc)::text)
48. 0.000 0.000 ↓ 0.0

Subquery Scan on b (cost=353,520.72..374,436.50 rows=557,754 width=36) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Unique (cost=353,520.72..368,858.96 rows=557,754 width=240) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=353,520.72..354,915.11 rows=557,754 width=240) (actual rows= loops=)

  • Sort Key: concept_f_3.concept_id, concept_f_3.concept_name, concept_f_3.domain_id, concept_f_3.vocabulary_id, concept_f_3.concept_class_id, concept_f_3.standard_concept, concept_f_3.concept_code, concept_f_3.valid_start_date, concept_f_3.valid_end_date, concept_f_3.invalid_reason
51. 0.000 0.000 ↓ 0.0

Append (cost=0.00..174,460.14 rows=557,754 width=240) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f concept_f_3 (cost=0.00..166,083.70 rows=557,749 width=115) (actual rows= loops=)

  • Filter: ((vocabulary_id)::text = 'NDC'::text)
53. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_3 (cost=0.42..10.18 rows=5 width=117) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using idx_concept_vocabluary_id on concept_amend concept_amend_3 (cost=0.42..10.13 rows=5 width=115) (actual rows= loops=)

  • Index Cond: ((vocabulary_id)::text = 'NDC'::text)
55. 0.000 0.000 ↓ 0.0

Hash (cost=6,930.45..6,930.45 rows=159,145 width=27) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on product_list a_2 (cost=0.00..6,930.45 rows=159,145 width=27) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Materialize (cost=2,141,294.68..2,311,870.57 rows=5,685,863 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Unique (cost=2,141,294.68..2,240,797.28 rows=5,685,863 width=82) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=2,141,294.68..2,155,509.33 rows=5,685,863 width=82) (actual rows= loops=)

  • Sort Key: concept_relationship_f.concept_id_1, concept_relationship_f.concept_id_2, concept_relationship_f.relationship_id, concept_relationship_f.valid_start_date, concept_relationship_f.valid_end_date, concept_relationship_f.invalid_reason
60. 0.000 0.000 ↓ 0.0

Append (cost=0.00..959,211.76 rows=5,685,863 width=82) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_f (cost=0.00..873,922.80 rows=5,685,862 width=37) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
62. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_amend (cost=0.00..1.01 rows=1 width=32) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
63. 0.000 0.000 ↓ 0.0

HashAggregate (cost=8,677,338,974,826,737.00..8,677,338,974,826,833.00 rows=9,600 width=16) (actual rows= loops=)

  • Group Key: a_3.mktdef_nm, concept_f_4.concept_id
64. 0.000 0.000 ↓ 0.0

Merge Join (cost=163,175,733,870,668.63..1,894,251,765,314,286.00 rows=1,356,617,441,902,490,112 width=16) (actual rows= loops=)

  • Merge Cond: (concept_f_5.concept_id = concept_ancestor_f_2.ancestor_concept_id)
65. 0.000 0.000 ↓ 0.0

Sort (cost=3,757,406.20..3,786,811.73 rows=11,762,210 width=12) (actual rows= loops=)

  • Sort Key: concept_f_5.concept_id
66. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,700,259.39..1,974,041.50 rows=11,762,210 width=12) (actual rows= loops=)

  • Merge Cond: (concept_f_4.concept_id = concept_ancestor_f_3.descendant_concept_id)
67. 0.000 0.000 ↓ 0.0

Unique (cost=937,874.06..998,859.69 rows=2,217,659 width=240) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Sort (cost=937,874.06..943,418.21 rows=2,217,659 width=240) (actual rows= loops=)

  • Sort Key: concept_f_4.concept_id, concept_f_4.concept_name, concept_f_4.domain_id, concept_f_4.vocabulary_id, concept_f_4.concept_class_id, concept_f_4.standard_concept, concept_f_4.concept_code, concept_f_4.valid_start_date, concept_f_4.valid_end_date, concept_f_4.invalid_reason
69. 0.000 0.000 ↓ 0.0

Append (cost=0.00..203,850.09 rows=2,217,659 width=240) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f concept_f_4 (cost=0.00..166,083.70 rows=2,217,658 width=115) (actual rows= loops=)

  • Filter: ((standard_concept)::text = 'S'::text)
71. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_4 (cost=0.00..4,501.51 rows=1 width=117) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on concept_amend concept_amend_4 (cost=0.00..4,501.50 rows=1 width=115) (actual rows= loops=)

  • Filter: ((standard_concept)::text = 'S'::text)
73. 0.000 0.000 ↓ 0.0

Materialize (cost=762,385.32..779,670.53 rows=3,457,041 width=12) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Sort (cost=762,385.32..771,027.93 rows=3,457,041 width=12) (actual rows= loops=)

  • Sort Key: concept_ancestor_f_3.descendant_concept_id
75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=134,455.04..268,764.55 rows=3,457,041 width=12) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

HashAggregate (cost=134,454.47..134,473.99 rows=1,952 width=240) (actual rows= loops=)

  • Group Key: concept_f_5.concept_id, concept_f_5.concept_name, concept_f_5.domain_id, concept_f_5.vocabulary_id, concept_f_5.concept_class_id, concept_f_5.standard_concept, concept_f_5.concept_code, concept_f_5.valid_start_date, concept_f_5.valid_end_date, concept_f_5.invalid_reason
77. 0.000 0.000 ↓ 0.0

Append (cost=1,000.00..134,405.67 rows=1,952 width=240) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..134,371.95 rows=1,951 width=115) (actual rows= loops=)

  • Workers Planned: 2
79. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on concept_f concept_f_5 (cost=0.00..133,176.85 rows=813 width=115) (actual rows= loops=)

  • Filter: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
80. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_5 (cost=0.42..4.45 rows=1 width=117) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Index Scan using idx_concept_class_id on concept_amend concept_amend_5 (cost=0.42..4.44 rows=1 width=115) (actual rows= loops=)

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Filter: ((vocabulary_id)::text = 'RxNorm'::text)
82. 0.000 0.000 ↓ 0.0

Index Only Scan using concept_ancestor_idx on concept_ancestor_f concept_ancestor_f_3 (cost=0.57..51.08 rows=1,771 width=8) (actual rows= loops=)

  • Index Cond: (ancestor_concept_id = concept_f_5.concept_id)
83. 0.000 0.000 ↓ 0.0

Materialize (cost=163,175,730,113,262.44..165,159,461,779,851.56 rows=396,746,333,317,827 width=16) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Sort (cost=163,175,730,113,262.44..164,167,595,946,557.00 rows=396,746,333,317,827 width=16) (actual rows= loops=)

  • Sort Key: concept_ancestor_f_2.ancestor_concept_id
85. 0.000 0.000 ↓ 0.0

Merge Join (cost=45,340,552.97..5,951,250,322,163.02 rows=396,746,333,317,827 width=16) (actual rows= loops=)

  • Merge Cond: (concept_relationship_f_1.concept_id_2 = concept_f_7.concept_id)
86. 0.000 0.000 ↓ 0.0

Sort (cost=29,274,727.17..29,608,274.11 rows=133,418,775 width=16) (actual rows= loops=)

  • Sort Key: concept_relationship_f_1.concept_id_2
87. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,270,952.76..4,428,619.09 rows=133,418,775 width=16) (actual rows= loops=)

  • Merge Cond: (concept_f_6.concept_id = concept_relationship_f_1.concept_id_1)
88. 0.000 0.000 ↓ 0.0

Sort (cost=129,658.09..129,669.82 rows=4,693 width=16) (actual rows= loops=)

  • Sort Key: concept_f_6.concept_id
89. 0.000 0.000 ↓ 0.0

Hash Join (cost=129,228.95..129,371.90 rows=4,693 width=16) (actual rows= loops=)

  • Hash Cond: ((concept_f_6.concept_code)::text = (a_3.prc_cd)::text)
90. 0.000 0.000 ↓ 0.0

HashAggregate (cost=129,169.61..129,203.01 rows=3,340 width=240) (actual rows= loops=)

  • Group Key: concept_f_6.concept_id, concept_f_6.concept_name, concept_f_6.domain_id, concept_f_6.vocabulary_id, concept_f_6.concept_class_id, concept_f_6.standard_concept, concept_f_6.concept_code, concept_f_6.valid_start_date, concept_f_6.valid_end_date, concept_f_6.invalid_reason
91. 0.000 0.000 ↓ 0.0

Append (cost=1,000.00..129,086.11 rows=3,340 width=240) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..129,025.88 rows=3,335 width=115) (actual rows= loops=)

  • Workers Planned: 2
93. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on concept_f concept_f_6 (cost=0.00..127,692.38 rows=1,390 width=115) (actual rows= loops=)

  • Filter: ((vocabulary_id)::text = 'HCPCS'::text)
94. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_6 (cost=0.42..10.18 rows=5 width=117) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Index Scan using idx_concept_vocabluary_id on concept_amend concept_amend_6 (cost=0.42..10.13 rows=5 width=115) (actual rows= loops=)

  • Index Cond: ((vocabulary_id)::text = 'HCPCS'::text)
96. 0.000 0.000 ↓ 0.0

Hash (cost=43.04..43.04 rows=1,304 width=18) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Seq Scan on drug_procedure_list a_3 (cost=0.00..43.04 rows=1,304 width=18) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Materialize (cost=2,141,294.68..2,311,870.57 rows=5,685,863 width=8) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Unique (cost=2,141,294.68..2,240,797.28 rows=5,685,863 width=82) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Sort (cost=2,141,294.68..2,155,509.33 rows=5,685,863 width=82) (actual rows= loops=)

  • Sort Key: concept_relationship_f_1.concept_id_1, concept_relationship_f_1.concept_id_2, concept_relationship_f_1.relationship_id, concept_relationship_f_1.valid_start_date, concept_relationship_f_1.valid_end_date, concept_relationship_f_1.invalid_reason
101. 0.000 0.000 ↓ 0.0

Append (cost=0.00..959,211.76 rows=5,685,863 width=82) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_f concept_relationship_f_1 (cost=0.00..873,922.80 rows=5,685,862 width=37) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
103. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_amend concept_relationship_amend_1 (cost=0.00..1.01 rows=1 width=32) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
104. 0.000 0.000 ↓ 0.0

Materialize (cost=16,065,825.80..26,869,280.96 rows=594,738,384 width=12) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Merge Join (cost=16,065,825.80..25,382,435.00 rows=594,738,384 width=12) (actual rows= loops=)

  • Merge Cond: (concept_f_7.concept_id = concept_ancestor_f_2.descendant_concept_id)
106. 0.000 0.000 ↓ 0.0

Unique (cost=2,673,814.94..2,822,585.98 rows=5,409,856 width=240) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Sort (cost=2,673,814.94..2,687,339.58 rows=5,409,856 width=240) (actual rows= loops=)

  • Sort Key: concept_f_7.concept_id, concept_f_7.concept_name, concept_f_7.domain_id, concept_f_7.vocabulary_id, concept_f_7.concept_class_id, concept_f_7.standard_concept, concept_f_7.concept_code, concept_f_7.valid_start_date, concept_f_7.valid_end_date, concept_f_7.invalid_reason
108. 0.000 0.000 ↓ 0.0

Append (cost=0.00..238,208.40 rows=5,409,856 width=240) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f concept_f_7 (cost=0.00..152,920.96 rows=5,265,096 width=115) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_7 (cost=0.00..5,587.20 rows=144,760 width=117) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Seq Scan on concept_amend concept_amend_7 (cost=0.00..4,139.60 rows=144,760 width=115) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Materialize (cost=13,392,010.86..13,750,289.26 rows=71,655,680 width=8) (actual rows= loops=)

113. 0.000 0.000 ↓ 0.0

Sort (cost=13,392,010.86..13,571,150.06 rows=71,655,680 width=8) (actual rows= loops=)

  • Sort Key: concept_ancestor_f_2.descendant_concept_id
114. 0.000 0.000 ↓ 0.0

Seq Scan on concept_ancestor_f concept_ancestor_f_2 (cost=0.00..1,103,884.80 rows=71,655,680 width=8) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Sort (cost=1,029.69..1,039.98 rows=4,116 width=126) (actual rows= loops=)

  • Sort Key: a.mktdef_nm, drug_exposure_f.person_id
116. 0.000 0.000 ↓ 0.0

Hash Join (cost=637.00..782.59 rows=4,116 width=126) (actual rows= loops=)

  • Hash Cond: (drug_exposure_f.drug_concept_id = a.concept_id)
117. 0.000 0.000 ↓ 0.0

Seq Scan on drug_exposure_f (cost=0.00..1.42 rows=42 width=12) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Hash (cost=392.00..392.00 rows=19,600 width=122) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

CTE Scan on cte_descendant_concept_product a (cost=0.00..392.00 rows=19,600 width=122) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Unique (cost=11,167,644,600,573.97..11,167,644,601,332.22 rows=3,370 width=126) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Unique (cost=11,167,644,600,573.97..11,167,644,600,826.72 rows=33,700 width=126) (actual rows= loops=)

122.          

CTE cte_descendant_concept_diagnosis

123. 0.000 0.000 ↓ 0.0

HashAggregate (cost=11,167,644,594,334.69..11,167,644,594,434.69 rows=10,000 width=16) (actual rows= loops=)

  • Group Key: a_4.mktdef_nm, f.concept_id
124. 0.000 0.000 ↓ 0.0

Merge Join (cost=24,692,742.67..8,375,758,880,067.87 rows=558,377,142,853,364 width=16) (actual rows= loops=)

  • Merge Cond: (concept_relationship_f_2.concept_id_2 = concept_f_9.concept_id)
125. 0.000 0.000 ↓ 0.0

Sort (cost=6,173,011.05..6,227,666.41 rows=21,862,143 width=16) (actual rows= loops=)

  • Sort Key: concept_relationship_f_2.concept_id_2
126. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,276,264.46..2,760,561.69 rows=21,862,143 width=16) (actual rows= loops=)

  • Merge Cond: (concept_f_8.concept_id = concept_relationship_f_2.concept_id_1)
127. 0.000 0.000 ↓ 0.0

Sort (cost=134,969.79..134,971.71 rows=769 width=16) (actual rows= loops=)

  • Sort Key: concept_f_8.concept_id
128. 0.000 0.000 ↓ 0.0

Hash Join (cost=134,870.91..134,932.93 rows=769 width=16) (actual rows= loops=)

  • Hash Cond: ((concept_f_8.concept_code)::text = (a_4.diag_std_cd)::text)
  • Join Filter: ((((concept_f_8.vocabulary_id)::text = 'ICD9CM'::text) AND ((a_4.diag_vers_typ_id)::text = '1'::text)) OR (((concept_f_8.vocabulary_id)::text = 'ICD10CM'::text) AND ((a_4.diag_vers_typ_id)::text = '2'::text)))
129. 0.000 0.000 ↓ 0.0

HashAggregate (cost=134,421.05..134,435.19 rows=1,414 width=240) (actual rows= loops=)

  • Group Key: concept_f_8.concept_id, concept_f_8.concept_name, concept_f_8.domain_id, concept_f_8.vocabulary_id, concept_f_8.concept_class_id, concept_f_8.standard_concept, concept_f_8.concept_code, concept_f_8.valid_start_date, concept_f_8.valid_end_date, concept_f_8.invalid_reason
130. 0.000 0.000 ↓ 0.0

Append (cost=1,000.00..134,385.70 rows=1,414 width=240) (actual rows= loops=)

131. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..134,317.25 rows=1,404 width=115) (actual rows= loops=)

  • Workers Planned: 2
132. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on concept_f concept_f_8 (cost=0.00..133,176.85 rows=585 width=115) (actual rows= loops=)

  • Filter: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
133. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_8 (cost=8.92..47.34 rows=10 width=117) (actual rows= loops=)

134. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on concept_amend concept_amend_8 (cost=8.92..47.24 rows=10 width=115) (actual rows= loops=)

  • Recheck Cond: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
135. 0.000 0.000 ↓ 0.0

BitmapOr (cost=8.92..8.92 rows=10 width=0) (actual rows= loops=)

136. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..4.46 rows=5 width=0) (actual rows= loops=)

  • Index Cond: ((vocabulary_id)::text = 'ICD9CM'::text)
137. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..4.46 rows=5 width=0) (actual rows= loops=)

  • Index Cond: ((vocabulary_id)::text = 'ICD10CM'::text)
138. 0.000 0.000 ↓ 0.0

Hash (cost=357.14..357.14 rows=7,417 width=21) (actual rows= loops=)

139. 0.000 0.000 ↓ 0.0

Seq Scan on diagnosis_list a_4 (cost=0.00..357.14 rows=7,417 width=21) (actual rows= loops=)

  • Filter: (((diag_vers_typ_id)::text = '1'::text) OR ((diag_vers_typ_id)::text = '2'::text))
140. 0.000 0.000 ↓ 0.0

Materialize (cost=2,141,294.68..2,311,870.57 rows=5,685,863 width=8) (actual rows= loops=)

141. 0.000 0.000 ↓ 0.0

Unique (cost=2,141,294.68..2,240,797.28 rows=5,685,863 width=82) (actual rows= loops=)

142. 0.000 0.000 ↓ 0.0

Sort (cost=2,141,294.68..2,155,509.33 rows=5,685,863 width=82) (actual rows= loops=)

  • Sort Key: concept_relationship_f_2.concept_id_1, concept_relationship_f_2.concept_id_2, concept_relationship_f_2.relationship_id, concept_relationship_f_2.valid_start_date, concept_relationship_f_2.valid_end_date, concept_relationship_f_2.invalid_reason
143. 0.000 0.000 ↓ 0.0

Append (cost=0.00..959,211.76 rows=5,685,863 width=82) (actual rows= loops=)

144. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_f concept_relationship_f_2 (cost=0.00..873,922.80 rows=5,685,862 width=37) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
145. 0.000 0.000 ↓ 0.0

Seq Scan on concept_relationship_amend concept_relationship_amend_2 (cost=0.00..1.01 rows=1 width=32) (actual rows= loops=)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
146. 0.000 0.000 ↓ 0.0

Materialize (cost=18,519,731.62..108,224,519.00 rows=5,108,164,766 width=12) (actual rows= loops=)

147. 0.000 0.000 ↓ 0.0

Merge Join (cost=18,519,731.62..95,454,107.09 rows=5,108,164,766 width=12) (actual rows= loops=)

  • Merge Cond: (concept_f_9.concept_id = concept_ancestor_f_4.ancestor_concept_id)
148. 0.000 0.000 ↓ 0.0

Unique (cost=2,673,814.94..2,822,585.98 rows=5,409,856 width=240) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Sort (cost=2,673,814.94..2,687,339.58 rows=5,409,856 width=240) (actual rows= loops=)

  • Sort Key: concept_f_9.concept_id, concept_f_9.concept_name, concept_f_9.domain_id, concept_f_9.vocabulary_id, concept_f_9.concept_class_id, concept_f_9.standard_concept, concept_f_9.concept_code, concept_f_9.valid_start_date, concept_f_9.valid_end_date, concept_f_9.invalid_reason
150. 0.000 0.000 ↓ 0.0

Append (cost=0.00..238,208.40 rows=5,409,856 width=240) (actual rows= loops=)

151. 0.000 0.000 ↓ 0.0

Seq Scan on concept_f concept_f_9 (cost=0.00..152,920.96 rows=5,265,096 width=115) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_9 (cost=0.00..5,587.20 rows=144,760 width=117) (actual rows= loops=)

153. 0.000 0.000 ↓ 0.0

Seq Scan on concept_amend concept_amend_9 (cost=0.00..4,139.60 rows=144,760 width=115) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Materialize (cost=15,845,916.68..16,036,935.02 rows=38,203,669 width=8) (actual rows= loops=)

155. 0.000 0.000 ↓ 0.0

Sort (cost=15,845,916.68..15,941,425.85 rows=38,203,669 width=8) (actual rows= loops=)

  • Sort Key: concept_ancestor_f_4.ancestor_concept_id
156. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,564,011.47..9,467,740.91 rows=38,203,669 width=8) (actual rows= loops=)

  • Hash Cond: (f.concept_id = concept_ancestor_f_4.descendant_concept_id)
157. 0.000 0.000 ↓ 0.0

Subquery Scan on f (cost=284,525.67..297,557.22 rows=347,508 width=4) (actual rows= loops=)

158. 0.000 0.000 ↓ 0.0

Unique (cost=284,525.67..294,082.14 rows=347,508 width=240) (actual rows= loops=)

159. 0.000 0.000 ↓ 0.0

Sort (cost=284,525.67..285,394.44 rows=347,508 width=240) (actual rows= loops=)

  • Sort Key: concept_f_10.concept_id, concept_f_10.concept_name, concept_f_10.domain_id, concept_f_10.vocabulary_id, concept_f_10.concept_class_id, concept_f_10.standard_concept, concept_f_10.concept_code, concept_f_10.valid_start_date, concept_f_10.valid_end_date, concept_f_10.invalid_reason
160. 0.000 0.000 ↓ 0.0

Append (cost=1,000.00..174,150.32 rows=347,508 width=240) (actual rows= loops=)

161. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..168,927.55 rows=347,507 width=115) (actual rows= loops=)

  • Workers Planned: 2
162. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on concept_f concept_f_10 (cost=0.00..133,176.85 rows=144,795 width=115) (actual rows= loops=)

  • Filter: (((standard_concept)::text = 'S'::text) AND ((vocabulary_id)::text = 'SNOMED'::text))
163. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_10 (cost=0.42..10.16 rows=1 width=117) (actual rows= loops=)

164. 0.000 0.000 ↓ 0.0

Index Scan using idx_concept_vocabluary_id on concept_amend concept_amend_10 (cost=0.42..10.15 rows=1 width=115) (actual rows= loops=)

  • Index Cond: ((vocabulary_id)::text = 'SNOMED'::text)
  • Filter: ((standard_concept)::text = 'S'::text)
165. 0.000 0.000 ↓ 0.0

Hash (cost=1,103,884.80..1,103,884.80 rows=71,655,680 width=8) (actual rows= loops=)

166. 0.000 0.000 ↓ 0.0

Seq Scan on concept_ancestor_f concept_ancestor_f_4 (cost=0.00..1,103,884.80 rows=71,655,680 width=8) (actual rows= loops=)

167. 0.000 0.000 ↓ 0.0

Sort (cost=6,139.27..6,223.52 rows=33,700 width=126) (actual rows= loops=)

  • Sort Key: a_1.mktdef_nm, condition_occurrence_f.person_id
168. 0.000 0.000 ↓ 0.0

Merge Join (cost=864.66..1,413.96 rows=33,700 width=126) (actual rows= loops=)

  • Merge Cond: (condition_occurrence_f.condition_concept_id = a_1.concept_id)
169. 0.000 0.000 ↓ 0.0

Index Scan using condition_occurrence_idx on condition_occurrence_f (cost=0.28..42.39 rows=674 width=12) (actual rows= loops=)

170. 0.000 0.000 ↓ 0.0

Sort (cost=864.39..889.39 rows=10,000 width=122) (actual rows= loops=)

  • Sort Key: a_1.concept_id
171. 0.000 0.000 ↓ 0.0

CTE Scan on cte_descendant_concept_diagnosis a_1 (cost=0.00..200.00 rows=10,000 width=122) (actual rows= loops=)