explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pkRC

Settings
# exclusive inclusive rows x rows loops node
1. 7.368 84,531.953 ↓ 50.0 100 1

Nested Loop Left Join (cost=15,874.92..37,447.49 rows=2 width=1,728) (actual time=2,226.257..84,531.953 rows=100 loops=1)

  • Join Filter: (o_3.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 4,949
2.          

CTE cte_orders

3. 0.404 35.238 ↓ 1.1 100 1

GroupAggregate (cost=1,578.09..1,581.38 rows=94 width=63) (actual time=34.742..35.238 rows=100 loops=1)

  • Group Key: o_4.orderid
4. 0.678 34.834 ↓ 19.8 1,863 1

Sort (cost=1,578.09..1,578.33 rows=94 width=39) (actual time=34.718..34.834 rows=1,863 loops=1)

  • Sort Key: o_4.orderid DESC
  • Sort Method: quicksort Memory: 193kB
5. 0.510 34.156 ↓ 19.8 1,863 1

Nested Loop Left Join (cost=1.97..1,575.01 rows=94 width=39) (actual time=0.801..34.156 rows=1,863 loops=1)

6. 1.646 31.783 ↓ 19.8 1,863 1

Nested Loop Left Join (cost=1.69..1,545.47 rows=94 width=39) (actual time=0.786..31.783 rows=1,863 loops=1)

7. 1.600 28.274 ↓ 19.8 1,863 1

Nested Loop (cost=1.41..1,515.16 rows=94 width=51) (actual time=0.771..28.274 rows=1,863 loops=1)

8. 1.415 24.811 ↓ 19.8 1,863 1

Nested Loop (cost=0.98..1,457.23 rows=94 width=51) (actual time=0.751..24.811 rows=1,863 loops=1)

9. 0.591 21.533 ↓ 19.8 1,863 1

Nested Loop (cost=0.70..1,429.34 rows=94 width=51) (actual time=0.721..21.533 rows=1,863 loops=1)

10. 1.082 1.082 ↓ 1.1 993 1

Index Scan using ix_orders_businessid on orders o_4 (cost=0.28..65.46 rows=917 width=22) (actual time=0.027..1.082 rows=993 loops=1)

  • Index Cond: (businessid = ANY ('{0,941}'::integer[]))
  • Filter: (sourceid = ANY ('{0,1,2,3,4}'::integer[]))
11. 19.860 19.860 ↓ 2.0 2 993

Index Scan using ix_orderdetails_orderid on orderdetails od_7 (cost=0.42..1.48 rows=1 width=41) (actual time=0.018..0.020 rows=2 loops=993)

  • Index Cond: (orderid = o_4.orderid)
  • Filter: ((salesmanagerid = 785) AND (statusid = ANY ('{0,1,3}'::integer[])) AND ((785 = salesmanagerid) OR (o_4.createdbyemployeeid = 785)))
  • Rows Removed by Filter: 20
12. 1.863 1.863 ↑ 1.0 1 1,863

Index Only Scan using product_pkey on product pr (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,863)

  • Index Cond: (productid = od_7.productid)
  • Heap Fetches: 1,863
13. 1.863 1.863 ↑ 1.0 1 1,863

Index Only Scan using ix_person_personidtype on person p_8 (cost=0.42..0.62 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,863)

  • Index Cond: (personid = o_4.clientid)
  • Heap Fetches: 1,863
14. 1.863 1.863 ↑ 1.0 1 1,863

Index Only Scan using stockproduct_unique on stockproduct sp (cost=0.28..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1,863)

  • Index Cond: ((productid = od_7.productid) AND (businessid = o_4.businessid) AND (stockid = od_7.stockid))
  • Heap Fetches: 1,334
15. 1.863 1.863 ↓ 0.0 0 1,863

Index Scan using ix_ordertask_orderdetailsid on ordertask ot_3 (cost=0.29..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,863)

  • Index Cond: (orderdetailsid = od_7.orderdetailsid)
16.          

CTE cte_partialavailability

17. 0.776 146.919 ↑ 2.8 244 1

Subquery Scan on t_2 (cost=4,481.31..6,497.51 rows=680 width=76) (actual time=140.191..146.919 rows=244 loops=1)

  • Filter: (t_2.required > (SubPlan 3))
  • Rows Removed by Filter: 1,492
18. 24.751 140.691 ↑ 1.2 1,736 1

HashAggregate (cost=4,481.31..4,506.81 rows=2,040 width=44) (actual time=140.015..140.691 rows=1,736 loops=1)

  • Group Key: od_8.productid, COALESCE(od_8.batchid, 0), o_5.businessid
19. 18.226 115.940 ↓ 2.7 101,361 1

Hash Join (cost=835.27..4,101.26 rows=38,005 width=17) (actual time=9.760..115.940 rows=101,361 loops=1)

  • Hash Cond: (od_8.productid = p_9.productid)
20. 18.611 97.116 ↓ 2.7 101,361 1

Hash Left Join (cost=784.84..3,950.83 rows=38,005 width=17) (actual time=9.146..97.116 rows=101,361 loops=1)

  • Hash Cond: (o_5.businessid = c_1.companyid)
  • Filter: ((od_8.statusid = 1) OR (od_8.statusid = 3) OR (COALESCE(c_1.offer_in_availability, false) AND (od_8.statusid = 0)))
21. 24.041 78.222 ↓ 2.0 101,361 1

Hash Join (cost=768.53..3,799.93 rows=50,731 width=21) (actual time=8.855..78.222 rows=101,361 loops=1)

  • Hash Cond: (od_8.orderid = o_5.orderid)
22. 45.362 45.362 ↑ 1.0 101,361 1

Seq Scan on orderdetails od_8 (cost=0.00..2,764.88 rows=101,462 width=21) (actual time=0.008..45.362 rows=101,361 loops=1)

  • Filter: ((statusid = 1) OR (statusid = 3) OR (statusid = 0))
  • Rows Removed by Filter: 33,935
23. 0.960 8.819 ↓ 2.0 6,096 1

Hash (cost=730.38..730.38 rows=3,052 width=8) (actual time=8.819..8.819 rows=6,096 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 303kB
24. 1.763 7.859 ↓ 2.0 6,096 1

Seq Scan on orders o_5 (cost=0.00..730.38 rows=3,052 width=8) (actual time=0.011..7.859 rows=6,096 loops=1)

  • Filter: (SubPlan 4)
25.          

SubPlan (for Seq Scan)

26. 6.096 6.096 ↑ 1.0 1 6,096

Seq Scan on stockbusiness stockbusiness_2 (cost=0.00..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=6,096)

  • Filter: (businessid = o_5.businessid)
  • Rows Removed by Filter: 3
27. 0.088 0.283 ↑ 1.0 667 1

Hash (cost=7.97..7.97 rows=667 width=5) (actual time=0.283..0.283 rows=667 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
28. 0.195 0.195 ↑ 1.0 667 1

Seq Scan on company c_1 (cost=0.00..7.97 rows=667 width=5) (actual time=0.008..0.195 rows=667 loops=1)

29. 0.216 0.598 ↑ 1.0 1,815 1

Hash (cost=27.75..27.75 rows=1,815 width=4) (actual time=0.598..0.598 rows=1,815 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 80kB
30. 0.382 0.382 ↑ 1.0 1,815 1

Seq Scan on product p_9 (cost=0.00..27.75 rows=1,815 width=4) (actual time=0.008..0.382 rows=1,815 loops=1)

31.          

SubPlan (for Subquery Scan)

32. 0.000 0.244 ↑ 1.0 1 244

Aggregate (cost=0.71..0.72 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=244)

33. 0.000 0.244 ↓ 0.0 0 244

Nested Loop (cost=0.28..0.71 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=244)

  • Join Filter: (stt.stockid = stockbusiness.stockid)
34. 0.244 0.244 ↓ 0.0 0 244

Index Scan using stockproduct_unique on stockproduct stt (cost=0.28..0.51 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=244)

  • Index Cond: ((productid = t_2.productid) AND (businessid = t_2.businessid))
  • Filter: CASE WHEN (t_2.batchid = 0) THEN true ELSE (COALESCE(batchid, 0) = t_2.batchid) END
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on stockbusiness (cost=0.00..0.19 rows=1 width=4) (never executed)

  • Filter: (businessid = t_2.businessid)
36. 1.736 5.208 ↑ 1.0 1 1,736

Aggregate (cost=0.71..0.72 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1,736)

37. 0.000 3.472 ↑ 1.0 1 1,736

Nested Loop (cost=0.28..0.71 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=1,736)

  • Join Filter: (stt_1.stockid = stockbusiness_1.stockid)
38. 3.472 3.472 ↑ 1.0 1 1,736

Index Scan using stockproduct_unique on stockproduct stt_1 (cost=0.28..0.51 rows=1 width=9) (actual time=0.001..0.002 rows=1 loops=1,736)

  • Index Cond: ((productid = t_2.productid) AND (businessid = t_2.businessid))
  • Filter: CASE WHEN (t_2.batchid = 0) THEN true ELSE (COALESCE(batchid, 0) = t_2.batchid) END
39. 0.000 0.000 ↑ 1.0 1 1,486

Seq Scan on stockbusiness stockbusiness_1 (cost=0.00..0.19 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1,486)

  • Filter: (businessid = t_2.businessid)
  • Rows Removed by Filter: 3
40. 0.490 716.585 ↓ 100.0 100 1

Nested Loop Left Join (cost=7,505.77..8,301.75 rows=1 width=1,419) (actual time=142.457..716.585 rows=100 loops=1)

  • Join Filter: (ord_3.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 4,950
41. 0.251 692.195 ↓ 100.0 100 1

Nested Loop Left Join (cost=6,137.12..6,912.88 rows=1 width=1,387) (actual time=136.499..692.195 rows=100 loops=1)

  • Join Filter: (ord_2.orderid = c_o.orderid)
42. 12.541 688.844 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,878.58..6,653.76 rows=1 width=1,355) (actual time=133.370..688.844 rows=100 loops=1)

  • Join Filter: (c_o.orderid = ot_2.orderid)
  • Rows Removed by Join Filter: 263,576
43. 0.227 366.803 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,729.77..6,504.52 rows=1 width=1,323) (actual time=93.671..366.803 rows=100 loops=1)

44. 0.193 366.576 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,729.49..6,504.23 rows=1 width=1,309) (actual time=93.662..366.576 rows=100 loops=1)

45. 0.255 366.383 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,729.07..6,503.68 rows=1 width=1,295) (actual time=93.655..366.383 rows=100 loops=1)

46. 0.227 366.128 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,728.79..6,503.39 rows=1 width=1,277) (actual time=93.641..366.128 rows=100 loops=1)

47. 0.355 365.501 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,728.37..6,502.84 rows=1 width=1,263) (actual time=93.626..365.501 rows=100 loops=1)

  • Join Filter: (c_o_8.orderid = o.orderid)
  • Rows Removed by Join Filter: 3,572
48. 0.151 361.346 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,652.67..6,422.33 rows=1 width=1,231) (actual time=93.301..361.346 rows=100 loops=1)

  • Join Filter: (c_o_7.orderid = o.orderid)
49. 0.458 359.195 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,572.49..6,341.26 rows=1 width=1,199) (actual time=91.364..359.195 rows=100 loops=1)

  • Join Filter: (c_o_6.orderid = o.orderid)
  • Rows Removed by Join Filter: 4,947
50. 0.447 353.137 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,510.17..6,277.72 rows=1 width=1,167) (actual time=90.818..353.137 rows=100 loops=1)

  • Join Filter: (od_2.orderid = o.orderid)
  • Rows Removed by Join Filter: 4,950
51. 0.192 344.890 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,265.52..6,009.80 rows=1 width=1,031) (actual time=88.738..344.890 rows=100 loops=1)

52. 0.271 344.598 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,265.38..6,009.64 rows=1 width=1,020) (actual time=88.727..344.598 rows=100 loops=1)

53. 0.271 344.127 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,265.23..6,009.47 rows=1 width=1,009) (actual time=88.715..344.127 rows=100 loops=1)

54. 0.328 341.956 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,264.81..6,009.02 rows=1 width=977) (actual time=88.703..341.956 rows=100 loops=1)

55. 0.326 334.128 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,264.38..6,008.58 rows=1 width=945) (actual time=88.687..334.128 rows=100 loops=1)

  • Join Filter: (pp_1.personid = p.personid)
  • Rows Removed by Join Filter: 2,615
56. 0.443 333.002 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,169.12..5,910.59 rows=1 width=917) (actual time=88.347..333.002 rows=100 loops=1)

  • Join Filter: (p_3.personid = p.personid)
  • Rows Removed by Join Filter: 4,030
57. 0.526 322.159 ↓ 100.0 100 1

Nested Loop Left Join (cost=5,061.82..5,802.73 rows=1 width=885) (actual time=87.021..322.159 rows=100 loops=1)

  • Join Filter: (ord_1.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 4,950
58. 0.711 192.533 ↓ 100.0 100 1

Nested Loop (cost=3,079.56..3,727.01 rows=1 width=733) (actual time=60.344..192.533 rows=100 loops=1)

  • Join Filter: (o.orderid = od_1.orderid)
  • Rows Removed by Join Filter: 4,950
59. 0.285 46.522 ↓ 100.0 100 1

Nested Loop (cost=989.27..990.06 rows=1 width=368) (actual time=43.544..46.522 rows=100 loops=1)

60. 0.332 44.937 ↓ 100.0 100 1

Nested Loop (cost=988.85..989.51 rows=1 width=305) (actual time=43.531..44.937 rows=100 loops=1)

  • Join Filter: (o.stockid = stk.stockid)
61. 0.214 44.505 ↓ 100.0 100 1

Nested Loop (cost=988.85..989.37 rows=1 width=297) (actual time=43.524..44.505 rows=100 loops=1)

62. 0.281 43.991 ↓ 100.0 100 1

Merge Join (cost=988.57..989.08 rows=1 width=275) (actual time=43.510..43.991 rows=100 loops=1)

  • Merge Cond: (o.orderid = ddl.orderid)
63. 0.221 40.038 ↓ 20.0 100 1

Sort (cost=458.00..458.01 rows=5 width=239) (actual time=39.889..40.038 rows=100 loops=1)

  • Sort Key: c_o.orderid DESC
  • Sort Method: quicksort Memory: 53kB
64. 0.067 39.817 ↓ 20.0 100 1

Merge Right Join (cost=368.10..457.94 rows=5 width=239) (actual time=39.111..39.817 rows=100 loops=1)

  • Merge Cond: (od.orderid = o.orderid)
  • Filter: ((785 = ANY ((array_remove(array_agg(DISTINCT od.salesmanagerid), NULL::integer)))) OR (o.createdbyemployeeid = 785))
65. 0.520 39.487 ↑ 20.8 100 1

GroupAggregate (cost=324.14..386.66 rows=2,084 width=156) (actual time=38.846..39.487 rows=100 loops=1)

  • Group Key: od.orderid
66. 0.609 38.967 ↓ 1.0 2,148 1

Sort (cost=324.14..329.35 rows=2,084 width=32) (actual time=38.828..38.967 rows=2,148 loops=1)

  • Sort Key: od.orderid
  • Sort Method: quicksort Memory: 264kB
67. 0.400 38.358 ↓ 1.0 2,148 1

Hash Join (cost=55.65..209.26 rows=2,084 width=32) (actual time=35.466..38.358 rows=2,148 loops=1)

  • Hash Cond: (od.productid = p_1.productid)
68. 1.721 37.285 ↓ 1.1 2,258 1

Nested Loop (cost=0.70..148.82 rows=2,086 width=36) (actual time=34.769..37.285 rows=2,258 loops=1)

  • -> Index Scan using ix_orderdetails_orderid on orderdetails od (cost=0.42..0.91 rows=24 width=36) (actualtime=0.003..0.009 rows=23 loops=100)
69. 0.077 35.564 ↓ 1.1 100 1

Nested Loop (cost=0.28..40.88 rows=94 width=8) (actual time=34.760..35.564 rows=100 loops=1)

  • Index Cond: (orderid = o_1.orderid)
70. 35.287 35.287 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_1 (cost=0.00..1.88 rows=94 width=4) (actual time=34.745..35.287 rows=100 loops=1)

71. 0.200 0.200 ↑ 1.0 1 100

Index Only Scan using order_pkey on orders o_1 (cost=0.28..0.41 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (orderid = c_o_1.orderid)
  • Heap Fetches: 100
72. 0.218 0.673 ↓ 1.0 1,814 1

Hash (cost=32.29..32.29 rows=1,813 width=4) (actual time=0.673..0.673 rows=1,814 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 80kB
73. 0.455 0.455 ↓ 1.0 1,814 1

Seq Scan on product p_1 (cost=0.00..32.29 rows=1,813 width=4) (actual time=0.010..0.455 rows=1,814 loops=1)

  • Filter: (producttypeid <> ALL ('{5,6}'::integer[]))
  • Rows Removed by Filter: 1
74. 0.059 0.263 ↓ 1.1 100 1

Sort (cost=43.96..44.20 rows=94 width=183) (actual time=0.257..0.263 rows=100 loops=1)

  • Sort Key: c_o.orderid
  • Sort Method: quicksort Memory: 44kB
75. 0.093 0.204 ↓ 1.1 100 1

Nested Loop (cost=0.28..40.88 rows=94 width=183) (actual time=0.007..0.204 rows=100 loops=1)

76. 0.011 0.011 ↓ 1.1 100 1

CTE Scan on cte_orders c_o (cost=0.00..1.88 rows=94 width=36) (actual time=0.001..0.011 rows=100 loops=1)

77. 0.100 0.100 ↑ 1.0 1 100

Index Scan using order_pkey on orders o (cost=0.28..0.41 rows=1 width=147) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (orderid = c_o.orderid)
78. 0.076 3.672 ↓ 1.1 100 1

Sort (cost=530.57..530.81 rows=94 width=36) (actual time=3.616..3.672 rows=100 loops=1)

  • Sort Key: ddl.orderid DESC
  • Sort Method: quicksort Memory: 29kB
79. 0.008 3.596 ↓ 1.1 100 1

Subquery Scan on ddl (cost=525.38..527.49 rows=94 width=36) (actual time=3.569..3.596 rows=100 loops=1)

80. 0.159 3.588 ↓ 1.1 100 1

HashAggregate (cost=525.38..526.55 rows=94 width=36) (actual time=3.568..3.588 rows=100 loops=1)

  • Group Key: ord.orderid
81. 0.156 3.429 ↓ 1.0 530 1

Nested Loop Left Join (cost=244.88..513.75 rows=517 width=29) (actual time=1.215..3.429 rows=530 loops=1)

82. 0.080 3.273 ↓ 1.0 530 1

Hash Left Join (cost=244.59..350.91 rows=517 width=29) (actual time=1.205..3.273 rows=530 loops=1)

  • Hash Cond: (odd.orderdetailsid = dod.orderdetailsid)
83. 0.518 3.156 ↓ 1.0 530 1

Merge Right Join (cost=242.33..346.70 rows=517 width=24) (actual time=1.154..3.156 rows=530 loops=1)

  • Merge Cond: (ot.orderdetailsid = odd.orderdetailsid)
84. 1.536 1.536 ↑ 1.9 5,479 1

Index Scan using ix_ordertask_orderdetailsid on ordertask ot (cost=0.29..166.35 rows=10,351 width=8) (actual time=0.012..1.536 rows=5,479 loops=1)

85. 0.207 1.102 ↓ 1.0 530 1

Sort (cost=242.04..243.34 rows=517 width=20) (actual time=1.074..1.102 rows=530 loops=1)

  • Sort Key: odd.orderdetailsid
  • Sort Method: quicksort Memory: 66kB
86. 0.183 0.895 ↓ 1.0 530 1

Nested Loop (cost=0.42..218.74 rows=517 width=20) (actual time=0.015..0.895 rows=530 loops=1)

87. 0.012 0.012 ↓ 1.1 100 1

CTE Scan on cte_orders ord (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.012 rows=100 loops=1)

88. 0.700 0.700 ↑ 1.0 5 100

Index Scan using ix_orderdetails_orderid on orderdetails odd (cost=0.42..2.26 rows=5 width=20) (actual time=0.003..0.007 rows=5 loops=100)

  • Index Cond: (orderid = ord.orderid)
  • Filter: (parentid IS NULL)
  • Rows Removed by Filter: 17
89. 0.018 0.037 ↑ 1.0 96 1

Hash (cost=1.06..1.06 rows=96 width=13) (actual time=0.037..0.037 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
90. 0.019 0.019 ↑ 1.0 96 1

Seq Scan on deliveryorderdetails dod (cost=0.00..1.06 rows=96 width=13) (actual time=0.008..0.019 rows=96 loops=1)

91. 0.000 0.000 ↓ 0.0 0 530

Index Scan using pk_task on task t (cost=0.29..0.31 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=530)

  • Index Cond: (taskid = ot.taskid)
92. 0.300 0.300 ↑ 1.0 1 100

Index Scan using ix_company_companyidtypeid on company b (cost=0.28..0.29 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (companyid = o.businessid)
93. 0.100 0.100 ↑ 2.0 1 100

Seq Scan on stock stk (cost=0.00..0.12 rows=2 width=12) (actual time=0.001..0.001 rows=1 loops=100)

94. 1.300 1.300 ↑ 1.0 1 100

Index Scan using ix_person_personidtype on person p (cost=0.42..0.55 rows=1 width=63) (actual time=0.013..0.013 rows=1 loops=100)

  • Index Cond: (personid = o.clientid)
95. 140.601 145.300 ↑ 41.7 50 100

HashAggregate (cost=2,090.29..2,690.01 rows=2,086 width=445) (actual time=0.186..1.453 rows=50 loops=100)

  • Group Key: od_1.orderid
96. 1.330 4.699 ↓ 1.1 2,258 1

Nested Loop Left Join (cost=51.57..1,162.02 rows=2,086 width=91) (actual time=0.658..4.699 rows=2,258 loops=1)

97. 0.615 3.369 ↓ 1.1 2,258 1

Hash Join (cost=51.14..204.74 rows=2,086 width=91) (actual time=0.647..3.369 rows=2,258 loops=1)

  • Hash Cond: (od_1.productid = p_2.productid)
98. 0.946 2.148 ↓ 1.1 2,258 1

Nested Loop (cost=0.70..148.82 rows=2,086 width=87) (actual time=0.027..2.148 rows=2,258 loops=1)

99. 0.079 0.302 ↓ 1.1 100 1

Nested Loop (cost=0.28..40.88 rows=94 width=8) (actual time=0.016..0.302 rows=100 loops=1)

100. 0.023 0.023 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_2 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.023 rows=100 loops=1)

101. 0.200 0.200 ↑ 1.0 1 100

Index Only Scan using order_pkey on orders o_2 (cost=0.28..0.41 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (orderid = c_o_2.orderid)
  • Heap Fetches: 100
102. 0.900 0.900 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails od_1 (cost=0.42..0.91 rows=24 width=87) (actual time=0.003..0.009 rows=23 loops=100)

  • Index Cond: (orderid = o_2.orderid)
103. 0.262 0.606 ↑ 1.0 1,815 1

Hash (cost=27.75..27.75 rows=1,815 width=8) (actual time=0.605..0.606 rows=1,815 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 87kB
104. 0.344 0.344 ↑ 1.0 1,815 1

Seq Scan on product p_2 (cost=0.00..27.75 rows=1,815 width=8) (actual time=0.004..0.344 rows=1,815 loops=1)

105. 0.000 0.000 ↓ 0.0 0 2,258

Index Scan using pk_address on address odda (cost=0.43..0.46 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2,258)

  • Index Cond: (addressid = od_1.deliveryaddressid)
106. 98.700 129.100 ↑ 1.9 50 100

GroupAggregate (cost=1,982.26..2,073.61 rows=94 width=156) (actual time=0.274..1.291 rows=50 loops=100)

  • Group Key: ord_1.orderid
107. 6.883 30.400 ↑ 2.0 1,088 100

Sort (cost=1,982.26..1,987.82 rows=2,225 width=53) (actual time=0.243..0.304 rows=1,088 loops=100)

  • Sort Key: ord_1.orderid
  • Sort Method: quicksort Memory: 370kB
108. 0.473 23.517 ↓ 1.0 2,295 1

Hash Left Join (cost=502.19..1,858.55 rows=2,225 width=53) (actual time=6.451..23.517 rows=2,295 loops=1)

  • Hash Cond: (iod.invoiceid = invv.invoiceid)
109. 8.865 18.570 ↓ 1.0 2,295 1

Hash Right Join (cost=264.41..1,614.93 rows=2,225 width=29) (actual time=1.909..18.570 rows=2,295 loops=1)

  • Hash Cond: (iod.orderdetailsid = odd_1.orderdetailsid)
110. 7.847 7.847 ↓ 1.0 92,807 1

Seq Scan on invoiceorderdetails iod (cost=0.00..987.25 rows=92,805 width=13) (actual time=0.009..7.847 rows=92,807 loops=1)

111. 0.498 1.858 ↓ 1.0 2,258 1

Hash (cost=236.60..236.60 rows=2,225 width=24) (actual time=1.858..1.858 rows=2,258 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 161kB
112. 0.647 1.360 ↓ 1.0 2,258 1

Nested Loop (cost=0.42..236.60 rows=2,225 width=24) (actual time=0.010..1.360 rows=2,258 loops=1)

113. 0.013 0.013 ↓ 1.1 100 1

CTE Scan on cte_orders ord_1 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.013 rows=100 loops=1)

114. 0.700 0.700 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails odd_1 (cost=0.42..2.26 rows=24 width=24) (actual time=0.002..0.007 rows=23 loops=100)

  • Index Cond: (orderid = ord_1.orderid)
115. 1.725 4.474 ↓ 1.0 9,649 1

Hash (cost=117.18..117.18 rows=9,648 width=24) (actual time=4.474..4.474 rows=9,649 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 732kB
116. 2.749 2.749 ↓ 1.0 9,649 1

Seq Scan on invoice invv (cost=0.00..117.18 rows=9,648 width=24) (actual time=0.007..2.749 rows=9,649 loops=1)

117. 8.900 10.400 ↓ 41.0 41 100

GroupAggregate (cost=107.30..107.83 rows=1 width=36) (actual time=0.028..0.104 rows=41 loops=100)

  • Group Key: p_3.personid
118. 0.459 1.500 ↓ 64.0 64 100

Sort (cost=107.30..107.31 rows=1 width=22) (actual time=0.011..0.015 rows=64 loops=100)

  • Sort Key: p_3.personid
  • Sort Method: quicksort Memory: 33kB
119. 0.099 1.041 ↓ 109.0 109 1

Nested Loop (cost=0.85..107.29 rows=1 width=22) (actual time=0.043..1.041 rows=109 loops=1)

  • Join Filter: (pp.phonetypeid = pt.phonetypeid)
  • Rows Removed by Join Filter: 125
120. 0.000 0.942 ↓ 109.0 109 1

Nested Loop (cost=0.85..107.10 rows=1 width=18) (actual time=0.035..0.942 rows=109 loops=1)

  • Join Filter: (c_o_3.clientid = pp.personid)
121. 0.023 0.443 ↓ 1.1 100 1

Nested Loop (cost=0.42..62.08 rows=94 width=18) (actual time=0.019..0.443 rows=100 loops=1)

122. 0.020 0.020 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_3 (cost=0.00..1.88 rows=94 width=4) (actual time=0.001..0.020 rows=100 loops=1)

123. 0.400 0.400 ↑ 1.0 1 100

Index Scan using ix_person_personidtype on person p_3 (cost=0.42..0.64 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (personid = c_o_3.clientid)
124. 0.500 0.500 ↑ 1.0 1 100

Index Scan using ix_personphone_personid on personphone pp (cost=0.42..0.47 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=100)

  • Index Cond: (personid = p_3.personid)
  • Filter: ((p_3.phonebusiness)::text = (personphone)::text)
  • Rows Removed by Filter: 0
125. 0.000 0.000 ↑ 2.0 2 109

Seq Scan on phonetypes pt (cost=0.00..0.14 rows=4 width=10) (actual time=0.000..0.000 rows=2 loops=109)

126. 0.567 0.800 ↑ 3.1 27 100

HashAggregate (cost=95.26..96.10 rows=84 width=36) (actual time=0.004..0.008 rows=27 loops=100)

  • Group Key: pp_1.personid
127. 0.022 0.233 ↑ 1.2 72 1

Nested Loop (cost=0.42..74.05 rows=84 width=16) (actual time=0.013..0.233 rows=72 loops=1)

128. 0.011 0.011 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_4 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.011 rows=100 loops=1)

129. 0.200 0.200 ↑ 1.0 1 100

Index Scan using ix_personphone_personid on personphone pp_1 (cost=0.42..0.76 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (personid = c_o_4.clientid)
  • Filter: (phonetypeid = 1)
  • Rows Removed by Filter: 1
130. 7.500 7.500 ↑ 1.0 1 100

Index Scan using pk_address on address cbad (cost=0.43..0.45 rows=1 width=40) (actual time=0.075..0.075 rows=1 loops=100)

  • Index Cond: (addressid = p.billingaddressid)
131. 1.900 1.900 ↑ 1.0 1 100

Index Scan using pk_address on address cvad (cost=0.43..0.45 rows=1 width=40) (actual time=0.019..0.019 rows=1 loops=100)

  • Index Cond: (addressid = p.visitingaddressid)
132. 0.200 0.200 ↑ 1.0 1 100

Index Scan using country_pk_contries on country cbcn (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (countryid = cbad.countryid)
133. 0.100 0.100 ↑ 1.0 1 100

Index Scan using country_pk_contries on country cvcn (cost=0.14..0.16 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (countryid = cvad.countryid)
134. 5.600 7.800 ↓ 1.7 50 100

GroupAggregate (cost=244.65..267.27 rows=29 width=140) (actual time=0.023..0.078 rows=50 loops=100)

  • Group Key: od_2.orderid
135. 0.335 2.200 ↓ 1.8 53 100

Sort (cost=244.65..244.72 rows=29 width=49) (actual time=0.019..0.022 rows=53 loops=100)

  • Sort Key: od_2.orderid
  • Sort Method: quicksort Memory: 33kB
136. 0.174 1.865 ↓ 3.8 109 1

Hash Join (cost=1.91..243.94 rows=29 width=49) (actual time=0.080..1.865 rows=109 loops=1)

  • Hash Cond: (od_2.productid = p_4.productid)
137. 0.539 1.651 ↓ 1.0 2,258 1

Nested Loop (cost=0.42..236.60 rows=2,225 width=45) (actual time=0.013..1.651 rows=2,258 loops=1)

138. 0.012 0.012 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_5 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.012 rows=100 loops=1)

139. 1.100 1.100 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails od_2 (cost=0.42..2.26 rows=24 width=45) (actual time=0.003..0.011 rows=23 loops=100)

  • Index Cond: (orderid = c_o_5.orderid)
140. 0.007 0.040 ↑ 1.0 24 1

Hash (cost=1.19..1.19 rows=24 width=8) (actual time=0.040..0.040 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
141. 0.033 0.033 ↑ 1.0 24 1

Index Scan using ix_product_producttype on product p_4 (cost=0.28..1.19 rows=24 width=8) (actual time=0.015..0.033 rows=24 loops=1)

  • Index Cond: (producttypeid = 8)
142. 4.700 5.600 ↓ 1.9 50 100

GroupAggregate (cost=62.32..62.93 rows=27 width=36) (actual time=0.008..0.056 rows=50 loops=100)

  • Group Key: c_o_6.orderid
143. 0.510 0.900 ↓ 2.9 78 100

Sort (cost=62.32..62.39 rows=27 width=8) (actual time=0.005..0.009 rows=78 loops=100)

  • Sort Key: c_o_6.orderid
  • Sort Method: quicksort Memory: 32kB
144. 0.079 0.390 ↓ 6.3 170 1

Nested Loop (cost=0.42..61.68 rows=27 width=8) (actual time=0.017..0.390 rows=170 loops=1)

145. 0.011 0.011 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_6 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.011 rows=100 loops=1)

146. 0.300 0.300 ↓ 2.0 2 100

Index Scan using idx_filelink_refereceid on filelink fl (cost=0.42..0.63 rows=1 width=8) (actual time=0.003..0.003 rows=2 loops=100)

  • Index Cond: ((filetypeid = 19) AND (referenceid = c_o_6.orderid))
147. 0.100 2.000 ↓ 0.0 0 100

GroupAggregate (cost=80.18..80.63 rows=20 width=36) (actual time=0.020..0.020 rows=0 loops=100)

  • Group Key: c_o_7.orderid
148. 0.000 1.900 ↓ 0.0 0 100

Sort (cost=80.18..80.23 rows=20 width=8) (actual time=0.019..0.019 rows=0 loops=100)

  • Sort Key: c_o_7.orderid
  • Sort Method: quicksort Memory: 25kB
149. 0.320 1.931 ↓ 0.0 0 1

Merge Join (cost=50.65..79.75 rows=20 width=8) (actual time=1.931..1.931 rows=0 loops=1)

  • Merge Cond: (ot_1.taskid = fl_1.referenceid)
150. 0.051 0.384 ↓ 1.1 186 1

Sort (cost=49.62..50.06 rows=177 width=8) (actual time=0.374..0.384 rows=186 loops=1)

  • Sort Key: ot_1.taskid
  • Sort Method: quicksort Memory: 33kB
151. 0.022 0.333 ↓ 1.1 186 1

Nested Loop (cost=0.29..43.01 rows=177 width=8) (actual time=0.013..0.333 rows=186 loops=1)

152. 0.011 0.011 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_7 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.011 rows=100 loops=1)

153. 0.300 0.300 ↑ 1.0 2 100

Index Scan using ix_ordertask_orderid on ordertask ot_1 (cost=0.29..0.42 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=100)

  • Index Cond: (orderid = c_o_7.orderid)
154. 1.227 1.227 ↓ 1.0 4,279 1

Index Scan using idx_filelink_refereceid on filelink fl_1 (cost=0.42..116.52 rows=4,086 width=8) (actual time=0.008..1.227 rows=4,279 loops=1)

  • Index Cond: (filetypeid = 13)
155. 3.300 3.800 ↑ 2.6 36 100

GroupAggregate (cost=75.70..78.39 rows=94 width=36) (actual time=0.005..0.038 rows=36 loops=100)

  • Group Key: c_o_8.orderid
156. 0.258 0.500 ↑ 3.6 48 100

Sort (cost=75.70..76.12 rows=171 width=8) (actual time=0.003..0.005 rows=48 loops=100)

  • Sort Key: c_o_8.orderid
  • Sort Method: quicksort Memory: 27kB
157. 0.032 0.242 ↑ 2.8 62 1

Nested Loop (cost=0.42..69.35 rows=171 width=8) (actual time=0.014..0.242 rows=62 loops=1)

158. 0.010 0.010 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_8 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.010 rows=100 loops=1)

159. 0.200 0.200 ↑ 2.0 1 100

Index Scan using ix_contactmomentlink_orderid on contactmomentlink cml (cost=0.42..0.70 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (orderid = c_o_8.orderid)
160. 0.400 0.400 ↑ 1.0 1 100

Index Scan using ix_person_personidtype on person e (cost=0.42..0.55 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (personid = o.createdbyemployeeid)
161. 0.000 0.000 ↓ 0.0 0 100

Index Scan using ix_company_companyidtypeid on company c (cost=0.28..0.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (companyid = p.companyid)
162. 0.000 0.000 ↓ 0.0 0 100

Index Scan using ix_person_personidtype on person lnk (cost=0.42..0.55 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (personid = o.linktoclientid)
163. 0.000 0.000 ↓ 0.0 0 100

Index Scan using ix_company_companyidtypeid on company lnkc (cost=0.28..0.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (companyid = lnk.companyid)
164. 259.900 309.500 ↓ 293.0 2,637 100

GroupAggregate (cost=148.81..149.04 rows=9 width=36) (actual time=0.345..3.095 rows=2,637 loops=100)

  • Group Key: ot_2.orderid
165. 17.167 49.600 ↓ 314.2 2,828 100

Sort (cost=148.81..148.83 rows=9 width=13) (actual time=0.340..0.496 rows=2,828 loops=100)

  • Sort Key: ot_2.orderid
  • Sort Method: quicksort Memory: 449kB
166. 2.156 32.433 ↓ 608.7 5,478 1

Nested Loop (cost=1.27..148.67 rows=9 width=13) (actual time=0.069..32.433 rows=5,478 loops=1)

  • Join Filter: (t_1.taskstatusid = ts.taskstatusid)
  • Rows Removed by Join Filter: 6,952
167. 0.268 30.277 ↓ 608.7 5,478 1

Nested Loop (cost=1.27..147.62 rows=9 width=8) (actual time=0.057..30.277 rows=5,478 loops=1)

168. 4.114 19.053 ↓ 608.7 5,478 1

Nested Loop (cost=0.98..144.68 rows=9 width=8) (actual time=0.044..19.053 rows=5,478 loops=1)

169. 2.117 7.698 ↓ 32.3 7,241 1

Nested Loop (cost=0.70..74.10 rows=224 width=4) (actual time=0.027..7.698 rows=7,241 loops=1)

170. 0.010 0.010 ↑ 1.0 3 1

Index Scan using ix_product_producttype on product p_5 (cost=0.28..0.55 rows=3 width=4) (actual time=0.007..0.010 rows=3 loops=1)

  • Index Cond: (producttypeid = 3)
171. 5.571 5.571 ↓ 12.1 2,414 3

Index Scan using ix_orderdetails_productid on orderdetails od_3 (cost=0.42..22.53 rows=199 width=8) (actual time=0.013..1.857 rows=2,414 loops=3)

  • Index Cond: (productid = p_5.productid)
172. 7.241 7.241 ↑ 1.0 1 7,241

Index Scan using ix_ordertask_orderdetailsid on ordertask ot_2 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=7,241)

  • Index Cond: ((orderdetailsid = od_3.orderdetailsid) AND (orderdetailsid IS NOT NULL))
173. 10.956 10.956 ↑ 1.0 1 5,478

Index Scan using pk_task on task t_1 (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,478)

  • Index Cond: (taskid = ot_2.taskid)
  • Filter: (taskstatusid > 0)
174. 0.000 0.000 ↑ 3.5 2 5,478

Materialize (cost=0.00..0.21 rows=7 width=13) (actual time=0.000..0.000 rows=2 loops=5,478)

175. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on taskstatus ts (cost=0.00..0.17 rows=7 width=13) (actual time=0.005..0.006 rows=7 loops=1)

176. 0.000 3.100 ↓ 0.0 0 100

GroupAggregate (cost=258.54..258.83 rows=13 width=36) (actual time=0.031..0.031 rows=0 loops=100)

  • Group Key: ord_2.orderid
177. 0.000 3.100 ↓ 0.0 0 100

Sort (cost=258.54..258.57 rows=13 width=8) (actual time=0.031..0.031 rows=0 loops=100)

  • Sort Key: ord_2.orderid
  • Sort Method: quicksort Memory: 25kB
178. 0.172 3.120 ↓ 0.0 0 1

Nested Loop (cost=1.78..258.29 rows=13 width=8) (actual time=3.120..3.120 rows=0 loops=1)

  • Join Filter: (lod.orderid <> ord_2.orderid)
  • Rows Removed by Join Filter: 2,110
179. 0.174 2.092 ↓ 7.1 107 1

Hash Join (cost=1.36..243.40 rows=15 width=8) (actual time=0.053..2.092 rows=107 loops=1)

  • Hash Cond: (od_4.productid = p_6.productid)
180. 0.679 1.897 ↓ 1.0 2,258 1

Nested Loop (cost=0.42..236.60 rows=2,225 width=12) (actual time=0.014..1.897 rows=2,258 loops=1)

181. 0.018 0.018 ↓ 1.1 100 1

CTE Scan on cte_orders ord_2 (cost=0.00..1.88 rows=94 width=4) (actual time=0.001..0.018 rows=100 loops=1)

182. 1.200 1.200 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails od_4 (cost=0.42..2.26 rows=24 width=12) (actual time=0.007..0.012 rows=23 loops=100)

  • Index Cond: (orderid = ord_2.orderid)
183. 0.004 0.021 ↑ 1.0 12 1

Hash (cost=0.79..0.79 rows=12 width=4) (actual time=0.021..0.021 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
184. 0.017 0.017 ↑ 1.0 12 1

Index Scan using ix_product_producttype on product p_6 (cost=0.28..0.79 rows=12 width=4) (actual time=0.008..0.017 rows=12 loops=1)

  • Index Cond: (producttypeid = 1)
185. 0.856 0.856 ↓ 1.1 20 107

Index Scan using ix_orderdetails_deliveryproductid on orderdetails lod (cost=0.42..0.76 rows=19 width=8) (actual time=0.003..0.008 rows=20 loops=107)

  • Index Cond: (deliveryproductid = od_4.orderdetailsid)
186. 13.500 23.900 ↑ 1.9 50 100

GroupAggregate (cost=1,368.66..1,386.75 rows=94 width=36) (actual time=0.060..0.239 rows=50 loops=100)

  • Group Key: ord_3.orderid
187. 5.340 10.400 ↑ 2.1 1,082 100

Sort (cost=1,368.66..1,374.22 rows=2,225 width=8) (actual time=0.056..0.104 rows=1,082 loops=100)

  • Sort Key: ord_3.orderid
  • Sort Method: quicksort Memory: 202kB
188. 1.466 5.060 ↓ 1.0 2,258 1

Nested Loop Left Join (cost=0.84..1,244.95 rows=2,225 width=8) (actual time=0.025..5.060 rows=2,258 loops=1)

  • Join Filter: (mod.orderid <> ord_3.orderid)
  • Rows Removed by Join Filter: 2,110
189. 0.622 1.336 ↓ 1.0 2,258 1

Nested Loop (cost=0.42..236.60 rows=2,225 width=8) (actual time=0.012..1.336 rows=2,258 loops=1)

190. 0.014 0.014 ↓ 1.1 100 1

CTE Scan on cte_orders ord_3 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.014 rows=100 loops=1)

191. 0.700 0.700 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails od_5 (cost=0.42..2.26 rows=24 width=8) (actual time=0.002..0.007 rows=23 loops=100)

  • Index Cond: (orderid = ord_3.orderid)
192. 2.258 2.258 ↑ 1.0 1 2,258

Index Scan using pk_orderdetails on orderdetails mod (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,258)

  • Index Cond: (orderdetailsid = od_5.deliveryproductid)
193. 21.170 83,796.700 ↑ 9.4 50 100

GroupAggregate (cost=290.25..20,967.76 rows=472 width=36) (actual time=17.804..837.967 rows=50 loops=100)

  • Group Key: o_3.orderid
194. 1.235 151.200 ↑ 8.7 54 100

Sort (cost=290.25..291.43 rows=472 width=65) (actual time=1.500..1.512 rows=54 loops=100)

  • Sort Key: o_3.orderid
  • Sort Method: quicksort Memory: 33kB
195. 0.125 149.965 ↑ 4.3 110 1

Merge Join (cost=198.41..269.29 rows=472 width=65) (actual time=149.358..149.965 rows=110 loops=1)

  • Merge Cond: (od_6.productid = p_7.productid)
196. 0.168 148.961 ↓ 1.1 530 1

Merge Left Join (cost=198.13..209.78 rows=484 width=61) (actual time=148.743..148.961 rows=530 loops=1)

  • Merge Cond: ((od_6.productid = part.productid) AND ((COALESCE(od_6.batchid, 0)) = part.batchid) AND (o_3.businessid = part.businessid))
197. 0.251 1.638 ↓ 1.1 530 1

Sort (cost=152.54..153.75 rows=484 width=29) (actual time=1.607..1.638 rows=530 loops=1)

  • Sort Key: od_6.productid, (COALESCE(od_6.batchid, 0)), o_3.businessid
  • Sort Method: quicksort Memory: 66kB
198. 0.256 1.387 ↓ 1.1 530 1

Nested Loop (cost=0.70..130.96 rows=484 width=29) (actual time=0.041..1.387 rows=530 loops=1)

199. 0.017 0.331 ↓ 1.1 100 1

Nested Loop (cost=0.28..40.88 rows=94 width=12) (actual time=0.009..0.331 rows=100 loops=1)

200. 0.014 0.014 ↓ 1.1 100 1

CTE Scan on cte_orders c_o_9 (cost=0.00..1.88 rows=94 width=4) (actual time=0.000..0.014 rows=100 loops=1)

201. 0.300 0.300 ↑ 1.0 1 100

Index Scan using order_pkey on orders o_3 (cost=0.28..0.41 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (orderid = c_o_9.orderid)
202. 0.800 0.800 ↑ 1.0 5 100

Index Scan using ix_orderdetails_orderid on orderdetails od_6 (cost=0.42..0.91 rows=5 width=25) (actual time=0.003..0.008 rows=5 loops=100)

  • Index Cond: (orderid = o_3.orderid)
  • Filter: (parentid IS NULL)
  • Rows Removed by Filter: 17
203. 0.122 147.155 ↑ 1.2 578 1

Sort (cost=45.59..47.29 rows=680 width=44) (actual time=147.130..147.155 rows=578 loops=1)

  • Sort Key: part.productid, part.batchid, part.businessid
  • Sort Method: quicksort Memory: 36kB
204. 147.033 147.033 ↑ 2.8 244 1

CTE Scan on cte_partialavailability part (cost=0.00..13.60 rows=680 width=44) (actual time=140.195..147.033 rows=244 loops=1)

205. 0.879 0.879 ↑ 1.1 1,555 1

Index Scan using product_pkey on product p_7 (cost=0.28..49.15 rows=1,771 width=8) (actual time=0.018..0.879 rows=1,555 loops=1)

  • Filter: (producttypeid = ANY ('{0,7,12,5,6}'::integer[]))
  • Rows Removed by Filter: 47
206.          

SubPlan (for GroupAggregate)

207. 83,022.831 83,624.330 ↑ 1.0 1 5,323

Aggregate (cost=43.51..43.52 rows=1 width=32) (actual time=15.710..15.710 rows=1 loops=5,323)

208. 59.138 601.499 ↓ 1.1 16 5,323

Nested Loop (cost=9.54..39.59 rows=15 width=57) (actual time=0.063..0.113 rows=16 loops=5,323)

209. 180.982 457.778 ↓ 1.1 16 5,323

Hash Right Join (cost=9.26..33.07 rows=15 width=53) (actual time=0.060..0.086 rows=16 loops=5,323)

  • Hash Cond: ((ccpart.productid = ccod.productid) AND (ccpart.batchid = COALESCE(ccod.batchid, 0)) AND (ccpart.businessid = cco.businessid))
210. 95.814 95.814 ↑ 2.8 244 5,323

CTE Scan on cte_partialavailability ccpart (cost=0.00..13.60 rows=680 width=44) (actual time=0.000..0.018 rows=244 loops=5,323)

211. 21.292 180.982 ↓ 1.1 16 5,323

Hash (cost=9.00..9.00 rows=15 width=25) (actual time=0.034..0.034 rows=16 loops=5,323)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
212. 37.846 159.690 ↓ 1.1 16 5,323

Nested Loop (cost=0.70..9.00 rows=15 width=25) (actual time=0.007..0.030 rows=16 loops=5,323)

213. 37.261 37.261 ↓ 1.1 16 5,323

Index Scan using ix_orderdetails_parentid on orderdetails ccod (cost=0.42..1.90 rows=15 width=25) (actual time=0.003..0.007 rows=16 loops=5,323)

  • Index Cond: (parentid = od_6.orderdetailsid)
214. 84.583 84.583 ↑ 1.0 1 84,583

Index Scan using order_pkey on orders cco (cost=0.28..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=84,583)

  • Index Cond: (orderid = ccod.orderid)
215. 84.583 84.583 ↑ 1.0 1 84,583

Index Scan using product_pkey on product ccp (cost=0.28..0.43 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=84,583)

  • Index Cond: (productid = ccod.productid)
216.          

SubPlan (for Nested Loop Left Join)

217. 0.400 1.900 ↑ 1.0 1 100

Result (cost=8.80..8.81 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=100)

218.          

Initplan (for Result)

219. 1.500 1.500 ↑ 10.0 1 100

Index Scan using person_idx on person pmm (cost=0.42..8.80 rows=10 width=32) (actual time=0.010..0.015 rows=1 loops=100)

  • Index Cond: (personid = ANY ((array_remove(array_agg(DISTINCT od.salesmanagerid), NULL::integer))))
220. 0.600 1.000 ↑ 5.0 2 100

HashAggregate (cost=0.13..0.23 rows=10 width=32) (actual time=0.010..0.010 rows=2 loops=100)

  • Group Key: a.a
221. 0.400 0.400 ↓ 1.5 15 100

Function Scan on unnest a (cost=0.00..0.10 rows=10 width=32) (actual time=0.004..0.004 rows=15 loops=100)

222. 0.100 6.800 ↑ 1.0 1 100

Limit (cost=17.95..17.96 rows=1 width=8) (actual time=0.068..0.068 rows=1 loops=100)

223. 0.100 6.700 ↑ 1.0 1 100

Unique (cost=17.95..17.96 rows=1 width=8) (actual time=0.067..0.067 rows=1 loops=100)

224. 0.800 6.600 ↑ 1.0 1 100

Sort (cost=17.95..17.95 rows=1 width=8) (actual time=0.066..0.066 rows=1 loops=100)

  • Sort Key: rod.shipmentdate DESC
  • Sort Method: quicksort Memory: 25kB
225. 2.242 5.800 ↓ 19.0 19 100

Nested Loop (cost=0.84..17.94 rows=1 width=8) (actual time=0.016..0.058 rows=19 loops=100)

226. 1.300 1.300 ↑ 1.0 23 100

Index Scan using ix_orderdetails_orderid on orderdetails od_9 (cost=0.42..2.64 rows=24 width=4) (actual time=0.006..0.013 rows=23 loops=100)

  • Index Cond: (orderid = o.orderid)
227. 2.258 2.258 ↑ 1.0 1 2,258

Index Scan using pk_orderdetails on orderdetails rod (cost=0.42..0.64 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,258)

  • Index Cond: (orderdetailsid = od_9.deliveryproductid)
  • Filter: (shipmentdate IS NOT NULL)
  • Rows Removed by Filter: 0
228. 0.206 1.200 ↑ 1.0 1 100

Nested Loop (cost=2.06..15.62 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=100)

229. 0.074 0.800 ↑ 1.0 1 100

Hash Join (cost=1.78..15.19 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=100)

  • Hash Cond: (d_odd.productid = d_p.productid)
230. 0.290 0.700 ↑ 12.0 1 100

Nested Loop (cost=0.84..14.22 rows=12 width=8) (actual time=0.007..0.007 rows=1 loops=100)

231. 0.300 0.300 ↑ 18.0 1 100

Index Scan using ix_orderdetails_orderid on orderdetails odd_2 (cost=0.42..2.70 rows=18 width=8) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (orderid = c_o.orderid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1
232. 0.110 0.110 ↑ 1.0 1 110

Index Scan using pk_orderdetails on orderdetails d_odd (cost=0.42..0.64 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=110)

  • Index Cond: (orderdetailsid = odd_2.deliveryproductid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 0
233. 0.008 0.026 ↑ 1.0 12 1

Hash (cost=0.79..0.79 rows=12 width=4) (actual time=0.026..0.026 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
234. 0.018 0.018 ↑ 1.0 12 1

Index Scan using ix_product_producttype on product d_p (cost=0.28..0.79 rows=12 width=4) (actual time=0.011..0.018 rows=12 loops=1)

  • Index Cond: (producttypeid = 1)
235. 0.194 0.194 ↑ 1.0 1 97

Index Scan using product_pkey on product p_10 (cost=0.28..0.43 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=97)

  • Index Cond: (productid = odd_2.productid)
  • Filter: (producttypeid <> 1)
236. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=55.94..902.39 rows=442 width=4) (never executed)

  • Hash Cond: (odd_3.productid = p_11.productid)
237. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.12..846.40 rows=444 width=8) (never executed)

238. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..212.80 rows=671 width=4) (never executed)

239. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_product_producttype on product d_p_1 (cost=0.28..0.79 rows=12 width=4) (never executed)

  • Index Cond: (producttypeid = 1)
240. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_orderdetails_statusid_productid on orderdetails d_odd_1 (cost=0.42..16.18 rows=149 width=8) (never executed)

  • Index Cond: ((productid = d_p_1.productid) AND (statusid = ANY ('{0,1}'::integer[])))
241. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_orderdetails_deliveryproductid on orderdetails odd_3 (cost=0.42..0.80 rows=14 width=12) (never executed)

  • Index Cond: (deliveryproductid = d_odd_1.orderdetailsid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
242. 0.000 0.000 ↓ 0.0 0

Hash (cost=32.29..32.29 rows=1,803 width=4) (never executed)

243. 0.000 0.000 ↓ 0.0 0

Seq Scan on product p_11 (cost=0.00..32.29 rows=1,803 width=4) (never executed)

  • Filter: (producttypeid <> 1)
244. 0.200 0.400 ↑ 1.0 1 100

Aggregate (cost=0.51..0.52 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=100)

245. 0.200 0.200 ↓ 0.0 0 100

Index Scan using invoice_idx on invoice (cost=0.29..0.50 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=100)

  • Index Cond: (orderid = c_o.orderid)
Planning time : 34.271 ms
Execution time : 84,537.379 ms