explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iSxP : new_plan_after_indexes

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,589,327.166 ↓ 0.0 0 1

GroupAggregate (cost=2,373,267,677,086,502,912.00..2,373,267,677,086,502,912.00 rows=200 width=126) (actual time=1,589,327.166..1,589,327.166 rows=0 loops=1)

  • Group Key: a.mktdef_nm
  • Buffers: shared hit=37425357 read=34686694, temp read=1784577 written=1810287
2. 0.002 1,589,327.164 ↓ 0.0 0 1

Unique (cost=2,373,267,677,086,502,912.00..2,373,267,677,086,502,912.00 rows=3,570 width=126) (actual time=1,589,327.164..1,589,327.164 rows=0 loops=1)

  • Buffers: shared hit=37425357 read=34686694, temp read=1784577 written=1810287
3. 0.051 1,589,327.162 ↓ 0.0 0 1

Sort (cost=2,373,267,677,086,502,912.00..2,373,267,677,086,502,912.00 rows=3,570 width=126) (actual time=1,589,327.162..1,589,327.162 rows=0 loops=1)

  • Sort Key: a.mktdef_nm, drug_exposure_f.person_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=37425357 read=34686694, temp read=1784577 written=1810287
4. 0.005 1,589,327.111 ↓ 0.0 0 1

Append (cost=2,373,254,950,126,576,128.00..2,373,267,677,086,502,912.00 rows=3,570 width=126) (actual time=1,589,327.111..1,589,327.111 rows=0 loops=1)

  • Buffers: shared hit=37425348 read=34686694, temp read=1784577 written=1810287
5. 0.001 1,589,308.447 ↓ 0.0 0 1

Unique (cost=2,373,254,950,126,576,128.00..2,373,254,950,126,576,128.00 rows=200 width=126) (actual time=1,589,308.447..1,589,308.447 rows=0 loops=1)

  • Buffers: shared hit=37425342 read=34686465, temp read=1784577 written=1810287
6. 0.002 1,589,308.446 ↓ 0.0 0 1

Unique (cost=2,373,254,950,126,576,128.00..2,373,254,950,126,576,128.00 rows=200 width=126) (actual time=1,589,308.446..1,589,308.446 rows=0 loops=1)

  • Buffers: shared hit=37425342 read=34686465, temp read=1784577 written=1810287
7.          

CTE cte_descendant_concept_product

8. 3,219.792 1,585,518.926 ↓ 89.3 1,749,598 1

HashAggregate (cost=2,373,254,950,126,575,104.00..2,373,254,950,126,575,104.00 rows=19,600 width=122) (actual time=1,584,405.581..1,585,518.926 rows=1,749,598 loops=1)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
  • Buffers: shared hit=37425341 read=34686465, temp read=1773302 written=1792495
9. 1,609.229 1,582,299.134 ↓ 111.9 2,192,443 1

Append (cost=2,363,349,394,171,023,360.00..2,373,254,950,126,575,104.00 rows=19,600 width=122) (actual time=1,012,591.586..1,582,299.134 rows=2,192,443 loops=1)

  • Buffers: shared hit=37425341 read=34686465, temp read=1773302 written=1792495
10. 204,311.239 1,013,901.679 ↓ 173.3 1,733,140 1

HashAggregate (cost=2,363,349,394,171,023,360.00..2,363,349,394,171,023,360.00 rows=10,000 width=19) (actual time=1,012,591.584..1,013,901.679 rows=1,733,140 loops=1)

  • Group Key: a_2.mktdef_nm, concept_f.concept_id
  • Buffers: shared hit=72767 read=952170, temp read=1311330 written=1321483
11. 278,850.305 809,590.440 ↑ 994,642,942,980.4 370,324,724 1

Merge Join (cost=51,655,552,207,142,440.00..521,645,027,482,211,776.00 rows=368,340,873,337,762,283,520 width=19) (actual time=388,441.182..809,590.440 rows=370,324,724 loops=1)

  • Merge Cond: (concept_f_1.concept_id = concept_ancestor_f.ancestor_concept_id)
  • Buffers: shared hit=72767 read=952170, temp read=1311330 written=1321483
12. 2,823.546 24,657.090 ↑ 5.5 2,336,185 1

Sort (cost=10,951,432.89..10,983,495.95 rows=12,825,221 width=12) (actual time=23,538.947..24,657.090 rows=2,336,185 loops=1)

  • Sort Key: concept_f_1.concept_id
  • Sort Method: external merge Disk: 50280kB
  • Buffers: shared hit=62326 read=128622, temp read=124660 written=124898
13. 2,940.894 21,833.544 ↑ 5.5 2,336,185 1

Merge Join (cost=8,708,698.42..8,998,888.50 rows=12,825,221 width=12) (actual time=12,459.047..21,833.544 rows=2,336,185 loops=1)

  • Merge Cond: (concept_f.concept_id = concept_ancestor_f_1.descendant_concept_id)
  • Buffers: shared hit=62326 read=128622, temp read=113732 written=113928
14. 2,252.084 10,193.076 ↑ 1.0 2,206,885 1

Unique (cost=934,625.38..995,366.06 rows=2,208,752 width=240) (actual time=6,703.856..10,193.076 rows=2,206,885 loops=1)

  • Buffers: shared hit=3047 read=99918, temp read=100924 written=101074
15. 4,443.232 7,940.992 ↑ 1.0 2,206,885 1

Sort (cost=934,625.38..940,147.26 rows=2,208,752 width=240) (actual time=6,703.832..7,940.992 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=3047 read=99918, temp read=100924 written=101074
16. 1,429.238 3,497.760 ↑ 1.0 2,206,903 1

Append (cost=0.00..203,613.34 rows=2,208,752 width=240) (actual time=0.066..3,497.760 rows=2,206,903 loops=1)

  • Buffers: shared hit=3044 read=99918
17. 2,039.858 2,039.858 ↑ 1.0 2,206,903 1

Seq Scan on concept_f (cost=0.00..165,980.56 rows=2,208,751 width=115) (actual time=0.065..2,039.858 rows=2,206,903 loops=1)

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

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

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

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

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 144760
  • Buffers: shared hit=2692
20. 1,776.341 8,699.574 ↑ 1.5 2,590,553 1

Materialize (cost=7,774,073.05..7,792,996.41 rows=3,784,672 width=12) (actual time=5,733.175..8,699.574 rows=2,590,553 loops=1)

  • Buffers: shared hit=59279 read=28704, temp read=12808 written=12854
21. 3,379.785 6,923.233 ↑ 1.5 2,590,553 1

Sort (cost=7,774,073.05..7,783,534.73 rows=3,784,672 width=12) (actual time=5,733.169..6,923.233 rows=2,590,553 loops=1)

  • Sort Key: concept_ancestor_f_1.descendant_concept_id
  • Sort Method: external merge Disk: 55768kB
  • Buffers: shared hit=59279 read=28704, temp read=12808 written=12854
22. 1,902.555 3,543.448 ↑ 1.5 2,590,553 1

Nested Loop (cost=14,784.57..7,231,204.77 rows=3,784,672 width=12) (actual time=69.959..3,543.448 rows=2,590,553 loops=1)

  • Buffers: shared hit=59279 read=28704
23. 29.670 83.857 ↓ 6.2 13,308 1

HashAggregate (cost=14,750.81..14,772.18 rows=2,137 width=240) (actual time=69.870..83.857 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 read=1613
24. 8.650 54.187 ↓ 6.2 13,308 1

Append (cost=7,093.71..14,697.38 rows=2,137 width=240) (actual time=32.257..54.187 rows=13,308 loops=1)

  • Buffers: shared read=1613
25. 13.300 45.439 ↓ 6.2 13,308 1

Bitmap Heap Scan on concept_f concept_f_1 (cost=7,093.71..14,660.89 rows=2,136 width=115) (actual time=32.254..45.439 rows=13,308 loops=1)

  • Recheck Cond: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
  • Heap Blocks: exact=670
  • Buffers: shared read=1610
26. 0.212 32.139 ↓ 0.0 0 1

BitmapAnd (cost=7,093.71..7,093.71 rows=2,136 width=0) (actual time=32.139..32.139 rows=0 loops=1)

  • Buffers: shared read=940
27. 4.774 4.774 ↓ 1.0 40,896 1

Bitmap Index Scan on idx_concept_class_id (cost=0.00..970.76 rows=39,777 width=0) (actual time=4.774..4.774 rows=40,896 loops=1)

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Buffers: shared read=160
28. 27.153 27.153 ↓ 1.0 284,287 1

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..6,121.63 rows=282,293 width=0) (actual time=27.153..27.153 rows=284,287 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'RxNorm'::text)
  • Buffers: shared read=780
29. 0.002 0.098 ↓ 0.0 0 1

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

  • Buffers: shared read=3
30. 0.096 0.096 ↓ 0.0 0 1

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

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

Bitmap Heap Scan on concept_ancestor_f concept_ancestor_f_1 (cost=33.76..3,359.18 rows=1,771 width=8) (actual time=0.032..0.117 rows=195 loops=13,308)

  • Recheck Cond: (ancestor_concept_id = concept_f_1.concept_id)
  • Heap Blocks: exact=26040
  • Buffers: shared hit=59279 read=27091
32. 306.084 306.084 ↑ 9.1 195 13,308

Bitmap Index Scan on idx_concept_ancestor_id_1 (cost=0.00..33.32 rows=1,771 width=0) (actual time=0.023..0.023 rows=195 loops=13,308)

  • Index Cond: (ancestor_concept_id = concept_f_1.concept_id)
  • Buffers: shared hit=50407 read=9923
33. 135,467.204 506,083.045 ↑ 257,959,854.4 382,981,226 1

Materialize (cost=51,655,552,196,191,008.00..52,149,521,102,682,576.00 rows=98,793,781,298,315,168 width=19) (actual time=364,898.225..506,083.045 rows=382,981,226 loops=1)

  • Buffers: shared hit=10441 read=823548, temp read=1186670 written=1196585
34. 20,173.636 370,615.841 ↑ 7,741,096,164.2 12,762,247 1

Sort (cost=51,655,552,196,191,008.00..51,902,536,649,436,792.00 rows=98,793,781,298,315,168 width=19) (actual time=364,898.219..370,615.841 rows=12,762,247 loops=1)

  • Sort Key: concept_ancestor_f.ancestor_concept_id
  • Sort Method: external merge Disk: 429344kB
  • Buffers: shared hit=10441 read=823548, temp read=1186670 written=1196585
35. 32,772.620 350,442.205 ↑ 7,129,262,847.6 13,857,503 1

Merge Join (cost=9,745,002,076.26..1,481,916,558,464,536.75 rows=98,793,781,298,315,168 width=19) (actual time=173,066.058..350,442.205 rows=13,857,503 loops=1)

  • Merge Cond: (concept_f_2.concept_id = concept_relationship_f.concept_id_2)
  • Buffers: shared hit=10441 read=823548, temp read=1019084 written=1024626
36. 52,418.121 275,149.852 ↑ 10.1 58,897,078 1

Merge Join (cost=16,061,818.30..25,364,468.91 rows=593,829,843 width=12) (actual time=134,709.520..275,149.852 rows=58,897,078 loops=1)

  • Merge Cond: (concept_f_2.concept_id = concept_ancestor_f.descendant_concept_id)
  • Buffers: shared hit=10441 read=479849, temp read=941566 written=946920
37. 5,284.972 23,372.024 ↑ 1.0 5,185,423 1

Unique (cost=2,669,833.44..2,818,377.57 rows=5,401,605 width=240) (actual time=15,097.126..23,372.024 rows=5,185,423 loops=1)

  • Buffers: shared hit=2693 read=100269, temp read=281455 written=285462
38. 11,604.903 18,087.052 ↑ 1.0 5,185,423 1

Sort (cost=2,669,833.44..2,683,337.45 rows=5,401,605 width=240) (actual time=15,097.122..18,087.052 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=2693 read=100269, temp read=281455 written=285462
39. 3,543.903 6,482.149 ↓ 1.0 5,409,856 1

Append (cost=0.00..238,002.13 rows=5,401,605 width=240) (actual time=0.049..6,482.149 rows=5,409,856 loops=1)

  • Buffers: shared hit=2693 read=100269
40. 2,747.699 2,747.699 ↓ 1.0 5,265,096 1

Seq Scan on concept_f concept_f_2 (cost=0.00..152,838.45 rows=5,256,845 width=115) (actual time=0.047..2,747.699 rows=5,265,096 loops=1)

  • Buffers: shared hit=1 read=100269
41. 131.622 190.547 ↑ 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.037..190.547 rows=144,760 loops=1)

  • Buffers: shared hit=2692
42. 58.925 58.925 ↑ 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.019..58.925 rows=144,760 loops=1)

  • Buffers: shared hit=2692
43. 48,417.407 199,359.707 ↑ 1.0 71,612,462 1

Materialize (cost=13,391,984.87..13,750,262.39 rows=71,655,504 width=8) (actual time=119,612.228..199,359.707 rows=71,612,462 loops=1)

  • Buffers: shared hit=7748 read=379580, temp read=660111 written=661458
44. 113,965.564 150,942.300 ↑ 1.0 71,612,462 1

Sort (cost=13,391,984.87..13,571,123.63 rows=71,655,504 width=8) (actual time=119,612.222..150,942.300 rows=71,612,462 loops=1)

  • Sort Key: concept_ancestor_f.descendant_concept_id
  • Sort Method: external merge Disk: 1260872kB
  • Buffers: shared hit=7748 read=379580, temp read=660111 written=661458
45. 36,976.736 36,976.736 ↓ 1.0 71,655,508 1

Seq Scan on concept_ancestor_f (cost=0.00..1,103,883.04 rows=71,655,504 width=8) (actual time=0.033..36,976.736 rows=71,655,508 loops=1)

  • Buffers: shared hit=7748 read=379580
46. 5,383.599 42,519.733 ↑ 2,401.1 13,857,482 1

Materialize (cost=9,728,940,257.96..9,895,307,412.56 rows=33,273,430,921 width=19) (actual time=37,082.232..42,519.733 rows=13,857,482 loops=1)

  • Buffers: shared read=343699, temp read=77518 written=77706
47. 131.305 37,136.134 ↑ 336,142.8 98,986 1

Sort (cost=9,728,940,257.96..9,812,123,835.26 rows=33,273,430,921 width=19) (actual time=37,082.218..37,136.134 rows=98,986 loops=1)

  • Sort Key: concept_relationship_f.concept_id_2
  • Sort Method: external merge Disk: 3008kB
  • Buffers: shared read=343699, temp read=77518 written=77706
48. 2,329.842 37,004.829 ↑ 336,142.8 98,986 1

Merge Join (cost=2,710,556.98..501,974,275.99 rows=33,273,430,921 width=19) (actual time=23,344.392..37,004.829 rows=98,986 loops=1)

  • Merge Cond: (b.concept_id = concept_relationship_f.concept_id_1)
  • Buffers: shared read=343699, temp read=77142 written=77328
49. 146.878 4,104.289 ↑ 11.8 99,259 1

Sort (cost=568,742.19..571,667.36 rows=1,170,065 width=19) (actual time=4,059.642..4,104.289 rows=99,259 loops=1)

  • Sort Key: b.concept_id
  • Sort Method: external merge Disk: 3024kB
  • Buffers: shared read=17054, temp read=16644 written=16676
50. 566.783 3,957.411 ↑ 11.8 99,259 1

Hash Join (cost=362,672.94..426,814.42 rows=1,170,065 width=19) (actual time=1,928.400..3,957.411 rows=99,259 loops=1)

  • Hash Cond: ((b.concept_code)::text = (a_2.ndc)::text)
  • Buffers: shared read=17054, temp read=16266 written=16296
51. 491.475 3,189.544 ↓ 1.0 577,523 1

Subquery Scan on b (cost=352,665.18..374,211.70 rows=574,574 width=36) (actual time=1,725.309..3,189.544 rows=577,523 loops=1)

  • Buffers: shared read=11715, temp read=13666 written=13696
52. 610.631 2,698.069 ↓ 1.0 577,523 1

Unique (cost=352,665.18..368,465.96 rows=574,574 width=240) (actual time=1,725.305..2,698.069 rows=577,523 loops=1)

  • Buffers: shared read=11715, temp read=13666 written=13696
53. 1,172.510 2,087.438 ↓ 1.0 577,523 1

Sort (cost=352,665.18..354,101.61 rows=574,574 width=240) (actual time=1,725.302..2,087.438 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 read=11715, temp read=13666 written=13696
54. 441.593 914.928 ↓ 1.0 577,523 1

Append (cost=12,601.37..168,082.05 rows=574,574 width=240) (actual time=76.360..914.928 rows=577,523 loops=1)

  • Buffers: shared read=11715
55. 399.016 473.239 ↓ 1.0 577,523 1

Bitmap Heap Scan on concept_f concept_f_3 (cost=12,601.37..159,455.00 rows=574,573 width=115) (actual time=76.358..473.239 rows=577,523 loops=1)

  • Recheck Cond: ((vocabulary_id)::text = 'NDC'::text)
  • Heap Blocks: exact=10131
  • Buffers: shared read=11712
56. 74.223 74.223 ↓ 1.0 577,523 1

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..12,457.73 rows=574,573 width=0) (actual time=74.222..74.223 rows=577,523 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'NDC'::text)
  • Buffers: shared read=1581
57. 0.003 0.096 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_3 (cost=0.42..8.45 rows=1 width=117) (actual time=0.095..0.096 rows=0 loops=1)

  • Buffers: shared read=3
58. 0.093 0.093 ↓ 0.0 0 1

Index Scan using ca_idx_concept_vocabluary_id on concept_amend concept_amend_3 (cost=0.42..8.44 rows=1 width=115) (actual time=0.093..0.093 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'NDC'::text)
  • Buffers: shared read=3
59. 87.927 201.084 ↑ 1.0 159,145 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2837kB
  • Buffers: shared read=5339, temp written=692
60. 113.157 113.157 ↑ 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.082..113.157 rows=159,145 loops=1)

  • Buffers: shared read=5339
61. 3,822.001 30,570.698 ↑ 1.0 5,657,694 1

Materialize (cost=2,141,814.78..2,312,438.28 rows=5,687,450 width=8) (actual time=19,200.479..30,570.698 rows=5,657,694 loops=1)

  • Buffers: shared read=326645, temp read=60498 written=60652
62. 5,053.996 26,748.697 ↑ 1.0 5,617,887 1

Unique (cost=2,141,814.78..2,241,345.16 rows=5,687,450 width=82) (actual time=19,200.460..26,748.697 rows=5,617,887 loops=1)

  • Buffers: shared read=326645, temp read=60498 written=60652
63. 7,951.135 21,694.701 ↑ 1.0 5,617,887 1

Sort (cost=2,141,814.78..2,156,033.41 rows=5,687,450 width=82) (actual time=19,200.458..21,694.701 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
64. 3,970.178 13,743.566 ↑ 1.0 5,620,543 1

Append (cost=0.00..959,388.36 rows=5,687,450 width=82) (actual time=0.075..13,743.566 rows=5,620,543 loops=1)

  • Buffers: shared read=326645
65. 9,773.341 9,773.341 ↑ 1.0 5,620,542 1

Seq Scan on concept_relationship_f (cost=0.00..874,075.60 rows=5,687,449 width=37) (actual time=0.072..9,773.341 rows=5,620,542 loops=1)

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

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

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Buffers: shared read=1
67. 1,005.083 566,788.226 ↓ 47.8 459,303 1

HashAggregate (cost=9,905,555,955,551,312.00..9,905,555,955,551,408.00 rows=9,600 width=16) (actual time=566,525.069..566,788.226 rows=459,303 loops=1)

  • Group Key: a_3.mktdef_nm, concept_f_4.concept_id
  • Buffers: shared hit=37352574 read=33734295, temp read=461972 written=471012
68. 1,781.312 565,783.143 ↑ 2,012,312,530,496.1 770,774 1

Merge Join (cost=171,297,106,822,077.91..2,150,365,063,648,429.00 rows=1,551,038,178,380,576,768 width=16) (actual time=562,431.008..565,783.143 rows=770,774 loops=1)

  • Merge Cond: (concept_f_5.concept_id = concept_ancestor_f_2.ancestor_concept_id)
  • Buffers: shared hit=37352574 read=33734295, temp read=461972 written=471012
69. 3,299.222 30,647.956 ↑ 5.5 2,336,185 1

Sort (cost=10,919,047.10..10,951,110.15 rows=12,825,221 width=12) (actual time=29,404.886..30,647.956 rows=2,336,185 loops=1)

  • Sort Key: concept_f_5.concept_id
  • Sort Method: external merge Disk: 50280kB
  • Buffers: shared hit=74273 read=116672, temp read=124660 written=124898
70. 3,403.228 27,348.734 ↑ 5.5 2,336,185 1

Merge Join (cost=8,676,312.63..8,966,502.70 rows=12,825,221 width=12) (actual time=16,593.857..27,348.734 rows=2,336,185 loops=1)

  • Merge Cond: (concept_f_4.concept_id = concept_ancestor_f_3.descendant_concept_id)
  • Buffers: shared hit=74273 read=116672, temp read=113732 written=113928
71. 2,566.956 13,526.746 ↑ 1.0 2,206,885 1

Unique (cost=934,625.38..995,366.06 rows=2,208,752 width=240) (actual time=9,555.365..13,526.746 rows=2,206,885 loops=1)

  • Buffers: shared hit=12824 read=90138, temp read=100924 written=101074
72. 6,027.941 10,959.790 ↑ 1.0 2,206,885 1

Sort (cost=934,625.38..940,147.26 rows=2,208,752 width=240) (actual time=9,555.359..10,959.790 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=12824 read=90138, temp read=100924 written=101074
73. 1,921.165 4,931.849 ↑ 1.0 2,206,903 1

Append (cost=0.00..203,613.34 rows=2,208,752 width=240) (actual time=0.084..4,931.849 rows=2,206,903 loops=1)

  • Buffers: shared hit=12824 read=90138
74. 2,971.579 2,971.579 ↑ 1.0 2,206,903 1

Seq Scan on concept_f concept_f_4 (cost=0.00..165,980.56 rows=2,208,751 width=115) (actual time=0.079..2,971.579 rows=2,206,903 loops=1)

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 3058193
  • Buffers: shared hit=10132 read=90138
75. 0.003 39.105 ↓ 0.0 0 1

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

  • Buffers: shared hit=2692
76. 39.102 39.102 ↓ 0.0 0 1

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

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 144760
  • Buffers: shared hit=2692
77. 2,013.648 10,418.760 ↑ 1.5 2,590,553 1

Materialize (cost=7,741,687.25..7,760,610.61 rows=3,784,672 width=12) (actual time=7,004.632..10,418.760 rows=2,590,553 loops=1)

  • Buffers: shared hit=61449 read=26534, temp read=12808 written=12854
78. 4,197.255 8,405.112 ↑ 1.5 2,590,553 1

Sort (cost=7,741,687.25..7,751,148.93 rows=3,784,672 width=12) (actual time=7,004.614..8,405.112 rows=2,590,553 loops=1)

  • Sort Key: concept_ancestor_f_3.descendant_concept_id
  • Sort Method: external merge Disk: 55768kB
  • Buffers: shared hit=61449 read=26534, temp read=12808 written=12854
79. 2,282.615 4,207.857 ↑ 1.5 2,590,553 1

Nested Loop (cost=14,784.57..7,198,818.97 rows=3,784,672 width=12) (actual time=97.903..4,207.857 rows=2,590,553 loops=1)

  • Buffers: shared hit=61449 read=26534
80. 40.181 115.354 ↓ 6.2 13,308 1

HashAggregate (cost=14,750.81..14,772.18 rows=2,137 width=240) (actual time=97.828..115.354 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=1 read=1612
81. 11.244 75.173 ↓ 6.2 13,308 1

Append (cost=7,093.71..14,697.38 rows=2,137 width=240) (actual time=48.150..75.173 rows=13,308 loops=1)

  • Buffers: shared hit=1 read=1612
82. 15.836 63.846 ↓ 6.2 13,308 1

Bitmap Heap Scan on concept_f concept_f_5 (cost=7,093.71..14,660.89 rows=2,136 width=115) (actual time=48.148..63.846 rows=13,308 loops=1)

  • Recheck Cond: (((concept_class_id)::text = 'Ingredient'::text) AND ((vocabulary_id)::text = 'RxNorm'::text))
  • Heap Blocks: exact=670
  • Buffers: shared hit=1 read=1609
83. 0.265 48.010 ↓ 0.0 0 1

BitmapAnd (cost=7,093.71..7,093.71 rows=2,136 width=0) (actual time=48.009..48.010 rows=0 loops=1)

  • Buffers: shared read=940
84. 6.768 6.768 ↓ 1.0 40,896 1

Bitmap Index Scan on idx_concept_class_id (cost=0.00..970.76 rows=39,777 width=0) (actual time=6.768..6.768 rows=40,896 loops=1)

  • Index Cond: ((concept_class_id)::text = 'Ingredient'::text)
  • Buffers: shared read=160
85. 40.977 40.977 ↓ 1.0 284,287 1

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..6,121.63 rows=282,293 width=0) (actual time=40.977..40.977 rows=284,287 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'RxNorm'::text)
  • Buffers: shared read=780
86. 0.002 0.083 ↓ 0.0 0 1

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

  • Buffers: shared read=3
87. 0.081 0.081 ↓ 0.0 0 1

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

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

Bitmap Heap Scan on concept_ancestor_f concept_ancestor_f_3 (cost=33.76..3,344.02 rows=1,771 width=8) (actual time=0.035..0.136 rows=195 loops=13,308)

  • Recheck Cond: (ancestor_concept_id = concept_f_5.concept_id)
  • Heap Blocks: exact=26040
  • Buffers: shared hit=61448 read=24922
89. 346.008 346.008 ↑ 9.1 195 13,308

Bitmap Index Scan on idx_concept_ancestor_id_1 (cost=0.00..33.32 rows=1,771 width=0) (actual time=0.026..0.026 rows=195 loops=13,308)

  • Index Cond: (ancestor_concept_id = concept_f_5.concept_id)
  • Buffers: shared hit=51513 read=8817
90. 332.146 533,353.875 ↑ 527,499,108.3 788,643 1

Materialize (cost=171,297,095,903,030.81..173,377,138,299,517.13 rows=416,008,479,297,257 width=16) (actual time=533,012.565..533,353.875 rows=788,643 loops=1)

  • Buffers: shared hit=37278301 read=33617623, temp read=337312 written=346114
91. 23.948 533,021.729 ↑ 22,438,429,304.1 18,540 1

Sort (cost=171,297,095,903,030.81..172,337,117,101,273.97 rows=416,008,479,297,257 width=16) (actual time=533,012.547..533,021.729 rows=18,540 loops=1)

  • Sort Key: concept_ancestor_f_2.ancestor_concept_id
  • Sort Method: quicksort Memory: 1971kB
  • Buffers: shared hit=37278301 read=33617623, temp read=337312 written=346114
92. 30,374.978 532,997.781 ↑ 21,563,781,842.1 19,292 1

Merge Join (cost=33,219,827.46..6,297,056,761,266.19 rows=416,008,479,297,257 width=16) (actual time=56,045.631..532,997.781 rows=19,292 loops=1)

  • Merge Cond: (concept_ancestor_f_2.descendant_concept_id = concept_relationship_f_1.concept_id_2)
  • Buffers: shared hit=37278301 read=33617623, temp read=337312 written=346114
93. 441,498.927 441,498.927 ↑ 1.0 71,608,649 1

Index Scan using idx_concept_ancestor_id_2 on concept_ancestor_f concept_ancestor_f_2 (cost=0.57..134,056,665.57 rows=71,655,504 width=8) (actual time=0.159..441,498.927 rows=71,608,649 loops=1)

  • Buffers: shared hit=37278288 read=33187883
94. 7.925 61,123.876 ↑ 196,352,393.2 19,272 1

Materialize (cost=33,219,826.89..66,255,594,309.03 rows=3,784,103,322,406 width=20) (actual time=51,447.306..61,123.876 rows=19,272 loops=1)

  • Buffers: shared hit=13 read=429740, temp read=337312 written=346114
95. 1,896.853 61,115.951 ↑ 5,524,238,426.9 685 1

Merge Join (cost=33,219,826.89..56,795,336,003.01 rows=3,784,103,322,406 width=20) (actual time=51,447.295..61,115.951 rows=685 loops=1)

  • Merge Cond: (concept_f_6.concept_id = concept_relationship_f_1.concept_id_2)
  • Buffers: shared hit=13 read=429740, temp read=337312 written=346114
96. 5,058.031 26,881.999 ↑ 1.0 5,185,208 1

Unique (cost=2,669,833.44..2,818,377.57 rows=5,401,605 width=240) (actual time=19,054.539..26,881.999 rows=5,185,208 loops=1)

  • Buffers: shared hit=2 read=102960, temp read=281455 written=285462
97. 13,606.212 21,823.968 ↑ 1.0 5,185,208 1

Sort (cost=2,669,833.44..2,683,337.45 rows=5,401,605 width=240) (actual time=19,054.536..21,823.968 rows=5,185,208 loops=1)

  • Sort 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
  • Sort Method: external merge Disk: 708656kB
  • Buffers: shared hit=2 read=102960, temp read=281455 written=285462
98. 4,351.657 8,217.756 ↓ 1.0 5,409,856 1

Append (cost=0.00..238,002.13 rows=5,401,605 width=240) (actual time=0.056..8,217.756 rows=5,409,856 loops=1)

  • Buffers: shared hit=2 read=102960
99. 3,614.795 3,614.795 ↓ 1.0 5,265,096 1

Seq Scan on concept_f concept_f_6 (cost=0.00..152,838.45 rows=5,256,845 width=115) (actual time=0.054..3,614.795 rows=5,265,096 loops=1)

  • Buffers: shared hit=2 read=100268
100. 147.840 251.304 ↑ 1.0 144,760 1

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

  • Buffers: shared read=2692
101. 103.464 103.464 ↑ 1.0 144,760 1

Seq Scan on concept_amend concept_amend_6 (cost=0.00..4,139.60 rows=144,760 width=115) (actual time=0.070..103.464 rows=144,760 loops=1)

  • Buffers: shared read=2692
102. 0.819 32,337.099 ↑ 196,784.2 712 1

Materialize (cost=30,549,993.46..31,250,545.11 rows=140,110,331 width=16) (actual time=32,335.663..32,337.099 rows=712 loops=1)

  • Buffers: shared hit=11 read=326780, temp read=55857 written=60652
103. 1.090 32,336.280 ↑ 196,784.2 712 1

Sort (cost=30,549,993.46..30,900,269.28 rows=140,110,331 width=16) (actual time=32,335.651..32,336.280 rows=712 loops=1)

  • Sort Key: concept_relationship_f_1.concept_id_2
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=11 read=326780, temp read=55857 written=60652
104. 1,796.058 32,335.190 ↑ 196,784.2 712 1

Merge Join (cost=2,150,191.93..4,408,276.41 rows=140,110,331 width=16) (actual time=21,892.709..32,335.190 rows=712 loops=1)

  • Merge Cond: (concept_f_7.concept_id = concept_relationship_f_1.concept_id_1)
  • Buffers: shared hit=11 read=326780, temp read=55857 written=60652
105. 0.720 25.230 ↑ 6.9 712 1

Sort (cost=8,377.15..8,389.47 rows=4,927 width=16) (actual time=24.948..25.230 rows=712 loops=1)

  • Sort Key: concept_f_7.concept_id
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=11 read=135
106. 1.465 24.510 ↑ 6.9 712 1

Hash Join (cost=7,924.90..8,074.97 rows=4,927 width=16) (actual time=21.425..24.510 rows=712 loops=1)

  • Hash Cond: ((concept_f_7.concept_code)::text = (a_3.prc_cd)::text)
  • Buffers: shared hit=11 read=135
107. 13.349 17.083 ↑ 1.3 2,681 1

HashAggregate (cost=7,865.56..7,900.62 rows=3,506 width=240) (actual time=15.432..17.083 rows=2,681 loops=1)

  • Group 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
  • Buffers: shared hit=11 read=105
108. 1.664 3.734 ↑ 1.3 2,681 1

Append (cost=0.43..7,777.91 rows=3,506 width=240) (actual time=0.101..3.734 rows=2,681 loops=1)

  • Buffers: shared read=69
109. 2.024 2.024 ↑ 1.3 2,681 1

Index Scan using idx_concept_vocabluary_id on concept_f concept_f_7 (cost=0.43..7,716.88 rows=3,505 width=115) (actual time=0.099..2.024 rows=2,681 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'HCPCS'::text)
  • Buffers: shared read=66
110. 0.002 0.046 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_7 (cost=0.42..8.45 rows=1 width=117) (actual time=0.045..0.046 rows=0 loops=1)

  • Buffers: shared read=3
111. 0.044 0.044 ↓ 0.0 0 1

Index Scan using ca_idx_concept_vocabluary_id on concept_amend concept_amend_7 (cost=0.42..8.44 rows=1 width=115) (actual time=0.044..0.044 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'HCPCS'::text)
  • Buffers: shared read=3
112. 0.623 5.962 ↑ 1.0 1,304 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared read=30
113. 5.339 5.339 ↑ 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.593..5.339 rows=1,304 loops=1)

  • Buffers: shared read=30
114. 3,004.178 30,513.902 ↑ 1.3 4,433,314 1

Materialize (cost=2,141,814.78..2,312,438.28 rows=5,687,450 width=8) (actual time=21,491.902..30,513.902 rows=4,433,314 loops=1)

  • Buffers: shared read=326645, temp read=55857 written=60652
115. 4,025.217 27,509.724 ↑ 1.3 4,433,078 1

Unique (cost=2,141,814.78..2,241,345.16 rows=5,687,450 width=82) (actual time=21,491.897..27,509.724 rows=4,433,078 loops=1)

  • Buffers: shared read=326645, temp read=55857 written=60652
116. 8,198.941 23,484.507 ↑ 1.3 4,433,078 1

Sort (cost=2,141,814.78..2,156,033.41 rows=5,687,450 width=82) (actual time=21,491.893..23,484.507 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 read=326645, temp read=55857 written=60652
117. 4,258.773 15,285.566 ↑ 1.0 5,620,543 1

Append (cost=0.00..959,388.36 rows=5,687,450 width=82) (actual time=0.033..15,285.566 rows=5,620,543 loops=1)

  • Buffers: shared read=326645
118. 11,026.765 11,026.765 ↑ 1.0 5,620,542 1

Seq Scan on concept_relationship_f concept_relationship_f_1 (cost=0.00..874,075.60 rows=5,687,449 width=37) (actual time=0.031..11,026.765 rows=5,620,542 loops=1)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Rows Removed by Filter: 38173986
  • Buffers: shared read=326644
119. 0.028 0.028 ↑ 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.027..0.028 rows=1 loops=1)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
  • Buffers: shared read=1
120. 0.007 1,589,308.444 ↓ 0.0 0 1

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

  • Sort Key: a.mktdef_nm, drug_exposure_f.person_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=37425342 read=34686465, temp read=1784577 written=1810287
121. 537.871 1,589,308.437 ↓ 0.0 0 1

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

  • Hash Cond: (drug_exposure_f.drug_concept_id = a.concept_id)
  • Buffers: shared hit=37425342 read=34686465, temp read=1784577 written=1810287
122. 0.050 0.050 ↑ 1.0 42 1

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

  • Buffers: shared hit=1
123. 1,183.116 1,588,770.516 ↓ 89.3 1,749,598 1

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

  • Buckets: 131072 (originally 32768) Batches: 32 (originally 1) Memory Usage: 3784kB
  • Buffers: shared hit=37425341 read=34686465, temp read=1773302 written=1807386
124. 1,587,587.400 1,587,587.400 ↓ 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,584,405.587..1,587,587.400 rows=1,749,598 loops=1)

  • Buffers: shared hit=37425341 read=34686465, temp read=1773302 written=1799012
125. 0.002 18.659 ↓ 0.0 0 1

Unique (cost=12,726,959,925,917.41..12,726,959,926,675.66 rows=3,370 width=126) (actual time=18.659..18.659 rows=0 loops=1)

  • Buffers: shared hit=6 read=229
126. 0.002 18.657 ↓ 0.0 0 1

Unique (cost=12,726,959,925,917.41..12,726,959,926,170.16 rows=33,700 width=126) (actual time=18.657..18.657 rows=0 loops=1)

  • Buffers: shared hit=6 read=229
127.          

CTE cte_descendant_concept_diagnosis

128. 0.105 18.476 ↓ 0.0 0 1

HashAggregate (cost=12,726,959,919,678.14..12,726,959,919,778.14 rows=10,000 width=16) (actual time=18.475..18.476 rows=0 loops=1)

  • Group Key: a_4.mktdef_nm, f.concept_id
  • Buffers: shared hit=6 read=226
129. 0.002 18.371 ↓ 0.0 0 1

Merge Join (cost=25,663,779.94..9,545,245,866,818.37 rows=636,342,810,571,953 width=16) (actual time=18.371..18.371 rows=0 loops=1)

  • Merge Cond: (concept_relationship_f_2.concept_id_2 = concept_f_9.concept_id)
  • Buffers: shared hit=6 read=226
130. 0.014 18.369 ↓ 0.0 0 1

Sort (cost=6,954,446.99..7,015,942.55 rows=24,598,221 width=16) (actual time=18.369..18.369 rows=0 loops=1)

  • Sort Key: concept_relationship_f_2.concept_id_2
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=226
131. 0.007 18.355 ↓ 0.0 0 1

Merge Join (cost=2,148,236.14..2,673,618.66 rows=24,598,221 width=16) (actual time=18.355..18.355 rows=0 loops=1)

  • Merge Cond: (concept_f_8.concept_id = concept_relationship_f_2.concept_id_1)
  • Buffers: shared hit=6 read=226
132. 0.008 18.348 ↓ 0.0 0 1

Sort (cost=6,421.36..6,423.52 rows=865 width=16) (actual time=18.348..18.348 rows=0 loops=1)

  • Sort Key: concept_f_8.concept_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=226
133. 0.047 18.340 ↓ 0.0 0 1

Hash Join (cost=6,309.38..6,379.16 rows=865 width=16) (actual time=18.340..18.340 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=6 read=226
134. 0.021 0.232 ↓ 0.0 0 1

HashAggregate (cost=5,859.52..5,875.43 rows=1,591 width=240) (actual time=0.232..0.232 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=6 read=6
135. 0.007 0.211 ↓ 0.0 0 1

Append (cost=37.59..5,819.74 rows=1,591 width=240) (actual time=0.211..0.211 rows=0 loops=1)

  • Buffers: shared hit=6 read=6
136. 0.008 0.124 ↓ 0.0 0 1

Bitmap Heap Scan on concept_f concept_f_8 (cost=37.59..5,783.01 rows=1,590 width=115) (actual time=0.123..0.124 rows=0 loops=1)

  • Recheck Cond: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
  • Buffers: shared hit=3 read=3
137. 0.003 0.116 ↓ 0.0 0 1

BitmapOr (cost=37.59..37.59 rows=1,591 width=0) (actual time=0.115..0.116 rows=0 loops=1)

  • Buffers: shared hit=3 read=3
138. 0.094 0.094 ↓ 0.0 0 1

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..18.39 rows=795 width=0) (actual time=0.094..0.094 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'ICD9CM'::text)
  • Buffers: shared read=3
139. 0.019 0.019 ↓ 0.0 0 1

Bitmap Index Scan on idx_concept_vocabluary_id (cost=0.00..18.39 rows=795 width=0) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'ICD10CM'::text)
  • Buffers: shared hit=3
140. 0.002 0.080 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_8 (cost=8.86..12.88 rows=1 width=117) (actual time=0.080..0.080 rows=0 loops=1)

  • Buffers: shared hit=3 read=3
141. 0.002 0.078 ↓ 0.0 0 1

Bitmap Heap Scan on concept_amend concept_amend_8 (cost=8.86..12.87 rows=1 width=115) (actual time=0.078..0.078 rows=0 loops=1)

  • Recheck Cond: (((vocabulary_id)::text = 'ICD9CM'::text) OR ((vocabulary_id)::text = 'ICD10CM'::text))
  • Buffers: shared hit=3 read=3
142. 0.002 0.076 ↓ 0.0 0 1

BitmapOr (cost=8.86..8.86 rows=1 width=0) (actual time=0.076..0.076 rows=0 loops=1)

  • Buffers: shared hit=3 read=3
143. 0.061 0.061 ↓ 0.0 0 1

Bitmap Index Scan on ca_idx_concept_vocabluary_id (cost=0.00..4.43 rows=1 width=0) (actual time=0.061..0.061 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'ICD9CM'::text)
  • Buffers: shared read=3
144. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on ca_idx_concept_vocabluary_id (cost=0.00..4.43 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((vocabulary_id)::text = 'ICD10CM'::text)
  • Buffers: shared hit=3
145. 7.162 18.061 ↓ 1.2 9,143 1

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 607kB
  • Buffers: shared read=220
146. 10.899 10.899 ↓ 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.633..10.899 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
147. 0.000 0.000 ↓ 0.0 0

Materialize (cost=2,141,814.78..2,312,438.28 rows=5,687,450 width=8) (never executed)

148. 0.000 0.000 ↓ 0.0 0

Unique (cost=2,141,814.78..2,241,345.16 rows=5,687,450 width=82) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,141,814.78..2,156,033.41 rows=5,687,450 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
150. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..959,388.36 rows=5,687,450 width=82) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Seq Scan on concept_relationship_f concept_relationship_f_2 (cost=0.00..874,075.60 rows=5,687,449 width=37) (never executed)

  • Filter: ((relationship_id)::text = 'Maps to'::text)
152. 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)
153. 0.000 0.000 ↓ 0.0 0

Materialize (cost=18,709,332.94..109,565,408.96 rows=5,173,892,946 width=12) (never executed)

154. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=18,709,332.94..96,630,676.60 rows=5,173,892,946 width=12) (never executed)

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

Unique (cost=2,669,833.44..2,818,377.57 rows=5,401,605 width=240) (never executed)

156. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,669,833.44..2,683,337.45 rows=5,401,605 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
157. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..238,002.13 rows=5,401,605 width=240) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Seq Scan on concept_f concept_f_9 (cost=0.00..152,838.45 rows=5,256,845 width=115) (never executed)

159. 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)

160. 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)

161. 0.000 0.000 ↓ 0.0 0

Materialize (cost=16,039,499.51..16,233,271.27 rows=38,754,353 width=8) (never executed)

162. 0.000 0.000 ↓ 0.0 0

Sort (cost=16,039,499.51..16,136,385.39 rows=38,754,353 width=8) (never executed)

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

Hash Join (cost=2,566,161.87..9,565,386.44 rows=38,754,353 width=8) (never executed)

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

Subquery Scan on f (cost=286,680.03..299,899.46 rows=352,518 width=4) (never executed)

165. 0.000 0.000 ↓ 0.0 0

Unique (cost=286,680.03..296,374.28 rows=352,518 width=240) (never executed)

166. 0.000 0.000 ↓ 0.0 0

Sort (cost=286,680.03..287,561.33 rows=352,518 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
167. 0.000 0.000 ↓ 0.0 0

Append (cost=1,000.00..174,673.19 rows=352,518 width=240) (never executed)

168. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..169,376.98 rows=352,517 width=115) (never executed)

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

Parallel Seq Scan on concept_f concept_f_10 (cost=0.00..133,125.28 rows=146,882 width=115) (never executed)

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

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

171. 0.000 0.000 ↓ 0.0 0

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

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

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

173. 0.000 0.000 ↓ 0.0 0

Seq Scan on concept_ancestor_f concept_ancestor_f_4 (cost=0.00..1,103,883.04 rows=71,655,504 width=8) (never executed)

174. 0.033 18.655 ↓ 0.0 0 1

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

  • Sort Key: a_1.mktdef_nm, condition_occurrence_f.person_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=229
175. 0.007 18.622 ↓ 0.0 0 1

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

  • Merge Cond: (condition_occurrence_f.condition_concept_id = a_1.concept_id)
  • Buffers: shared hit=6 read=229
176. 0.118 0.118 ↑ 674.0 1 1

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

  • Buffers: shared read=3
177. 0.015 18.497 ↓ 0.0 0 1

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

  • Sort Key: a_1.concept_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=226
178. 18.482 18.482 ↓ 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=18.481..18.482 rows=0 loops=1)

  • Buffers: shared hit=6 read=226
Planning time : 12.581 ms
Execution time : 1,590,175.765 ms