explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VBVF : Optimization for: test_3; plan #bqmh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 1,352,905.716 ↓ 0.0 0 1

GroupAggregate (cost=2,258,513,292,225,834,496.00..2,258,513,292,225,834,496.00 rows=200 width=126) (actual time=1,352,905.716..1,352,905.716 rows=0 loops=1)

  • Group Key: a.mktdef_nm
  • Buffers: shared hit=4895041 read=2318671, temp read=2444739 written=2471745
2. 0.006 1,352,905.714 ↓ 0.0 0 1

Sort (cost=2,258,513,292,225,834,496.00..2,258,513,292,225,834,496.00 rows=3,570 width=126) (actual time=1,352,905.714..1,352,905.714 rows=0 loops=1)

  • Sort Key: a.mktdef_nm
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4895041 read=2318671, temp read=2444739 written=2471745
3. 0.030 1,352,905.708 ↓ 0.0 0 1

HashAggregate (cost=2,258,513,292,225,834,240.00..2,258,513,292,225,834,240.00 rows=3,570 width=126) (actual time=1,352,905.707..1,352,905.708 rows=0 loops=1)

  • Group Key: a.mktdef_nm, drug_exposure_f.person_id
  • Buffers: shared hit=4895041 read=2318671, temp read=2444739 written=2471745
4. 0.004 1,352,905.678 ↓ 0.0 0 1

Append (cost=2,258,501,222,213,048,576.00..2,258,513,292,225,834,240.00 rows=3,570 width=126) (actual time=1,352,905.678..1,352,905.678 rows=0 loops=1)

  • Buffers: shared hit=4895041 read=2318671, temp read=2444739 written=2471745
5. 0.002 1,352,018.605 ↓ 0.0 0 1

Unique (cost=2,258,501,222,213,048,576.00..2,258,501,222,213,048,576.00 rows=200 width=126) (actual time=1,352,018.604..1,352,018.605 rows=0 loops=1)

  • Buffers: shared hit=4892540 read=2220673, temp read=2444739 written=2471745
6. 0.001 1,352,018.603 ↓ 0.0 0 1

Unique (cost=2,258,501,222,213,048,576.00..2,258,501,222,213,048,576.00 rows=200 width=126) (actual time=1,352,018.603..1,352,018.603 rows=0 loops=1)

  • Buffers: shared hit=4892540 read=2220673, temp read=2444739 written=2471745
7.          

CTE cte_descendant_concept_product

8. 2,674.811 1,348,552.460 ↓ 89.3 1,749,598 1

HashAggregate (cost=2,258,501,222,213,047,296.00..2,258,501,222,213,047,552.00 rows=19,600 width=122) (actual time=1,347,526.597..1,348,552.460 rows=1,749,598 loops=1)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
  • Buffers: shared hit=4892539 read=2220673, temp read=2433464 written=2453953
9. 1,384.080 1,345,877.649 ↓ 111.9 2,192,443 1

Append (cost=2,248,975,300,420,909,312.00..2,258,501,222,213,047,296.00 rows=19,600 width=122) (actual time=971,227.583..1,345,877.649 rows=2,192,443 loops=1)

  • Buffers: shared hit=4892539 read=2220673, temp read=2433464 written=2453953
10. 201,937.562 972,266.458 ↓ 173.3 1,733,140 1

HashAggregate (cost=2,248,975,300,420,909,312.00..2,248,975,300,420,909,312.00 rows=10,000 width=19) (actual time=971,227.581..972,266.458 rows=1,733,140 loops=1)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
  • Buffers: shared hit=2444226 read=1115024, temp read=1311363 written=1321483
11. 273,336.013 770,328.896 ↑ 944,473,297,648.5 370,324,724 1

Merge Join (cost=53,860,169,820,508,152.00..500,166,234,035,718,912.00 rows=349,761,813,277,038,084,096 width=19) (actual time=356,341.711..770,328.896 rows=370,324,724 loops=1)

  • Merge Cond: (concept_f_1.concept_id = concept_ancestor_f.ancestor_concept_id)
  • Buffers: shared hit=2444226 read=1115024, temp read=1311363 written=1321483
12. 2,689.612 24,448.126 ↑ 5.0 2,336,185 1

Sort (cost=3,740,021.52..3,769,237.71 rows=11,686,474 width=12) (actual time=23,350.121..24,448.126 rows=2,336,185 loops=1)

  • Sort Key: concept_f_1.concept_id
  • Sort Method: external merge Disk: 50280kB
  • Buffers: shared hit=2437120 read=199583, temp read=124660 written=124898
13. 2,857.998 21,758.514 ↑ 5.0 2,336,185 1

Merge Join (cost=1,696,077.45..1,968,685.68 rows=11,686,474 width=12) (actual time=12,721.190..21,758.514 rows=2,336,185 loops=1)

  • Merge Cond: (concept_f.concept_id = concept_ancestor_f_1.descendant_concept_id)
  • Buffers: shared hit=2437120 read=199583, temp read=113732 written=113928
14. 2,155.851 9,811.122 ↑ 1.0 2,206,885 1

Unique (cost=938,042.77..999,041.95 rows=2,218,152 width=240) (actual time=6,486.508..9,811.122 rows=2,206,885 loops=1)

  • Buffers: shared hit=6459 read=96503, temp read=100924 written=101074
15. 4,276.177 7,655.271 ↑ 1.0 2,206,885 1

Sort (cost=938,042.77..943,588.15 rows=2,218,152 width=240) (actual time=6,486.497..7,655.271 rows=2,206,885 loops=1)

  • 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
  • Sort Method: external merge Disk: 314448kB
  • Buffers: shared hit=6459 read=96503, temp read=100924 written=101074
16. 1,417.877 3,379.094 ↑ 1.0 2,206,903 1

Append (cost=0.00..203,851.27 rows=2,218,152 width=240) (actual time=0.059..3,379.094 rows=2,206,903 loops=1)

  • Buffers: shared hit=6459 read=96503
17. 1,940.446 1,940.446 ↑ 1.0 2,206,903 1

Seq Scan on concept_f (cost=0.00..166,077.49 rows=2,218,151 width=115) (actual time=0.058..1,940.446 rows=2,206,903 loops=1)

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 3058193
  • Buffers: shared hit=3767 read=96503
18. 0.002 20.771 ↓ 0.0 0 1

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

  • Buffers: shared hit=2692
19. 20.769 20.769 ↓ 0.0 0 1

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

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 144760
  • Buffers: shared hit=2692
20. 1,726.851 9,089.394 ↑ 1.3 2,590,553 1

Materialize (cost=758,034.68..775,204.77 rows=3,434,018 width=12) (actual time=6,212.544..9,089.394 rows=2,590,553 loops=1)

  • Buffers: shared hit=2430661 read=103080, temp read=12808 written=12854
21. 3,062.499 7,362.543 ↑ 1.3 2,590,553 1

Sort (cost=758,034.68..766,619.73 rows=3,434,018 width=12) (actual time=6,212.539..7,362.543 rows=2,590,553 loops=1)

  • Sort Key: concept_ancestor_f_1.descendant_concept_id
  • Sort Method: external merge Disk: 55768kB
  • Buffers: shared hit=2430661 read=103080, temp read=12808 written=12854
22. 1,749.264 4,300.044 ↑ 1.3 2,590,553 1

Nested Loop (cost=134,450.11..267,870.85 rows=3,434,018 width=12) (actual time=901.829..4,300.044 rows=2,590,553 loops=1)

  • Buffers: shared hit=2430661 read=103080
23. 35.051 913.896 ↓ 6.9 13,308 1

HashAggregate (cost=134,449.54..134,468.93 rows=1,939 width=240) (actual time=901.790..913.896 rows=13,308 loops=1)

  • 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
  • Buffers: shared hit=3799 read=96474
24. 9.754 878.845 ↓ 6.9 13,308 1

Append (cost=1,000.00..134,401.07 rows=1,939 width=240) (actual time=191.470..878.845 rows=13,308 loops=1)

  • Buffers: shared hit=3799 read=96474
25. 31.478 868.927 ↓ 6.9 13,308 1

Gather (cost=1,000.00..134,367.54 rows=1,938 width=115) (actual time=191.467..868.927 rows=13,308 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=3799 read=96471
26. 837.449 837.449 ↓ 5.5 4,436 3 / 3

Parallel Seq Scan on concept_f concept_f_1 (cost=0.00..133,173.74 rows=808 width=115) (actual time=154.820..837.449 rows=4,436 loops=3)

  • Filter: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
  • Rows Removed by Filter: 1750596
  • Buffers: shared hit=3799 read=96471
27. 0.002 0.164 ↓ 0.0 0 1

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

  • Buffers: shared read=3
28. 0.162 0.162 ↓ 0.0 0 1

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

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Filter: ((vocabulary_id)::text = 'RxNorm'::text)
  • Buffers: shared read=3
29. 1,636.884 1,636.884 ↑ 9.1 195 13,308

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 time=0.007..0.123 rows=195 loops=13,308)

  • Index Cond: (ancestor_concept_id = concept_f_1.concept_id)
  • Heap Fetches: 0
  • Buffers: shared hit=2426862 read=6606
30. 133,854.358 472,544.757 ↑ 268,816,486.5 382,981,226 1

Materialize (cost=53,860,169,816,768,128.00..54,374,928,154,682,800.00 rows=102,951,667,582,934,208 width=19) (actual time=332,988.116..472,544.757 rows=382,981,226 loops=1)

  • Buffers: shared hit=7106 read=915441, temp read=1186703 written=1196585
31. 18,039.055 338,690.399 ↑ 8,066,891,949.6 12,762,247 1

Sort (cost=53,860,169,816,768,128.00..54,117,548,985,725,464.00 rows=102,951,667,582,934,208 width=19) (actual time=332,988.098..338,690.399 rows=12,762,247 loops=1)

  • Sort Key: concept_ancestor_f.ancestor_concept_id
  • Sort Method: external merge Disk: 429344kB
  • Buffers: shared hit=7106 read=915441, temp read=1186703 written=1196585
32. 30,308.033 320,651.344 ↑ 7,429,308,698.9 13,857,503 1

Merge Join (cost=10,149,749,333.35..1,544,285,260,980,159.00 rows=102,951,667,582,934,208 width=19) (actual time=150,434.722..320,651.344 rows=13,857,503 loops=1)

  • Merge Cond: (concept_f_2.concept_id = concept_relationship_f.concept_id_2)
  • Buffers: shared hit=7106 read=915441, temp read=1019102 written=1024626
33. 51,121.720 251,228.429 ↑ 10.1 58,897,078 1

Merge Join (cost=16,065,586.21..25,381,355.96 rows=594,683,746 width=12) (actual time=114,800.751..251,228.429 rows=58,897,078 loops=1)

  • Merge Cond: (concept_f_2.concept_id = concept_ancestor_f.descendant_concept_id)
  • Buffers: shared hit=4896 read=485394, temp read=941584 written=946920
34. 5,027.862 22,198.495 ↑ 1.0 5,185,423 1

Unique (cost=2,673,575.35..2,822,332.72 rows=5,409,359 width=240) (actual time=14,357.378..22,198.495 rows=5,185,423 loops=1)

  • Buffers: shared hit=4867 read=98095, temp read=281455 written=285462
35. 10,960.047 17,170.633 ↑ 1.0 5,185,423 1

Sort (cost=2,673,575.35..2,687,098.75 rows=5,409,359 width=240) (actual time=14,357.368..17,170.633 rows=5,185,423 loops=1)

  • 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
  • Sort Method: external merge Disk: 708656kB
  • Buffers: shared hit=4867 read=98095, temp read=281455 written=285462
36. 3,459.370 6,210.586 ↓ 1.0 5,409,856 1

Append (cost=0.00..238,195.98 rows=5,409,359 width=240) (actual time=0.037..6,210.586 rows=5,409,856 loops=1)

  • Buffers: shared hit=4867 read=98095
37. 2,580.581 2,580.581 ↓ 1.0 5,265,096 1

Seq Scan on concept_f concept_f_2 (cost=0.00..152,915.99 rows=5,264,599 width=115) (actual time=0.036..2,580.581 rows=5,265,096 loops=1)

  • Buffers: shared hit=2175 read=98095
38. 116.962 170.635 ↑ 1.0 144,760 1

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

  • Buffers: shared hit=2692
39. 53.673 53.673 ↑ 1.0 144,760 1

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

  • Buffers: shared hit=2692
40. 47,291.637 177,908.214 ↑ 1.0 71,612,462 1

Materialize (cost=13,392,010.86..13,750,289.26 rows=71,655,680 width=8) (actual time=100,443.209..177,908.214 rows=71,612,462 loops=1)

  • Buffers: shared hit=29 read=387299, temp read=660129 written=661458
41. 99,738.795 130,616.577 ↑ 1.0 71,612,462 1

Sort (cost=13,392,010.86..13,571,150.06 rows=71,655,680 width=8) (actual time=100,443.203..130,616.577 rows=71,612,462 loops=1)

  • Sort Key: concept_ancestor_f.descendant_concept_id
  • Sort Method: external merge Disk: 1260872kB
  • Buffers: shared hit=29 read=387299, temp read=660129 written=661458
42. 30,877.782 30,877.782 ↑ 1.0 71,655,508 1

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

  • Buffers: shared hit=29 read=387299
43. 4,705.057 39,114.882 ↑ 2,498.6 13,857,482 1

Materialize (cost=10,133,683,747.15..10,306,803,776.55 rows=34,624,005,880 width=19) (actual time=34,364.751..39,114.882 rows=13,857,482 loops=1)

  • Buffers: shared hit=2210 read=430047, temp read=77518 written=77706
44. 122.858 34,409.825 ↑ 349,786.9 98,986 1

Sort (cost=10,133,683,747.15..10,220,243,761.85 rows=34,624,005,880 width=19) (actual time=34,364.745..34,409.825 rows=98,986 loops=1)

  • Sort Key: concept_relationship_f.concept_id_2
  • Sort Method: external merge Disk: 3008kB
  • Buffers: shared hit=2210 read=430047, temp read=77518 written=77706
45. 2,318.645 34,286.967 ↑ 349,786.9 98,986 1

Merge Join (cost=2,733,467.49..522,256,006.41 rows=34,624,005,880 width=19) (actual time=20,759.556..34,286.967 rows=98,986 loops=1)

  • Merge Cond: (b.concept_id = concept_relationship_f.concept_id_1)
  • Buffers: shared hit=2210 read=430047, temp read=77142 written=77328
46. 118.737 4,295.705 ↑ 12.3 99,259 1

Sort (cost=592,172.81..595,217.56 rows=1,217,898 width=19) (actual time=4,253.087..4,295.705 rows=99,259 loops=1)

  • Sort Key: b.concept_id
  • Sort Method: external merge Disk: 3024kB
  • Buffers: shared hit=2210 read=103402, temp read=16644 written=16676
47. 456.307 4,176.968 ↑ 12.3 99,259 1

Hash Join (cost=377,368.29..444,088.41 rows=1,217,898 width=19) (actual time=2,471.781..4,176.968 rows=99,259 loops=1)

  • Hash Cond: ((b.concept_code)::text = (a_2.ndc)::text)
  • Buffers: shared hit=2210 read=103402, temp read=16266 written=16296
48. 424.531 3,522.160 ↑ 1.0 577,523 1

Subquery Scan on b (cost=367,360.52..389,787.89 rows=598,063 width=36) (actual time=2,271.751..3,522.160 rows=577,523 loops=1)

  • Buffers: shared hit=2210 read=98063, temp read=13666 written=13696
49. 520.453 3,097.629 ↑ 1.0 577,523 1

Unique (cost=367,360.52..383,807.26 rows=598,063 width=240) (actual time=2,271.747..3,097.629 rows=577,523 loops=1)

  • Buffers: shared hit=2210 read=98063, temp read=13666 written=13696
50. 881.879 2,577.176 ↑ 1.0 577,523 1

Sort (cost=367,360.52..368,855.68 rows=598,063 width=240) (actual time=2,271.744..2,577.176 rows=577,523 loops=1)

  • 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
  • Sort Method: external merge Disk: 68640kB
  • Buffers: shared hit=2210 read=98063, temp read=13666 written=13696
51. 370.106 1,695.297 ↑ 1.0 577,523 1

Append (cost=0.00..175,058.57 rows=598,063 width=240) (actual time=56.236..1,695.297 rows=577,523 loops=1)

  • Buffers: shared hit=2210 read=98063
52. 1,325.145 1,325.145 ↑ 1.0 577,523 1

Seq Scan on concept_f concept_f_3 (cost=0.00..166,077.49 rows=598,058 width=115) (actual time=56.233..1,325.145 rows=577,523 loops=1)

  • Filter: ((vocabulary_id)::text = 'NDC'::text)
  • Rows Removed by Filter: 4687573
  • Buffers: shared hit=2207 read=98063
53. 0.006 0.046 ↓ 0.0 0 1

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

  • Buffers: shared hit=3
54. 0.040 0.040 ↓ 0.0 0 1

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

  • Index Cond: ((vocabulary_id)::text = 'NDC'::text)
  • Buffers: shared hit=3
55. 86.611 198.501 ↑ 1.0 159,145 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2837kB
  • Buffers: shared read=5339, temp written=692
56. 111.890 111.890 ↑ 1.0 159,145 1

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

  • Buffers: shared read=5339
57. 3,797.116 27,672.617 ↑ 1.0 5,657,694 1

Materialize (cost=2,141,294.68..2,311,870.57 rows=5,685,863 width=8) (actual time=16,417.888..27,672.617 rows=5,657,694 loops=1)

  • Buffers: shared read=326645, temp read=60498 written=60652
58. 5,005.818 23,875.501 ↑ 1.0 5,617,887 1

Unique (cost=2,141,294.68..2,240,797.28 rows=5,685,863 width=82) (actual time=16,417.881..23,875.501 rows=5,617,887 loops=1)

  • Buffers: shared read=326645, temp read=60498 written=60652
59. 7,205.861 18,869.683 ↑ 1.0 5,617,887 1

Sort (cost=2,141,294.68..2,155,509.33 rows=5,685,863 width=82) (actual time=16,417.878..18,869.683 rows=5,617,887 loops=1)

  • 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
  • Sort Method: external merge Disk: 186872kB
  • Buffers: shared read=326645, temp read=60498 written=60652
60. 3,582.438 11,663.822 ↑ 1.0 5,620,543 1

Append (cost=0.00..959,211.76 rows=5,685,863 width=82) (actual time=0.050..11,663.822 rows=5,620,543 loops=1)

  • Buffers: shared read=326645
61. 8,081.350 8,081.350 ↑ 1.0 5,620,542 1

Seq Scan on concept_relationship_f (cost=0.00..873,922.80 rows=5,685,862 width=37) (actual time=0.048..8,081.350 rows=5,620,542 loops=1)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Rows Removed by Filter: 38173986
  • Buffers: shared read=326644
62. 0.034 0.034 ↑ 1.0 1 1

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

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Buffers: shared read=1
63. 833.114 372,227.111 ↓ 47.8 459,303 1

HashAggregate (cost=9,525,921,792,137,772.00..9,525,921,792,137,868.00 rows=9,600 width=16) (actual time=371,965.111..372,227.111 rows=459,303 loops=1)

  • Group Key: a_3.mktdef_nm, concept_f_4.concept_id
  • Buffers: shared hit=2448313 read=1105649, temp read=1122101 written=1132470
64. 1,435.415 371,393.997 ↑ 1,931,893,485,248.9 770,774 1

Merge Join (cost=180,580,941,790,187.75..2,080,655,446,141,647.25 rows=1,489,053,269,199,224,832 width=16) (actual time=368,719.730..371,393.997 rows=770,774 loops=1)

  • Merge Cond: (concept_f_5.concept_id = concept_ancestor_f_2.ancestor_concept_id)
  • Buffers: shared hit=2448313 read=1105649, temp read=1122101 written=1132470
65. 2,575.420 24,131.765 ↑ 5.0 2,336,185 1

Sort (cost=3,740,021.52..3,769,237.71 rows=11,686,474 width=12) (actual time=23,162.669..24,131.765 rows=2,336,185 loops=1)

  • Sort Key: concept_f_5.concept_id
  • Sort Method: external merge Disk: 50280kB
  • Buffers: shared hit=2440656 read=196030, temp read=124660 written=124898
66. 2,834.906 21,556.345 ↑ 5.0 2,336,185 1

Merge Join (cost=1,696,077.45..1,968,685.68 rows=11,686,474 width=12) (actual time=12,546.759..21,556.345 rows=2,336,185 loops=1)

  • Merge Cond: (concept_f_4.concept_id = concept_ancestor_f_3.descendant_concept_id)
  • Buffers: shared hit=2440656 read=196030, temp read=113732 written=113928
67. 2,152.127 9,773.677 ↑ 1.0 2,206,885 1

Unique (cost=938,042.77..999,041.95 rows=2,218,152 width=240) (actual time=6,448.789..9,773.677 rows=2,206,885 loops=1)

  • Buffers: shared hit=4931 read=98031, temp read=100924 written=101074
68. 4,224.331 7,621.550 ↑ 1.0 2,206,885 1

Sort (cost=938,042.77..943,588.15 rows=2,218,152 width=240) (actual time=6,448.785..7,621.550 rows=2,206,885 loops=1)

  • 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
  • Sort Method: external merge Disk: 314448kB
  • Buffers: shared hit=4931 read=98031, temp read=100924 written=101074
69. 1,408.131 3,397.219 ↑ 1.0 2,206,903 1

Append (cost=0.00..203,851.27 rows=2,218,152 width=240) (actual time=0.070..3,397.219 rows=2,206,903 loops=1)

  • Buffers: shared hit=4931 read=98031
70. 1,966.671 1,966.671 ↑ 1.0 2,206,903 1

Seq Scan on concept_f concept_f_4 (cost=0.00..166,077.49 rows=2,218,151 width=115) (actual time=0.068..1,966.671 rows=2,206,903 loops=1)

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 3058193
  • Buffers: shared hit=2239 read=98031
71. 0.002 22.417 ↓ 0.0 0 1

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

  • Buffers: shared hit=2692
72. 22.415 22.415 ↓ 0.0 0 1

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

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 144760
  • Buffers: shared hit=2692
73. 1,726.436 8,947.762 ↑ 1.3 2,590,553 1

Materialize (cost=758,034.68..775,204.77 rows=3,434,018 width=12) (actual time=6,075.796..8,947.762 rows=2,590,553 loops=1)

  • Buffers: shared hit=2435725 read=97999, temp read=12808 written=12854
74. 3,063.159 7,221.326 ↑ 1.3 2,590,553 1

Sort (cost=758,034.68..766,619.73 rows=3,434,018 width=12) (actual time=6,075.775..7,221.326 rows=2,590,553 loops=1)

  • Sort Key: concept_ancestor_f_3.descendant_concept_id
  • Sort Method: external merge Disk: 55768kB
  • Buffers: shared hit=2435725 read=97999, temp read=12808 written=12854
75. 1,751.031 4,158.167 ↑ 1.3 2,590,553 1

Nested Loop (cost=134,450.11..267,870.85 rows=3,434,018 width=12) (actual time=851.426..4,158.167 rows=2,590,553 loops=1)

  • Buffers: shared hit=2435725 read=97999
76. 33.873 863.408 ↓ 6.9 13,308 1

HashAggregate (cost=134,449.54..134,468.93 rows=1,939 width=240) (actual time=851.338..863.408 rows=13,308 loops=1)

  • 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
  • Buffers: shared hit=2274 read=97999
77. 9.656 829.535 ↓ 6.9 13,308 1

Append (cost=1,000.00..134,401.07 rows=1,939 width=240) (actual time=192.037..829.535 rows=13,308 loops=1)

  • Buffers: shared hit=2274 read=97999
78. 31.266 819.766 ↓ 6.9 13,308 1

Gather (cost=1,000.00..134,367.54 rows=1,938 width=115) (actual time=192.035..819.766 rows=13,308 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2271 read=97999
79. 788.500 788.500 ↓ 5.5 4,436 3 / 3

Parallel Seq Scan on concept_f concept_f_5 (cost=0.00..133,173.74 rows=808 width=115) (actual time=155.903..788.500 rows=4,436 loops=3)

  • Filter: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
  • Rows Removed by Filter: 1750596
  • Buffers: shared hit=2271 read=97999
80. 0.003 0.113 ↓ 0.0 0 1

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

  • Buffers: shared hit=3
81. 0.110 0.110 ↓ 0.0 0 1

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

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Filter: ((vocabulary_id)::text = 'RxNorm'::text)
  • Buffers: shared hit=3
82. 1,543.728 1,543.728 ↑ 9.1 195 13,308

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 time=0.006..0.116 rows=195 loops=13,308)

  • Index Cond: (ancestor_concept_id = concept_f_5.concept_id)
  • Heap Fetches: 0
  • Buffers: shared hit=2433451
83. 275.229 345,826.817 ↑ 555,764,465.5 788,643 1

Materialize (cost=180,580,938,050,166.22..182,772,436,826,827.28 rows=438,299,755,332,213 width=16) (actual time=345,543.653..345,826.817 rows=788,643 loops=1)

  • Buffers: shared hit=7657 read=909619, temp read=997441 written=1007572
84. 20.808 345,551.588 ↑ 23,640,763,502.3 18,540 1

Sort (cost=180,580,938,050,166.22..181,676,687,438,496.75 rows=438,299,755,332,213 width=16) (actual time=345,543.636..345,551.588 rows=18,540 loops=1)

  • Sort Key: concept_ancestor_f_2.ancestor_concept_id
  • Sort Method: quicksort Memory: 1971kB
  • Buffers: shared hit=7657 read=909619, temp read=997441 written=1007572
85. 22,563.600 345,530.780 ↑ 22,719,249,187.9 19,292 1

Merge Join (cost=48,261,020.14..6,574,554,644,333.03 rows=438,299,755,332,213 width=16) (actual time=144,892.434..345,530.780 rows=19,292 loops=1)

  • Merge Cond: (concept_relationship_f_1.concept_id_2 = concept_f_7.concept_id)
  • Buffers: shared hit=7657 read=909619, temp read=997441 written=1007572
86. 0.929 27,858.611 ↑ 207,030.9 712 1

Sort (cost=32,195,433.93..32,563,948.93 rows=147,405,998 width=16) (actual time=27,858.128..27,858.611 rows=712 loops=1)

  • Sort Key: concept_relationship_f_1.concept_id_2
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=2441 read=424545, temp read=55857 written=60652
87. 1,758.019 27,857.682 ↑ 207,030.9 712 1

Merge Join (cost=2,271,047.89..4,638,525.02 rows=147,405,998 width=16) (actual time=17,663.485..27,857.682 rows=712 loops=1)

  • Merge Cond: (concept_f_6.concept_id = concept_relationship_f_1.concept_id_1)
  • Buffers: shared hit=2441 read=424545, temp read=55857 written=60652
88. 0.880 825.580 ↑ 7.3 712 1

Sort (cost=129,753.21..129,766.18 rows=5,185 width=16) (actual time=825.278..825.580 rows=712 loops=1)

  • Sort Key: concept_f_6.concept_id
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=2408 read=97933
89. 2.331 824.700 ↑ 7.3 712 1

Hash Join (cost=129,275.36..129,433.30 rows=5,185 width=16) (actual time=820.008..824.700 rows=712 loops=1)

  • Hash Cond: ((concept_f_6.concept_code)::text = (a_3.prc_cd)::text)
  • Buffers: shared hit=2408 read=97933
90. 10.664 820.840 ↑ 1.4 2,681 1

HashAggregate (cost=129,216.02..129,252.92 rows=3,690 width=240) (actual time=818.454..820.840 rows=2,681 loops=1)

  • 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
  • Buffers: shared hit=2408 read=97903
91. 1.966 810.176 ↑ 1.4 2,681 1

Append (cost=1,000.00..129,123.77 rows=3,690 width=240) (actual time=2.486..810.176 rows=2,681 loops=1)

  • Buffers: shared hit=2370 read=97903
92. 52.699 808.171 ↑ 1.4 2,681 1

Gather (cost=1,000.00..129,058.29 rows=3,685 width=115) (actual time=2.485..808.171 rows=2,681 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2367 read=97903
93. 755.472 755.472 ↑ 1.7 894 3 / 3

Parallel Seq Scan on concept_f concept_f_6 (cost=0.00..127,689.79 rows=1,535 width=115) (actual time=479.931..755.472 rows=894 loops=3)

  • Filter: ((vocabulary_id)::text = 'HCPCS'::text)
  • Rows Removed by Filter: 1754138
  • Buffers: shared hit=2367 read=97903
94. 0.002 0.039 ↓ 0.0 0 1

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

  • Buffers: shared hit=3
95. 0.037 0.037 ↓ 0.0 0 1

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

  • Index Cond: ((vocabulary_id)::text = 'HCPCS'::text)
  • Buffers: shared hit=3
96. 0.670 1.529 ↑ 1.0 1,304 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared read=30
97. 0.859 0.859 ↑ 1.0 1,304 1

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

  • Buffers: shared read=30
98. 2,972.542 25,274.083 ↑ 1.3 4,433,314 1

Materialize (cost=2,141,294.68..2,311,870.57 rows=5,685,863 width=8) (actual time=16,464.422..25,274.083 rows=4,433,314 loops=1)

  • Buffers: shared hit=33 read=326612, temp read=55857 written=60652
99. 3,917.586 22,301.541 ↑ 1.3 4,433,078 1

Unique (cost=2,141,294.68..2,240,797.28 rows=5,685,863 width=82) (actual time=16,464.413..22,301.541 rows=4,433,078 loops=1)

  • Buffers: shared hit=33 read=326612, temp read=55857 written=60652
100. 6,708.105 18,383.955 ↑ 1.3 4,433,078 1

Sort (cost=2,141,294.68..2,155,509.33 rows=5,685,863 width=82) (actual time=16,464.410..18,383.955 rows=4,433,078 loops=1)

  • 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
  • Sort Method: external merge Disk: 186872kB
  • Buffers: shared hit=33 read=326612, temp read=55857 written=60652
101. 3,590.603 11,675.850 ↑ 1.0 5,620,543 1

Append (cost=0.00..959,211.76 rows=5,685,863 width=82) (actual time=0.072..11,675.850 rows=5,620,543 loops=1)

  • Buffers: shared hit=33 read=326612
102. 8,085.231 8,085.231 ↑ 1.0 5,620,542 1

Seq Scan on concept_relationship_f concept_relationship_f_1 (cost=0.00..873,922.80 rows=5,685,862 width=37) (actual time=0.070..8,085.231 rows=5,620,542 loops=1)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Rows Removed by Filter: 38173986
  • Buffers: shared hit=32 read=326612
103. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Buffers: shared hit=1
104. 43,062.282 295,108.569 ↑ 10.1 58,902,079 1

Materialize (cost=16,065,586.21..26,868,065.32 rows=594,683,746 width=12) (actual time=114,991.661..295,108.569 rows=58,902,079 loops=1)

  • Buffers: shared hit=5216 read=485074, temp read=941584 written=946920
105. 51,358.980 252,046.287 ↑ 10.1 58,893,265 1

Merge Join (cost=16,065,586.21..25,381,355.96 rows=594,683,746 width=12) (actual time=114,991.654..252,046.287 rows=58,893,265 loops=1)

  • Merge Cond: (concept_f_7.concept_id = concept_ancestor_f_2.descendant_concept_id)
  • Buffers: shared hit=5216 read=485074, temp read=941584 written=946920
106. 5,077.772 22,136.595 ↑ 1.0 5,185,208 1

Unique (cost=2,673,575.35..2,822,332.72 rows=5,409,359 width=240) (actual time=14,210.820..22,136.595 rows=5,185,208 loops=1)

  • Buffers: shared hit=5155 read=97807, temp read=281455 written=285462
107. 10,847.333 17,058.823 ↑ 1.0 5,185,208 1

Sort (cost=2,673,575.35..2,687,098.75 rows=5,409,359 width=240) (actual time=14,210.817..17,058.823 rows=5,185,208 loops=1)

  • 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
  • Sort Method: external merge Disk: 708656kB
  • Buffers: shared hit=5155 read=97807, temp read=281455 written=285462
108. 3,460.759 6,211.490 ↓ 1.0 5,409,856 1

Append (cost=0.00..238,195.98 rows=5,409,359 width=240) (actual time=0.058..6,211.490 rows=5,409,856 loops=1)

  • Buffers: shared hit=5155 read=97807
109. 2,576.634 2,576.634 ↓ 1.0 5,265,096 1

Seq Scan on concept_f concept_f_7 (cost=0.00..152,915.99 rows=5,264,599 width=115) (actual time=0.056..2,576.634 rows=5,265,096 loops=1)

  • Buffers: shared hit=2463 read=97807
110. 119.318 174.097 ↑ 1.0 144,760 1

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

  • Buffers: shared hit=2692
111. 54.779 54.779 ↑ 1.0 144,760 1

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

  • Buffers: shared hit=2692
112. 47,269.884 178,550.712 ↑ 1.0 71,608,649 1

Materialize (cost=13,392,010.86..13,750,289.26 rows=71,655,680 width=8) (actual time=100,780.668..178,550.712 rows=71,608,649 loops=1)

  • Buffers: shared hit=61 read=387267, temp read=660129 written=661458
113. 100,367.126 131,280.828 ↑ 1.0 71,608,649 1

Sort (cost=13,392,010.86..13,571,150.06 rows=71,655,680 width=8) (actual time=100,780.662..131,280.828 rows=71,608,649 loops=1)

  • Sort Key: concept_ancestor_f_2.descendant_concept_id
  • Sort Method: external merge Disk: 1260872kB
  • Buffers: shared hit=61 read=387267, temp read=660129 written=661458
114. 30,913.702 30,913.702 ↑ 1.0 71,655,508 1

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

  • Buffers: shared hit=61 read=387267
115. 0.007 1,352,018.602 ↓ 0.0 0 1

Sort (cost=1,029.69..1,039.98 rows=4,116 width=126) (actual time=1,352,018.601..1,352,018.602 rows=0 loops=1)

  • Sort Key: a.mktdef_nm, drug_exposure_f.person_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4892540 read=2220673, temp read=2444739 written=2471745
116. 419.711 1,352,018.595 ↓ 0.0 0 1

Hash Join (cost=637.00..782.59 rows=4,116 width=126) (actual time=1,352,018.594..1,352,018.595 rows=0 loops=1)

  • Hash Cond: (drug_exposure_f.drug_concept_id = a.concept_id)
  • Buffers: shared hit=4892540 read=2220673, temp read=2444739 written=2471745
117. 0.041 0.041 ↑ 1.0 42 1

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

  • Buffers: shared hit=1
118. 1,091.932 1,351,598.843 ↓ 89.3 1,749,598 1

Hash (cost=392.00..392.00 rows=19,600 width=122) (actual time=1,351,598.843..1,351,598.843 rows=1,749,598 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 32 (originally 1) Memory Usage: 3784kB
  • Buffers: shared hit=4892539 read=2220673, temp read=2433464 written=2468844
119. 1,350,506.911 1,350,506.911 ↓ 89.3 1,749,598 1

CTE Scan on cte_descendant_concept_product a (cost=0.00..392.00 rows=19,600 width=122) (actual time=1,347,526.602..1,350,506.911 rows=1,749,598 loops=1)

  • Buffers: shared hit=4892539 read=2220673, temp read=2433464 written=2460470
120. 0.001 887.069 ↓ 0.0 0 1

Unique (cost=12,070,012,784,843.08..12,070,012,785,601.33 rows=3,370 width=126) (actual time=887.069..887.069 rows=0 loops=1)

  • Buffers: shared hit=2501 read=97998
121. 0.003 887.068 ↓ 0.0 0 1

Unique (cost=12,070,012,784,843.08..12,070,012,785,095.83 rows=33,700 width=126) (actual time=887.067..887.068 rows=0 loops=1)

  • Buffers: shared hit=2501 read=97998
122.          

CTE cte_descendant_concept_diagnosis

123. 0.182 886.964 ↓ 0.0 0 1

HashAggregate (cost=12,070,012,778,603.81..12,070,012,778,703.81 rows=10,000 width=16) (actual time=886.964..886.964 rows=0 loops=1)

  • Group Key: a_4.mktdef_nm, f.concept_id
  • Buffers: shared hit=2501 read=97995
124. 0.002 886.782 ↓ 0.0 0 1

Merge Join (cost=25,312,651.61..9,052,535,796,916.76 rows=603,495,396,337,411 width=16) (actual time=886.781..886.782 rows=0 loops=1)

  • Merge Cond: (concept_relationship_f_2.concept_id_2 = concept_f_9.concept_id)
  • Buffers: shared hit=2501 read=97995
125. 0.005 886.780 ↓ 0.0 0 1

Sort (cost=6,355,682.12..6,412,896.12 rows=22,885,599 width=16) (actual time=886.779..886.780 rows=0 loops=1)

  • Sort Key: concept_relationship_f_2.concept_id_2
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2501 read=97995
126. 0.000 886.775 ↓ 0.0 0 1

Merge Join (cost=2,276,275.50..2,775,924.74 rows=22,885,599 width=16) (actual time=886.774..886.775 rows=0 loops=1)

  • Merge Cond: (concept_f_8.concept_id = concept_relationship_f_2.concept_id_1)
  • Buffers: shared hit=2501 read=97995
127. 0.009 886.775 ↓ 0.0 0 1

Sort (cost=134,980.83..134,982.84 rows=805 width=16) (actual time=886.772..886.775 rows=0 loops=1)

  • Sort Key: concept_f_8.concept_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2501 read=97995
128. 0.023 886.766 ↓ 0.0 0 1

Hash Join (cost=134,877.04..134,941.97 rows=805 width=16) (actual time=886.766..886.766 rows=0 loops=1)

  • 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)))
  • Buffers: shared hit=2501 read=97995
129. 0.024 875.456 ↓ 0.0 0 1

HashAggregate (cost=134,427.19..134,441.99 rows=1,480 width=240) (actual time=875.455..875.456 rows=0 loops=1)

  • 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
  • Buffers: shared hit=2501 read=97775
130. 0.000 875.432 ↓ 0.0 0 1

Append (cost=1,000.00..134,390.19 rows=1,480 width=240) (actual time=875.432..875.432 rows=0 loops=1)

  • Buffers: shared hit=2501 read=97775
131. 45.333 880.760 ↓ 0.0 0 1

Gather (cost=1,000.00..134,320.74 rows=1,470 width=115) (actual time=875.376..880.760 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2495 read=97775
132. 835.427 835.427 ↓ 0.0 0 3 / 3

Parallel Seq Scan on concept_f concept_f_8 (cost=0.00..133,173.74 rows=612 width=115) (actual time=835.426..835.427 rows=0 loops=3)

  • Filter: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
  • Rows Removed by Filter: 1755032
  • Buffers: shared hit=2495 read=97775
133. 0.003 0.053 ↓ 0.0 0 1

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

  • Buffers: shared hit=6
134. 0.008 0.050 ↓ 0.0 0 1

Bitmap Heap Scan on concept_amend concept_amend_8 (cost=8.92..47.24 rows=10 width=115) (actual time=0.049..0.050 rows=0 loops=1)

  • Recheck Cond: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
  • Buffers: shared hit=6
135. 0.003 0.042 ↓ 0.0 0 1

BitmapOr (cost=8.92..8.92 rows=10 width=0) (actual time=0.041..0.042 rows=0 loops=1)

  • Buffers: shared hit=6
136. 0.029 0.029 ↓ 0.0 0 1

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

  • Index Cond: ((vocabulary_id)::text = 'ICD9CM'::text)
  • Buffers: shared hit=3
137. 0.010 0.010 ↓ 0.0 0 1

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

  • Index Cond: ((vocabulary_id)::text = 'ICD10CM'::text)
  • Buffers: shared hit=3
138. 4.628 11.287 ↓ 1.2 9,143 1

Hash (cost=357.14..357.14 rows=7,417 width=21) (actual time=11.286..11.287 rows=9,143 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 607kB
  • Buffers: shared read=220
139. 6.659 6.659 ↓ 1.2 9,143 1

Seq Scan on diagnosis_list a_4 (cost=0.00..357.14 rows=7,417 width=21) (actual time=0.032..6.659 rows=9,143 loops=1)

  • Filter: (((diag_vers_typ_id)::text = '1'::text) OR ((diag_vers_typ_id)::text = '2'::text))
  • Buffers: shared read=220
140. 0.000 0.000 ↓ 0.0 0

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

141. 0.000 0.000 ↓ 0.0 0

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

142. 0.000 0.000 ↓ 0.0 0

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

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

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

144. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Materialize (cost=18,956,969.48..111,567,291.13 rows=5,274,018,795 width=12) (never executed)

147. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=18,956,969.48..98,382,244.15 rows=5,274,018,795 width=12) (never executed)

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

Unique (cost=2,673,575.35..2,822,332.72 rows=5,409,359 width=240) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,673,575.35..2,687,098.75 rows=5,409,359 width=240) (never executed)

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

Append (cost=0.00..238,195.98 rows=5,409,359 width=240) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Seq Scan on concept_f concept_f_9 (cost=0.00..152,915.99 rows=5,264,599 width=115) (never executed)

152. 0.000 0.000 ↓ 0.0 0

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

153. 0.000 0.000 ↓ 0.0 0

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

154. 0.000 0.000 ↓ 0.0 0

Materialize (cost=16,283,394.13..16,480,632.66 rows=39,447,705 width=8) (never executed)

155. 0.000 0.000 ↓ 0.0 0

Sort (cost=16,283,394.13..16,382,013.40 rows=39,447,705 width=8) (never executed)

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

Hash Join (cost=2,568,989.10..9,688,411.54 rows=39,447,705 width=8) (never executed)

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

Subquery Scan on f (cost=289,503.30..302,959.20 rows=358,824 width=4) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Unique (cost=289,503.30..299,370.96 rows=358,824 width=240) (never executed)

159. 0.000 0.000 ↓ 0.0 0

Sort (cost=289,503.30..290,400.36 rows=358,824 width=240) (never executed)

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

Append (cost=1,000.00..175,448.55 rows=358,824 width=240) (never executed)

161. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..170,056.04 rows=358,823 width=115) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
162. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on concept_f concept_f_10 (cost=0.00..133,173.74 rows=149,510 width=115) (never executed)

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

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

164. 0.000 0.000 ↓ 0.0 0

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

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

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

166. 0.000 0.000 ↓ 0.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) (never executed)

167. 0.015 887.065 ↓ 0.0 0 1

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

  • Sort Key: a_1.mktdef_nm, condition_occurrence_f.person_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2501 read=97998
168. 0.004 887.050 ↓ 0.0 0 1

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

  • Merge Cond: (condition_occurrence_f.condition_concept_id = a_1.concept_id)
  • Buffers: shared hit=2501 read=97998
169. 0.071 0.071 ↑ 674.0 1 1

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

  • Buffers: shared read=3
170. 0.006 886.975 ↓ 0.0 0 1

Sort (cost=864.39..889.39 rows=10,000 width=122) (actual time=886.974..886.975 rows=0 loops=1)

  • Sort Key: a_1.concept_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2501 read=97995
171. 886.969 886.969 ↓ 0.0 0 1

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

  • Buffers: shared hit=2501 read=97995
Planning time : 11.279 ms
Execution time : 1,354,170.299 ms