explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gJM7

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

Sort (cost=1,150,824.78..1,150,824.90 rows=51 width=482) (actual rows= loops=)

  • Sort Key: final_1.medication_name_final DESC
2.          

CTE products_1

3. 0.000 0.000 ↓ 0.0

Sort (cost=569,386.34..569,514.30 rows=51,185 width=314) (actual rows= loops=)

  • Sort Key: p.gcn_seqno DESC, p.name DESC
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=442,479.13..565,382.79 rows=51,185 width=314) (actual rows= loops=)

  • Hash Cond: (p.id = inventory_items.product_id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=441,772.10..448,351.46 rows=51,185 width=222) (actual rows= loops=)

  • Hash Cond: (p.id = ad_3.product_id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=349,511.21..355,956.20 rows=51,185 width=190) (actual rows= loops=)

  • Hash Cond: (p.id = ad_2.product_id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=257,307.20..263,617.83 rows=51,185 width=158) (actual rows= loops=)

  • Hash Cond: (p.id = ad_1.product_id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=165,114.74..171,291.01 rows=51,185 width=126) (actual rows= loops=)

  • Hash Cond: (p.id = ap_tot.product_id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=99,337.37..105,379.28 rows=51,185 width=94) (actual rows= loops=)

  • Hash Cond: (p.id = ad_tot.product_id)
10. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=22,240.83..28,148.38 rows=51,185 width=62) (actual rows= loops=)

  • Hash Cond: (pp.product_id = p.id)
  • Join Filter: (NOT p.is_breakable)
11. 0.000 0.000 ↓ 0.0

Seq Scan on product_packages pp (cost=0.00..5,306.12 rows=229,112 width=9) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=21,601.02..21,601.02 rows=51,185 width=57) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..21,601.02 rows=51,185 width=57) (actual rows= loops=)

  • Workers Planned: 2
14. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on products p (cost=0.00..15,482.52 rows=21,327 width=57) (actual rows= loops=)

  • Filter: (active AND (is_breakable IS NOT NULL) AND (dea_schedule <> 2) AND ((gcn_seqno)::text <> '19549'::text) AND ((gcn_seqno)::text <> '70632'::text) AND ((gcn_seqno)::text <> '64589'::text) AND ((gcn_seqno)::text <> '4481'::text) AND ((gcn_seqno)::text <> '64725'::text) AND ((gcn_seqno)::text <> '016995'::text) AND ((gcn_seqno)::text <> '64161'::text) AND ((gcn_seqno)::text <> '74595'::text) AND ((gcn_seqno)::text <> ALL ('{47315,47316,47317}'::text[])) AND ((gcn_seqno)::text <> ALL ('{3173,3176,31769,34383,37909,44967,45017,47336,47451,47557,47558,53123,53206,53207,53208,53671,53674,53675,58907,59035,59358,62815,6575}'::text[])))
15. 0.000 0.000 ↓ 0.0

Hash (cost=77,037.23..77,037.23 rows=4,744 width=36) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Subquery Scan on ad_tot (cost=76,835.61..77,037.23 rows=4,744 width=36) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=76,835.61..76,989.79 rows=4,744 width=36) (actual rows= loops=)

  • Group Key: deliveries.product_id
18. 0.000 0.000 ↓ 0.0

Sort (cost=76,835.61..76,859.33 rows=9,488 width=36) (actual rows= loops=)

  • Sort Key: deliveries.product_id
19. 0.000 0.000 ↓ 0.0

Gather (cost=75,200.74..76,208.84 rows=9,488 width=36) (actual rows= loops=)

  • Workers Planned: 2
20. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=74,200.74..74,260.04 rows=4,744 width=36) (actual rows= loops=)

  • Group Key: deliveries.product_id
21. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on deliveries (cost=0.00..72,681.37 rows=303,874 width=9) (actual rows= loops=)

  • Filter: ((status)::text = 'delivered'::text)
22. 0.000 0.000 ↓ 0.0

Hash (cost=65,710.71..65,710.71 rows=5,333 width=36) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Subquery Scan on ap_tot (cost=65,590.71..65,710.71 rows=5,333 width=36) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Finalize HashAggregate (cost=65,590.71..65,657.38 rows=5,333 width=36) (actual rows= loops=)

  • Group Key: prescriptions.product_id
25. 0.000 0.000 ↓ 0.0

Gather (cost=64,377.46..65,510.72 rows=10,666 width=36) (actual rows= loops=)

  • Workers Planned: 2
26. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=63,377.46..63,444.12 rows=5,333 width=36) (actual rows= loops=)

  • Group Key: prescriptions.product_id
27. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on prescriptions (cost=0.00..62,144.64 rows=246,564 width=9) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=92,134.73..92,134.73 rows=4,619 width=36) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on ad_1 (cost=92,030.80..92,134.73 rows=4,619 width=36) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

HashAggregate (cost=92,030.80..92,088.54 rows=4,619 width=36) (actual rows= loops=)

  • Group Key: deliveries_1.product_id
31. 0.000 0.000 ↓ 0.0

Seq Scan on deliveries deliveries_1 (cost=0.00..91,945.24 rows=17,112 width=9) (actual rows= loops=)

  • Filter: (((status)::text = 'delivered'::text) AND (facility_id = 1) AND (delivered_at > (now() - '28 days'::interval)))
32. 0.000 0.000 ↓ 0.0

Hash (cost=92,146.27..92,146.27 rows=4,619 width=36) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Subquery Scan on ad_2 (cost=92,030.80..92,146.27 rows=4,619 width=36) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

HashAggregate (cost=92,030.80..92,100.08 rows=4,619 width=36) (actual rows= loops=)

  • Group Key: deliveries_2.product_id
35. 0.000 0.000 ↓ 0.0

Seq Scan on deliveries deliveries_2 (cost=0.00..91,945.24 rows=17,112 width=9) (actual rows= loops=)

  • Filter: (((status)::text = 'delivered'::text) AND (facility_id = 1) AND (delivered_at > (now() - '28 days'::interval)))
36. 0.000 0.000 ↓ 0.0

Hash (cost=92,230.15..92,230.15 rows=2,460 width=36) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on ad_3 (cost=92,148.82..92,230.15 rows=2,460 width=36) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=92,148.82..92,205.55 rows=2,460 width=36) (actual rows= loops=)

  • Group Key: deliveries_3.product_id
39. 0.000 0.000 ↓ 0.0

Sort (cost=92,148.82..92,157.48 rows=3,463 width=9) (actual rows= loops=)

  • Sort Key: deliveries_3.product_id
40. 0.000 0.000 ↓ 0.0

Seq Scan on deliveries deliveries_3 (cost=0.00..91,945.24 rows=3,463 width=9) (actual rows= loops=)

  • Filter: (((status)::text = 'delivered'::text) AND (facility_id = 1) AND (delivered_at > (now() - '7 days'::interval)))
41. 0.000 0.000 ↓ 0.0

Hash (cost=560.33..560.33 rows=11,736 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on inventory_items (cost=0.00..560.33 rows=11,736 width=8) (actual rows= loops=)

  • Filter: (facility_id = 1)
43.          

CTE products_2

44. 0.000 0.000 ↓ 0.0

Sort (cost=11,509.12..11,560.30 rows=20,472 width=354) (actual rows= loops=)

  • Sort Key: ((GREATEST(sum(products_1.l7d_moving_avg), sum(products_1.l7d_disp_qty), products_1.top_200_min) - sum(products_1.qoh)))
45. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=5,027.25..10,043.19 rows=20,472 width=354) (actual rows= loops=)

  • Group Key: products_1.gcn_seqno, products_1.is_breakable, products_1.is_brand, products_1.size, products_1.top_200_min
46. 0.000 0.000 ↓ 0.0

Sort (cost=5,027.25..5,155.21 rows=51,185 width=226) (actual rows= loops=)

  • Sort Key: products_1.gcn_seqno, products_1.is_breakable, products_1.is_brand, products_1.size, products_1.top_200_min
47. 0.000 0.000 ↓ 0.0

CTE Scan on products_1 (cost=0.00..1,023.70 rows=51,185 width=226) (actual rows= loops=)

48.          

CTE products_to_order

49. 0.000 0.000 ↓ 0.0

CTE Scan on products_2 (cost=0.00..460.62 rows=102 width=354) (actual rows= loops=)

  • Filter: (need_to_order = 'true'::text)
50.          

CTE isotretinoin_gp

51. 0.000 0.000 ↓ 0.0

Sort (cost=1,151.91..1,151.99 rows=32 width=67) (actual rows= loops=)

  • Sort Key: ip.net_price_per_unit DESC
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=28.19..1,151.11 rows=32 width=67) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=27.76..816.25 rows=24 width=38) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on products p_1 (cost=27.34..426.08 rows=40 width=31) (actual rows= loops=)

  • Recheck Cond: ((gcn_seqno)::text = ANY ('{36045,36046,72730,53055,72731,36047}'::text[]))
  • Filter: active
55. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_products_on_gcn_seqno (cost=0.00..27.33 rows=108 width=0) (actual rows= loops=)

  • Index Cond: ((gcn_seqno)::text = ANY ('{36045,36046,72730,53055,72731,36047}'::text[]))
56. 0.000 0.000 ↓ 0.0

Index Scan using index_product_packages_on_product_id on product_packages pp_1 (cost=0.42..9.74 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (product_id = p_1.id)
  • Filter: ((sku IS NOT NULL) AND (size < '100'::numeric))
57. 0.000 0.000 ↓ 0.0

Index Scan using index_inventory_prices_on_ndc on inventory_prices ip (cost=0.43..13.90 rows=5 width=28) (actual rows= loops=)

  • Index Cond: ((ndc)::text = (pp_1.sku)::text)
  • Filter: ((obsolete_at IS NULL) AND (net_price_per_unit IS NOT NULL) AND ((source)::text <> 'bellco'::text))
58.          

CTE best_isotretinoin_prices

59. 0.000 0.000 ↓ 0.0

Subquery Scan on s (cost=1.44..2.64 rows=1 width=168) (actual rows= loops=)

  • Filter: (s.row_number = 1)
60. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1.44..2.24 rows=32 width=168) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Sort (cost=1.44..1.52 rows=32 width=160) (actual rows= loops=)

  • Sort Key: isotretinoin_gp.medication, isotretinoin_gp.gcn_seqno, isotretinoin_gp.net_price_per_unit, isotretinoin_gp.source
62. 0.000 0.000 ↓ 0.0

CTE Scan on isotretinoin_gp (cost=0.00..0.64 rows=32 width=160) (actual rows= loops=)

63.          

CTE best_isotretinoin_prices_final

64. 0.000 0.000 ↓ 0.0

CTE Scan on best_isotretinoin_prices (cost=0.00..0.08 rows=1 width=160) (actual rows= loops=)

65.          

CTE gp

66. 0.000 0.000 ↓ 0.0

Gather (cost=12,750.59..186,263.34 rows=22,359 width=143) (actual rows= loops=)

  • Workers Planned: 1
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11,750.59..183,027.44 rows=13,152 width=143) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,750.16..16,766.28 rows=10,221 width=62) (actual rows= loops=)

  • Hash Cond: (pp_2.product_id = p_2.id)
69. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on product_packages pp_2 (cost=0.00..4,699.65 rows=120,555 width=20) (actual rows= loops=)

  • Filter: (size < '500'::numeric)
70. 0.000 0.000 ↓ 0.0

Hash (cost=11,503.30..11,503.30 rows=19,749 width=46) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Seq Scan on products p_2 (cost=0.00..11,503.30 rows=19,749 width=46) (actual rows= loops=)

  • Filter: (active AND (gcn_seqno IS NOT NULL) AND is_breakable)
72. 0.000 0.000 ↓ 0.0

Index Scan using index_inventory_prices_on_ndc on inventory_prices ip_1 (cost=0.43..16.22 rows=5 width=81) (actual rows= loops=)

  • Index Cond: ((ndc)::text = (pp_2.sku)::text)
  • Filter: ((obsolete_at IS NULL) AND (net_price_per_unit IS NOT NULL) AND ((source)::text <> 'bellco'::text) AND ((ndc)::text <> '49348097310'::text) AND ((ndc)::text <> '69367015804'::text) AND ((ndc)::text <> '69367015904'::text) AND ((ndc)::text <> '51407020901'::text) AND ((ndc)::text <> '76282034101'::text) AND ((ndc)::text <> '76282034201'::text) AND ((ndc)::text <> '76282034001'::text) AND ((ndc)::text <> ALL ('{24658026018,24658026060}'::text[])) AND ((ndc)::text <> '69102031901'::text))
73.          

CTE best_breakable_prices

74. 0.000 0.000 ↓ 0.0

Subquery Scan on s_1 (cost=2,062.46..2,900.92 rows=112 width=188) (actual rows= loops=)

  • Filter: (s_1.row_number = 1)
75. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,062.46..2,621.43 rows=22,359 width=188) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Sort (cost=2,062.46..2,118.36 rows=22,359 width=180) (actual rows= loops=)

  • Sort Key: gp.gcn_seqno, gp.is_brand, gp.net_price_per_unit, gp.source
77. 0.000 0.000 ↓ 0.0

CTE Scan on gp (cost=0.00..447.18 rows=22,359 width=180) (actual rows= loops=)

78.          

CTE gp2

79. 0.000 0.000 ↓ 0.0

Gather (cost=20,057.38..366,919.83 rows=84,617 width=143) (actual rows= loops=)

  • Workers Planned: 2
80. 0.000 0.000 ↓ 0.0

Hash Join (cost=19,057.38..357,458.13 rows=35,257 width=143) (actual rows= loops=)

  • Hash Cond: ((ip_2.ndc)::text = (pp_3.sku)::text)
81. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on inventory_prices ip_2 (cost=0.00..336,763.03 rows=68,542 width=81) (actual rows= loops=)

  • Filter: ((obsolete_at IS NULL) AND (net_price_per_unit IS NOT NULL) AND ((source)::text <> 'bellco'::text) AND ((ndc)::text <> '47682080313'::text) AND ((ndc)::text <> '00193955601'::text) AND ((ndc)::text <> '00245087135'::text) AND ((ndc)::text <> '65862091564'::text) AND ((ndc)::text <> ALL ('{08214035721,08214035726,08214035729}'::text[])))
82. 0.000 0.000 ↓ 0.0

Hash (cost=18,240.91..18,240.91 rows=65,317 width=62) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,333.36..18,240.91 rows=65,317 width=62) (actual rows= loops=)

  • Hash Cond: (pp_3.product_id = p_3.id)
84. 0.000 0.000 ↓ 0.0

Seq Scan on product_packages pp_3 (cost=0.00..5,306.12 rows=229,112 width=20) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=11,503.30..11,503.30 rows=66,405 width=46) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on products p_3 (cost=0.00..11,503.30 rows=66,405 width=46) (actual rows= loops=)

  • Filter: (active AND (gcn_seqno IS NOT NULL) AND (NOT is_breakable))
87.          

CTE best_unbreakable_prices

88. 0.000 0.000 ↓ 0.0

Subquery Scan on s_2 (cost=8,617.67..12,002.35 rows=423 width=188) (actual rows= loops=)

  • Filter: (s_2.row_number = 1)
89. 0.000 0.000 ↓ 0.0

WindowAgg (cost=8,617.67..10,944.64 rows=84,617 width=188) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Sort (cost=8,617.67..8,829.22 rows=84,617 width=180) (actual rows= loops=)

  • Sort Key: gp2.gcn_seqno, gp2.is_brand, gp2.size, gp2.net_price_per_unit, gp2.source
91. 0.000 0.000 ↓ 0.0

CTE Scan on gp2 (cost=0.00..1,692.34 rows=84,617 width=180) (actual rows= loops=)

92.          

CTE final

93. 0.000 0.000 ↓ 0.0

Sort (cost=30.14..30.39 rows=101 width=756) (actual rows= loops=)

  • Sort Key: best_unbreakable_prices.sku
94. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8.29..26.78 rows=101 width=756) (actual rows= loops=)

  • Hash Cond: (products_to_order.size = ((best_isotretinoin_prices_final.size)::integer)::numeric)
95. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8.26..24.46 rows=101 width=436) (actual rows= loops=)

  • Hash Cond: (((products_to_order.gcn_seqno)::text = (best_breakable_prices.gcn_seqno)::text) AND (products_to_order.is_breakable = best_breakable_prices.is_breakable) AND (products_to_order.is_brand = best_breakable_prices.is_brand))
96. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4.06..18.88 rows=101 width=322) (actual rows= loops=)

  • Hash Cond: (((best_unbreakable_prices.gcn_seqno)::text = (products_to_order.gcn_seqno)::text) AND (best_unbreakable_prices.is_breakable = products_to_order.is_breakable) AND (best_unbreakable_prices.is_brand = products_to_order.is_brand) AND (best_unbreakable_prices.size = products_to_order.size))
97. 0.000 0.000 ↓ 0.0

CTE Scan on best_unbreakable_prices (cost=0.00..8.46 rows=423 width=148) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Hash (cost=2.04..2.04 rows=101 width=226) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

CTE Scan on products_to_order (cost=0.00..2.04 rows=101 width=226) (actual rows= loops=)

  • Filter: (gcn_seqno IS NOT NULL)
100. 0.000 0.000 ↓ 0.0

Hash (cost=2.24..2.24 rows=112 width=148) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

CTE Scan on best_breakable_prices (cost=0.00..2.24 rows=112 width=148) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=128) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

CTE Scan on best_isotretinoin_prices_final (cost=0.00..0.02 rows=1 width=128) (actual rows= loops=)

104.          

CTE final_1

105. 0.000 0.000 ↓ 0.0

Sort (cost=13.80..14.03 rows=91 width=450) (actual rows= loops=)

  • Sort Key: ((final.qty_to_order * final.net_price_per_unit)) DESC
106. 0.000 0.000 ↓ 0.0

CTE Scan on final (cost=0.00..10.84 rows=91 width=450) (actual rows= loops=)

  • Filter: ((ndc_to_order IS NOT NULL) AND ((gcn_seqno)::text <> ALL ('{21444,43027}'::text[])) AND ((gcn_seqno)::text <> '21900'::text) AND ((gcn_seqno)::text <> '65777'::text) AND ((gcn_seqno)::text <> '77445'::text) AND ((ndc_to_order)::text <> '63481068447'::text) AND ((gcn_seqno)::text <> ALL ('{78046,77264,77265,78047}'::text[])))
107. 0.000 0.000 ↓ 0.0

CTE Scan on final_1 (cost=0.00..2.53 rows=51 width=482) (actual rows= loops=)

  • Filter: ((qoh < three_day_qty_to_stock) OR (qoh < top_200_min))