explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p2es

Settings
# exclusive inclusive rows x rows loops node
1. 1,421.512 1,421.512 ↓ 4,438.5 8,877 1

CTE Scan on base_extra (cost=56,591.01..56,591.05 rows=2 width=648) (actual time=1,391.364..1,421.512 rows=8,877 loops=1)

2.          

CTE _session_id

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

4.          

CTE _part_no

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

6.          

CTE _comp_part

7. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

8.          

CTE _session_id

9. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

10.          

CTE _part_no

11. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

12.          

CTE _comp_part

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

14.          

CTE _qty

15. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

16.          

CTE up_id

17. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

18.          

CTE m_depth

19. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

20.          

CTE _main

21. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

22.          

CTE _update_code

23. 0.025 0.025 ↑ 1.0 1 1

Values Scan on ""*VALUES*"" (cost=0.00..0.01 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=1)

24.          

CTE b1

25. 1.452 827.212 ↓ 10.0 2,182 1

Subquery Scan on c_3 (cost=41,714.10..41,723.91 rows=218 width=160) (actual time=822.543..827.212 rows=2,182 loops=1)

26. 825.760 825.760 ↓ 10.0 2,182 1

CTE Scan on e (cost=41,714.10..41,718.46 rows=218 width=292) (actual time=822.540..825.760 rows=2,182 loops=1)

27.          

CTE a

28. 0.507 1.823 ↑ 1.0 2,182 1

Hash Right Join (cost=115.41..173.47 rows=2,183 width=25) (actual time=0.981..1.823 rows=2,182 loops=1)

  • Hash Cond: (sa.part_no = a.code)
29. 0.355 0.355 ↑ 1.0 1,509 1

Seq Scan on structure_alt sa (cost=0.00..54.09 rows=1,509 width=22) (actual time=0.005..0.355 rows=1,509 loops=1)

30. 0.415 0.961 ↑ 1.0 2,182 1

Hash (cost=88.12..88.12 rows=2,183 width=19) (actual time=0.961..0.961 rows=2,182 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 135kB
31. 0.546 0.546 ↑ 1.0 2,182 1

Seq Scan on articles a (cost=0.00..88.12 rows=2,183 width=19) (actual time=0.005..0.546 rows=2,182 loops=1)

  • Filter: (NOT closed)
  • Rows Removed by Filter: 30
32.          

CTE so_rep

33. 2.015 326.081 ↓ 3.8 23,872 1

Subquery Scan on so_reportings_total_agg (cost=9,133.31..9,917.06 rows=6,204 width=50) (actual time=290.299..326.081 rows=23,872 loops=1)

34. 19.965 324.066 ↓ 3.8 23,872 1

GroupAggregate (cost=9,133.31..9,855.02 rows=6,204 width=150) (actual time=290.297..324.066 rows=23,872 loops=1)

  • Group Key: r.sernr, r.comp_part, r.alt_code, ((COALESCE((rs.value ->> 'mat_auto_report_calc_round_to'::text), '2'::text))::integer)
35. 91.061 304.101 ↑ 1.0 35,534 1

Sort (cost=9,133.31..9,222.58 rows=35,706 width=525) (actual time=290.277..304.101 rows=35,534 loops=1)

  • Sort Key: r.sernr, r.comp_part, r.alt_code, ((COALESCE((rs.value ->> 'mat_auto_report_calc_round_to'::text), '2'::text))::integer)
  • Sort Method: external merge Disk: 28608kB
36. 208.883 213.040 ↑ 1.0 35,534 1

Nested Loop Left Join (cost=0.00..1,621.25 rows=35,706 width=525) (actual time=0.056..213.040 rows=35,534 loops=1)

37. 4.157 4.157 ↑ 1.0 35,534 1

Seq Scan on so_reportings r (cost=0.00..906.06 rows=35,706 width=23) (actual time=0.006..4.157 rows=35,534 loops=1)

38. 0.000 0.000 ↑ 1.0 1 35,534

Materialize (cost=0.00..1.07 rows=1 width=498) (actual time=0.000..0.000 rows=1 loops=35,534)

39. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on reg_settings rs (cost=0.00..1.06 rows=1 width=498) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (register = 'ProdOrder'::text)
  • Rows Removed by Filter: 4
40.          

CTE gr_so_rows

41. 33.599 36.592 ↓ 4.2 29,564 1

HashAggregate (cost=1,105.70..1,194.48 rows=7,102 width=50) (actual time=23.426..36.592 rows=29,564 loops=1)

  • Group Key: r_1.sernr, r_1.comp_part, r_1.alt_code
42. 2.993 2.993 ↓ 1.0 29,756 1

Seq Scan on so_rows r_1 (cost=0.00..808.35 rows=29,735 width=23) (actual time=0.004..2.993 rows=29,756 loops=1)

43.          

CTE st_total

44. 0.539 13.249 ↑ 1.1 6,211 1

Subquery Scan on so_receivings_total (cost=695.45..854.75 rows=7,080 width=36) (actual time=10.836..13.249 rows=6,211 loops=1)

45. 5.562 12.710 ↑ 1.1 6,211 1

HashAggregate (cost=695.45..783.95 rows=7,080 width=55) (actual time=10.835..12.710 rows=6,211 loops=1)

  • Group Key: h.sernr
46. 2.551 7.148 ↓ 1.0 8,192 1

Hash Join (cost=390.30..654.56 rows=8,178 width=26) (actual time=3.855..7.148 rows=8,192 loops=1)

  • Hash Cond: (rec.sernr = h.sernr)
47. 0.790 0.790 ↓ 1.0 8,192 1

Seq Scan on so_receivings rec (cost=0.00..242.78 rows=8,178 width=8) (actual time=0.003..0.790 rows=8,192 loops=1)

48. 1.264 3.807 ↓ 1.0 7,108 1

Hash (cost=301.80..301.80 rows=7,080 width=22) (actual time=3.807..3.807 rows=7,108 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 441kB
49. 2.543 2.543 ↓ 1.0 7,108 1

Seq Scan on so_header h (cost=0.00..301.80 rows=7,080 width=22) (actual time=0.002..2.543 rows=7,108 loops=1)

50.          

CTE to_deliver

51. 0.206 31.769 ↓ 2.9 1,157 1

Subquery Scan on co_rows_to_deliver (cost=6,209.15..6,281.05 rows=400 width=36) (actual time=30.083..31.769 rows=1,157 loops=1)

52. 1.410 31.563 ↓ 2.9 1,157 1

GroupAggregate (cost=6,209.15..6,276.05 rows=400 width=361) (actual time=30.081..31.563 rows=1,157 loops=1)

  • Group Key: rows.sernr, rows.rowno, rows.artcode, rows.artname, rows.batch, rows.alt_code, rows.alt_desc, rows.unit, rows.unit2, rows.qty, rows.qty2, rows.unitkoef, rows.warehouse, rows.location, rows.unit_primary, rows.id, rows.co_sernr, rows.parent_rowno
53. 2.148 30.153 ↓ 1.3 1,485 1

Sort (cost=6,209.15..6,212.00 rows=1,141 width=297) (actual time=30.070..30.153 rows=1,485 loops=1)

  • Sort Key: rows.sernr, rows.rowno, rows.artcode, rows.artname, rows.batch, rows.alt_code, rows.alt_desc, rows.unit, rows.unit2, rows.qty, rows.qty2, rows.unitkoef, rows.warehouse, rows.location, rows.unit_primary, rows.id, rows.co_sernr, rows.parent_rowno
  • Sort Method: quicksort Memory: 386kB
54. 0.360 28.005 ↓ 1.3 1,485 1

Subquery Scan on rows (cost=6,136.95..6,151.21 rows=1,141 width=297) (actual time=27.564..28.005 rows=1,485 loops=1)

55. 0.987 27.645 ↓ 1.3 1,485 1

Sort (cost=6,136.95..6,139.80 rows=1,141 width=382) (actual time=27.562..27.645 rows=1,485 loops=1)

  • Sort Key: r_2.sernr, r_2.rowno
  • Sort Method: quicksort Memory: 426kB
56. 2.905 26.658 ↓ 1.3 1,485 1

HashAggregate (cost=6,061.89..6,079.01 rows=1,141 width=382) (actual time=25.705..26.658 rows=1,485 loops=1)

  • Group Key: str.alt_desc, d.to_deliver_qty, dh.confirmed, dh.sent_to_collection, r_2.id, h_1.sernr, cor.rowno
57. 9.060 23.753 ↓ 1.3 1,485 1

Nested Loop (cost=624.98..6,024.81 rows=1,141 width=290) (actual time=5.529..23.753 rows=1,485 loops=1)

  • Join Filter: CASE WHEN ((rs_1.value ->> 'co_rows_view'::text) = '1'::text) THEN CASE WHEN (r_2.parent IS NOT NULL) THEN (r_2.parent IS NOT NULL) WHEN ((SubPlan 15) = 0) THEN true ELSE (cor.id IS NOT NULL) END ELSE (r_2.rowno > 0) END
  • Rows Removed by Join Filter: 1149
58. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on reg_settings rs_1 (cost=0.00..1.06 rows=1 width=498) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: (register = 'Invoice'::text)
  • Rows Removed by Filter: 4
59. 0.744 11.231 ↓ 1.2 2,634 1

Hash Left Join (cost=624.98..706.69 rows=2,282 width=298) (actual time=5.506..11.231 rows=2,634 loops=1)

  • Hash Cond: (r_2.parent = cor.id)
60. 1.160 9.721 ↓ 1.2 2,634 1

Hash Left Join (cost=490.88..566.60 rows=2,282 width=290) (actual time=4.720..9.721 rows=2,634 loops=1)

  • Hash Cond: (r_2.artcode = str.part_no)
  • Join Filter: (NOT (str.code IS DISTINCT FROM r_2.alt_code))
61. 0.706 7.996 ↓ 1.2 2,634 1

Hash Left Join (cost=417.92..456.56 rows=2,282 width=267) (actual time=4.147..7.996 rows=2,634 loops=1)

  • Hash Cond: (d.sernr = dh.sernr)
62. 1.050 7.059 ↓ 1.2 2,634 1

Hash Left Join (cost=383.73..416.33 rows=2,282 width=255) (actual time=3.907..7.059 rows=2,634 loops=1)

  • Hash Cond: (r_2.artcode = a_1.code)
63. 0.762 5.150 ↓ 1.2 2,634 1

Hash Join (cost=267.96..294.56 rows=2,282 width=251) (actual time=3.029..5.150 rows=2,634 loops=1)

  • Hash Cond: (r_2.sernr = h_1.sernr)
64. 1.024 4.241 ↓ 1.1 2,634 1

Hash Right Join (cost=244.93..265.44 rows=2,295 width=247) (actual time=2.870..4.241 rows=2,634 loops=1)

  • Hash Cond: (d.co_rows_id = r_2.id)
65. 0.380 1.479 ↓ 1.0 2,567 1

Hash Right Join (cost=115.53..129.32 rows=2,557 width=45) (actual time=1.120..1.479 rows=2,567 loops=1)

  • Hash Cond: (c.del_row_id = d.id)
66. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on collection_rows c (cost=0.00..13.00 rows=300 width=36) (actual time=0.001..0.001 rows=0 loops=1)

67. 0.414 1.098 ↓ 1.0 2,567 1

Hash (cost=83.57..83.57 rows=2,557 width=17) (actual time=1.098..1.098 rows=2,567 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 159kB
68. 0.684 0.684 ↓ 1.0 2,567 1

Seq Scan on del_rows d (cost=0.00..83.57 rows=2,557 width=17) (actual time=0.004..0.684 rows=2,567 loops=1)

69. 0.758 1.738 ↓ 1.0 2,306 1

Hash (cost=100.71..100.71 rows=2,295 width=206) (actual time=1.738..1.738 rows=2,306 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 286kB
70. 0.980 0.980 ↓ 1.0 2,306 1

Seq Scan on co_rows r_2 (cost=0.00..100.71 rows=2,295 width=206) (actual time=0.004..0.980 rows=2,306 loops=1)

  • Filter: (closed IS NULL)
  • Rows Removed by Filter: 380
71. 0.042 0.147 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.147..0.147 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
72. 0.105 0.105 ↑ 1.0 350 1

Seq Scan on co_header h_1 (cost=0.00..18.58 rows=356 width=4) (actual time=0.002..0.105 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
73. 0.430 0.859 ↑ 1.0 2,212 1

Hash (cost=88.12..88.12 rows=2,212 width=19) (actual time=0.859..0.859 rows=2,212 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 145kB
74. 0.429 0.429 ↑ 1.0 2,212 1

Seq Scan on articles a_1 (cost=0.00..88.12 rows=2,212 width=19) (actual time=0.002..0.429 rows=2,212 loops=1)

75. 0.078 0.231 ↑ 1.0 539 1

Hash (cost=27.42..27.42 rows=542 width=20) (actual time=0.231..0.231 rows=539 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
76. 0.153 0.153 ↑ 1.0 539 1

Seq Scan on del_header dh (cost=0.00..27.42 rows=542 width=20) (actual time=0.003..0.153 rows=539 loops=1)

77. 0.342 0.565 ↑ 1.0 1,509 1

Hash (cost=54.09..54.09 rows=1,509 width=41) (actual time=0.565..0.565 rows=1,509 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 126kB
78. 0.223 0.223 ↑ 1.0 1,509 1

Seq Scan on structure_alt str (cost=0.00..54.09 rows=1,509 width=41) (actual time=0.002..0.223 rows=1,509 loops=1)

79. 0.392 0.766 ↓ 1.0 2,686 1

Hash (cost=100.71..100.71 rows=2,671 width=8) (actual time=0.766..0.766 rows=2,686 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
80. 0.374 0.374 ↓ 1.0 2,686 1

Seq Scan on co_rows cor (cost=0.00..100.71 rows=2,671 width=8) (actual time=0.005..0.374 rows=2,686 loops=1)

81.          

SubPlan (for Nested Loop)

82. 1.152 3.456 ↑ 1.0 1 1,152

Aggregate (cost=2.30..2.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,152)

83. 2.304 2.304 ↑ 1.0 1 1,152

Index Scan using co_rows_parent_idx on co_rows cr (cost=0.28..2.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,152)

  • Index Cond: (parent = r_2.id)
84.          

CTE mt

85. 0.369 464.029 ↓ 2.1 428 1

GroupAggregate (cost=9,199.44..9,269.87 rows=200 width=61) (actual time=462.273..464.029 rows=428 loops=1)

  • Group Key: r_3.artcode, r_3.alt_code
86. 0.348 463.660 ↑ 1.1 792 1

Merge Append (cost=9,199.44..9,260.78 rows=878 width=61) (actual time=462.261..463.660 rows=792 loops=1)

  • Sort Key: r_3.artcode, r_3.alt_code
87. 0.001 1.058 ↓ 0.0 0 1

GroupAggregate (cost=6,244.45..6,250.99 rows=235 width=53) (actual time=1.058..1.058 rows=0 loops=1)

  • Group Key: r_3.artcode, r_3.alt_code
88. 0.005 1.057 ↓ 0.0 0 1

Sort (cost=6,244.45..6,245.20 rows=301 width=31) (actual time=1.057..1.057 rows=0 loops=1)

  • Sort Key: r_3.artcode, r_3.alt_code
  • Sort Method: quicksort Memory: 25kB
89. 0.008 1.052 ↓ 0.0 0 1

Hash Join (cost=6,168.01..6,232.06 rows=301 width=31) (actual time=1.052..1.052 rows=0 loops=1)

  • Hash Cond: (r_3.sernr = h_2.sernr)
90. 0.010 0.861 ↓ 0.0 0 1

Hash Right Join (cost=6,144.98..6,208.22 rows=303 width=35) (actual time=0.861..0.861 rows=0 loops=1)

  • Hash Cond: (rows_1.id = r_3.id)
91. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=6,064.56..6,122.76 rows=400 width=361) (never executed)

  • Group Key: rows_1.sernr, rows_1.rowno, rows_1.artcode, rows_1.artname, rows_1.batch, rows_1.alt_code, rows_1.alt_desc, rows_1.unit, rows_1.unit2, rows_1.qty, rows_1.qty2, rows_1.unitkoef, rows_1.warehouse, rows_1.location, rows_1.unit_primary, rows_1.id, rows_1.co_sernr, rows_1.parent_rowno
92. 0.000 0.000 ↓ 0.0 0

Sort (cost=6,064.56..6,067.41 rows=1,141 width=233) (never executed)

  • Sort Key: rows_1.sernr, rows_1.rowno, rows_1.artcode, rows_1.artname, rows_1.batch, rows_1.alt_code, rows_1.alt_desc, rows_1.unit, rows_1.unit2, rows_1.qty, rows_1.qty2, rows_1.unitkoef, rows_1.warehouse, rows_1.location, rows_1.unit_primary, rows_1.id, rows_1.co_sernr, rows_1.parent_rowno
93. 0.000 0.000 ↓ 0.0 0

Subquery Scan on rows_1 (cost=5,992.35..6,006.62 rows=1,141 width=233) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,992.35..5,995.21 rows=1,141 width=382) (never executed)

  • Sort Key: r_4.sernr, r_4.rowno
95. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,923.00..5,934.41 rows=1,141 width=382) (never executed)

  • Group Key: str_1.alt_desc, d_1.to_deliver_qty, dh_1.confirmed, dh_1.sent_to_collection, r_4.id, h_3.sernr, cor_1.rowno
96. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=509.20..5,903.04 rows=1,141 width=254) (never executed)

  • Join Filter: CASE WHEN ((rs_2.value ->> 'co_rows_view'::text) = '1'::text) THEN CASE WHEN (r_4.parent IS NOT NULL) THEN (r_4.parent IS NOT NULL) WHEN ((SubPlan 17) = 0) THEN true ELSE (cor_1.id IS NOT NULL) END ELSE (r_4.rowno > 0) END
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on reg_settings rs_2 (cost=0.00..1.06 rows=1 width=498) (never executed)

  • Filter: (register = 'Invoice'::text)
98. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=509.20..584.91 rows=2,282 width=262) (never executed)

  • Hash Cond: (r_4.parent = cor_1.id)
99. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=375.11..444.83 rows=2,282 width=254) (never executed)

  • Hash Cond: (r_4.artcode = str_1.part_no)
  • Join Filter: (NOT (str_1.code IS DISTINCT FROM r_4.alt_code))
100. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=302.15..334.79 rows=2,282 width=231) (never executed)

  • Hash Cond: (d_1.sernr = dh_1.sernr)
101. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=267.96..294.56 rows=2,282 width=219) (never executed)

  • Hash Cond: (r_4.sernr = h_3.sernr)
102. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=244.93..265.44 rows=2,295 width=215) (never executed)

  • Hash Cond: (d_1.co_rows_id = r_4.id)
103. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=115.53..129.32 rows=2,557 width=13) (never executed)

  • Hash Cond: (c_1.del_row_id = d_1.id)
104. 0.000 0.000 ↓ 0.0 0

Seq Scan on collection_rows c_1 (cost=0.00..13.00 rows=300 width=4) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Hash (cost=83.57..83.57 rows=2,557 width=17) (never executed)

106. 0.000 0.000 ↓ 0.0 0

Seq Scan on del_rows d_1 (cost=0.00..83.57 rows=2,557 width=17) (never executed)

107. 0.000 0.000 ↓ 0.0 0

Hash (cost=100.71..100.71 rows=2,295 width=206) (never executed)

108. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_rows r_4 (cost=0.00..100.71 rows=2,295 width=206) (never executed)

  • Filter: (closed IS NULL)
109. 0.000 0.000 ↓ 0.0 0

Hash (cost=18.58..18.58 rows=356 width=4) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_header h_3 (cost=0.00..18.58 rows=356 width=4) (never executed)

  • Filter: (confirmed IS NOT NULL)
111. 0.000 0.000 ↓ 0.0 0

Hash (cost=27.42..27.42 rows=542 width=20) (never executed)

112. 0.000 0.000 ↓ 0.0 0

Seq Scan on del_header dh_1 (cost=0.00..27.42 rows=542 width=20) (never executed)

113. 0.000 0.000 ↓ 0.0 0

Hash (cost=54.09..54.09 rows=1,509 width=41) (never executed)

114. 0.000 0.000 ↓ 0.0 0

Seq Scan on structure_alt str_1 (cost=0.00..54.09 rows=1,509 width=41) (never executed)

115. 0.000 0.000 ↓ 0.0 0

Hash (cost=100.71..100.71 rows=2,671 width=8) (never executed)

116. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_rows cor_1 (cost=0.00..100.71 rows=2,671 width=8) (never executed)

117.          

SubPlan (for Nested Loop)

118. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.30..2.31 rows=1 width=8) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Index Scan using co_rows_parent_idx on co_rows cr_1 (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Index Cond: (parent = r_4.id)
120. 0.000 0.851 ↓ 0.0 0 1

Hash (cost=76.63..76.63 rows=303 width=34) (actual time=0.851..0.851 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
121. 0.851 0.851 ↓ 0.0 0 1

Index Scan using co_rows_parent_idx on co_rows r_3 (cost=0.28..76.63 rows=303 width=34) (actual time=0.851..0.851 rows=0 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((closed IS NULL) AND (alt_code IS NOT NULL) AND (so_ref IS NULL) AND (batch IS NULL))
  • Rows Removed by Filter: 1333
122. 0.049 0.183 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.183..0.183 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
123. 0.134 0.134 ↑ 1.0 350 1

Seq Scan on co_header h_2 (cost=0.00..18.58 rows=356 width=4) (actual time=0.006..0.134 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
124. 0.017 50.189 ↑ 1.5 184 1

Subquery Scan on "*SELECT* 2" (cost=526.02..540.88 rows=283 width=49) (actual time=49.985..50.189 rows=184 loops=1)

125. 0.181 50.172 ↑ 1.5 184 1

GroupAggregate (cost=526.02..538.05 rows=283 width=102) (actual time=49.984..50.172 rows=184 loops=1)

  • Group Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
126. 0.384 49.991 ↑ 1.5 185 1

Sort (cost=526.02..526.73 rows=283 width=107) (actual time=49.973..49.991 rows=185 loops=1)

  • Sort Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
  • Sort Method: quicksort Memory: 47kB
127. 0.599 49.607 ↑ 1.5 185 1

Hash Right Join (cost=343.51..514.49 rows=283 width=107) (actual time=45.149..49.607 rows=185 loops=1)

  • Hash Cond: (st.sernr = sh.sernr)
128. 14.747 14.747 ↑ 1.1 6,211 1

CTE Scan on st_total st (cost=0.00..141.60 rows=7,080 width=36) (actual time=10.838..14.747 rows=6,211 loops=1)

129. 0.060 34.261 ↑ 1.5 185 1

Hash (cost=339.98..339.98 rows=283 width=79) (actual time=34.261..34.261 rows=185 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
130. 0.163 34.201 ↑ 1.5 185 1

Nested Loop (cost=107.96..339.98 rows=283 width=79) (actual time=33.444..34.201 rows=185 loops=1)

131. 0.045 33.668 ↑ 1.7 185 1

Hash Join (cost=107.67..117.57 rows=318 width=53) (actual time=33.425..33.668 rows=185 loops=1)

  • Hash Cond: (r_5.sernr = h_4.sernr)
132. 0.363 33.476 ↑ 1.7 185 1

Hash Right Join (cost=84.64..93.69 rows=320 width=57) (actual time=33.272..33.476 rows=185 loops=1)

  • Hash Cond: (t.id = r_5.id)
  • Filter: CASE WHEN (r_5.closed IS NOT NULL) THEN true ELSE (t.qty > '0'::numeric) END
  • Rows Removed by Filter: 1110
133. 32.046 32.046 ↓ 2.9 1,157 1

CTE Scan on to_deliver t (cost=0.00..8.00 rows=400 width=36) (actual time=30.085..32.046 rows=1,157 loops=1)

134. 0.234 1.067 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=29) (actual time=1.066..1.067 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
135. 0.833 0.833 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_5 (cost=0.28..76.63 rows=641 width=29) (actual time=0.034..0.833 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
136. 0.043 0.147 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.147..0.147 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
137. 0.104 0.104 ↑ 1.0 350 1

Seq Scan on co_header h_4 (cost=0.00..18.58 rows=356 width=4) (actual time=0.003..0.104 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
138. 0.370 0.370 ↑ 1.0 1 185

Index Scan using so_header_pkey on so_header sh (cost=0.28..0.70 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=185)

  • Index Cond: (sernr = r_5.so_ref)
  • Filter: ((planned IS NOT NULL) AND (cancelled IS NULL))
139. 0.047 393.441 ↓ 6.7 497 1

Subquery Scan on "*SELECT* 3" (cost=842.63..853.55 rows=74 width=96) (actual time=392.653..393.441 rows=497 loops=1)

140. 0.696 393.394 ↓ 6.7 497 1

GroupAggregate (cost=842.63..852.81 rows=74 width=128) (actual time=392.652..393.394 rows=497 loops=1)

  • Group Key: sr.comp_part, sr.alt_code, st_1.qty
141. 1.343 392.698 ↓ 9.4 696 1

Sort (cost=842.63..842.82 rows=74 width=128) (actual time=392.642..392.698 rows=696 loops=1)

  • Sort Key: sr.comp_part, sr.alt_code, st_1.qty
  • Sort Method: quicksort Memory: 76kB
142. 0.583 391.355 ↓ 9.4 696 1

Nested Loop (cost=562.50..840.34 rows=74 width=128) (actual time=387.006..391.355 rows=696 loops=1)

143. 4.206 390.076 ↓ 9.4 696 1

Hash Right Join (cost=562.36..826.16 rows=74 width=132) (actual time=386.973..390.076 rows=696 loops=1)

  • Hash Cond: ((st_1.sernr = sr.sernr) AND (st_1.comp_part = sr.comp_part))
  • Join Filter: (NOT (st_1.alt_code IS DISTINCT FROM sr.alt_code))
144. 333.862 333.862 ↓ 3.8 23,872 1

CTE Scan on so_rep st_1 (cost=0.00..124.08 rows=6,204 width=100) (actual time=290.301..333.862 rows=23,872 loops=1)

145. 0.219 52.008 ↓ 9.4 696 1

Hash (cost=561.25..561.25 rows=74 width=104) (actual time=52.008..52.008 rows=696 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
146. 2.695 51.789 ↓ 9.4 696 1

Hash Join (cost=391.83..561.25 rows=74 width=104) (actual time=26.541..51.789 rows=696 loops=1)

  • Hash Cond: (sr.sernr = sh_1.sernr)
147. 46.007 46.007 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows sr (cost=0.00..142.04 rows=7,102 width=100) (actual time=23.428..46.007 rows=29,564 loops=1)

148. 0.011 3.087 ↑ 1.7 43 1

Hash (cost=390.91..390.91 rows=74 width=16) (actual time=3.087..3.087 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
149. 0.032 3.076 ↑ 1.7 43 1

Hash Join (cost=84.64..390.91 rows=74 width=16) (actual time=2.182..3.076 rows=43 loops=1)

  • Hash Cond: (sh_1.sernr = r_6.so_ref)
150. 1.918 1.918 ↑ 5.5 148 1

Seq Scan on so_header sh_1 (cost=0.00..301.80 rows=821 width=4) (actual time=0.095..1.918 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
151. 0.173 1.126 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=12) (actual time=1.126..1.126 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
152. 0.953 0.953 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_6 (cost=0.28..76.63 rows=641 width=12) (actual time=0.043..0.953 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
153. 0.696 0.696 ↑ 1.0 1 696

Index Scan using co_header_pkey on co_header h_5 (cost=0.15..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=696)

  • Index Cond: (sernr = r_6.sernr)
  • Filter: (confirmed IS NOT NULL)
154. 0.106 4.685 ↑ 2.0 71 1

Sort (cost=615.93..616.29 rows=143 width=49) (actual time=4.680..4.685 rows=71 loops=1)

  • Sort Key: ""*SELECT* 4"".part_no, ""*SELECT* 4"".alt_code
  • Sort Method: quicksort Memory: 30kB
155. 0.009 4.579 ↑ 2.0 71 1

Subquery Scan on "*SELECT* 4" (cost=607.60..610.81 rows=143 width=49) (actual time=4.540..4.579 rows=71 loops=1)

156. 0.034 4.570 ↑ 2.0 71 1

Group (cost=607.60..609.38 rows=143 width=90) (actual time=4.539..4.570 rows=71 loops=1)

  • Group Key: st_2.received, sh_2.sernr
157. 0.042 4.536 ↑ 2.0 71 1

Sort (cost=607.60..607.95 rows=143 width=58) (actual time=4.532..4.536 rows=71 loops=1)

  • Sort Key: st_2.received, sh_2.sernr
  • Sort Method: quicksort Memory: 30kB
158. 0.062 4.494 ↑ 2.0 71 1

Nested Loop (cost=405.46..602.48 rows=143 width=58) (actual time=3.456..4.494 rows=71 loops=1)

159. 0.493 4.361 ↑ 2.0 71 1

Hash Right Join (cost=405.31..574.90 rows=144 width=62) (actual time=3.447..4.361 rows=71 loops=1)

  • Hash Cond: (st_2.sernr = sh_2.sernr)
160. 0.441 0.441 ↑ 1.1 6,211 1

CTE Scan on st_total st_2 (cost=0.00..141.60 rows=7,080 width=36) (actual time=0.001..0.441 rows=6,211 loops=1)

161. 0.018 3.427 ↑ 2.0 71 1

Hash (cost=403.51..403.51 rows=144 width=30) (actual time=3.427..3.427 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
162. 0.123 3.409 ↑ 2.0 71 1

Hash Right Join (cost=393.79..403.51 rows=144 width=30) (actual time=3.328..3.409 rows=71 loops=1)

  • Hash Cond: (t_1.id = r_7.id)
163. 0.094 0.094 ↓ 2.9 1,157 1

CTE Scan on to_deliver t_1 (cost=0.00..8.00 rows=400 width=4) (actual time=0.001..0.094 rows=1,157 loops=1)

164. 0.022 3.192 ↑ 2.0 71 1

Hash (cost=391.99..391.99 rows=144 width=34) (actual time=3.192..3.192 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
165. 0.038 3.170 ↑ 2.0 71 1

Hash Join (cost=84.64..391.99 rows=144 width=34) (actual time=1.566..3.170 rows=71 loops=1)

  • Hash Cond: (sh_2.master_sernr = r_7.so_ref)
166. 1.966 1.966 ↑ 5.5 148 1

Seq Scan on so_header sh_2 (cost=0.00..301.80 rows=821 width=30) (actual time=0.105..1.966 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
167. 0.204 1.166 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=16) (actual time=1.166..1.166 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
168. 0.962 0.962 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_7 (cost=0.28..76.63 rows=641 width=16) (actual time=0.037..0.962 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
169. 0.071 0.071 ↑ 1.0 1 71

Index Scan using co_header_pkey on co_header h_6 (cost=0.15..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=71)

  • Index Cond: (sernr = r_7.sernr)
  • Filter: (confirmed IS NOT NULL)
170. 0.005 13.939 ↑ 3.6 40 1

Subquery Scan on "*SELECT* 5" (cost=970.34..984.29 rows=143 width=96) (actual time=13.879..13.939 rows=40 loops=1)

171. 0.064 13.934 ↑ 3.6 40 1

GroupAggregate (cost=970.34..982.86 rows=143 width=128) (actual time=13.878..13.934 rows=40 loops=1)

  • Group Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
172. 0.109 13.870 ↑ 1.5 94 1

Sort (cost=970.34..970.70 rows=143 width=128) (actual time=13.864..13.870 rows=94 loops=1)

  • Sort Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
  • Sort Method: quicksort Memory: 30kB
173. 0.001 13.761 ↑ 1.5 94 1

Nested Loop (cost=565.28..965.22 rows=143 width=128) (actual time=13.418..13.761 rows=94 loops=1)

174. 3.364 13.572 ↑ 1.5 94 1

Hash Right Join (cost=565.13..937.64 rows=144 width=132) (actual time=13.395..13.572 rows=94 loops=1)

  • Hash Cond: ((st_3.sernr = sr_1.sernr) AND (st_3.comp_part = sr_1.comp_part))
  • Join Filter: (NOT (st_3.alt_code IS DISTINCT FROM sr_1.alt_code))
175. 2.305 2.305 ↓ 3.8 23,872 1

CTE Scan on so_rep st_3 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.305 rows=23,872 loops=1)

176. 0.027 7.903 ↑ 1.5 94 1

Hash (cost=562.97..562.97 rows=144 width=104) (actual time=7.903..7.903 rows=94 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
177. 0.090 7.876 ↑ 1.5 94 1

Hash Join (cost=396.71..562.97 rows=144 width=104) (actual time=2.940..7.876 rows=94 loops=1)

  • Hash Cond: (sh_3.master_sernr = r_8.so_ref)
178. 2.591 6.751 ↓ 1.3 1,061 1

Hash Join (cost=312.06..472.76 rows=824 width=104) (actual time=1.692..6.751 rows=1,061 loops=1)

  • Hash Cond: (sr_1.sernr = sh_3.sernr)
179. 2.484 2.484 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows sr_1 (cost=0.00..142.04 rows=7,102 width=100) (actual time=0.003..2.484 rows=29,564 loops=1)

180. 0.022 1.676 ↑ 5.5 148 1

Hash (cost=301.80..301.80 rows=821 width=8) (actual time=1.676..1.676 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
181. 1.654 1.654 ↑ 5.5 148 1

Seq Scan on so_header sh_3 (cost=0.00..301.80 rows=821 width=8) (actual time=0.075..1.654 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
182. 0.186 1.035 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=12) (actual time=1.035..1.035 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
183. 0.849 0.849 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_8 (cost=0.28..76.63 rows=641 width=12) (actual time=0.039..0.849 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
184. 0.188 0.188 ↑ 1.0 1 94

Index Scan using co_header_pkey on co_header h_7 (cost=0.15..0.19 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=94)

  • Index Cond: (sernr = r_8.sernr)
  • Filter: (confirmed IS NOT NULL)
185.          

CTE mt2

186. 0.055 3.556 ↓ 57.0 57 1

GroupAggregate (cost=212.04..212.07 rows=1 width=53) (actual time=3.504..3.556 rows=57 loops=1)

  • Group Key: r_9.artcode, r_9.alt_code
187. 0.120 3.501 ↓ 83.0 83 1

Sort (cost=212.04..212.05 rows=1 width=58) (actual time=3.497..3.501 rows=83 loops=1)

  • Sort Key: r_9.artcode, r_9.alt_code
  • Sort Method: quicksort Memory: 31kB
188. 0.089 3.381 ↓ 83.0 83 1

Nested Loop (cost=202.49..212.03 rows=1 width=58) (actual time=3.122..3.381 rows=83 loops=1)

189. 0.114 3.201 ↓ 91.0 91 1

Hash Right Join (cost=202.34..211.85 rows=1 width=62) (actual time=3.107..3.201 rows=91 loops=1)

  • Hash Cond: (t_2.id = r_9.id)
190. 0.085 0.085 ↓ 2.9 1,157 1

CTE Scan on to_deliver t_2 (cost=0.00..8.00 rows=400 width=36) (actual time=0.001..0.085 rows=1,157 loops=1)

191. 0.024 3.002 ↓ 91.0 91 1

Hash (cost=202.33..202.33 rows=1 width=34) (actual time=3.002..3.002 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
192. 0.804 2.978 ↓ 91.0 91 1

Merge Left Join (cost=0.56..202.33 rows=1 width=34) (actual time=0.281..2.978 rows=91 loops=1)

  • Merge Cond: (r_9.id = r2.parent)
  • Filter: (r2.id IS NULL)
  • Rows Removed by Filter: 1333
193. 1.531 1.531 ↓ 1.0 1,390 1

Index Scan using co_rows_pkey on co_rows r_9 (cost=0.28..125.34 rows=1,380 width=34) (actual time=0.054..1.531 rows=1,390 loops=1)

  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NULL))
  • Rows Removed by Filter: 1296
194. 0.643 0.643 ↑ 2.0 1,334 1

Index Scan using co_rows_parent_idx on co_rows r2 (cost=0.28..127.34 rows=2,671 width=8) (actual time=0.043..0.643 rows=1,334 loops=1)

195. 0.091 0.091 ↑ 1.0 1 91

Index Scan using co_header_pkey on co_header h_8 (cost=0.15..0.18 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=91)

  • Index Cond: (sernr = r_9.sernr)
  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 0
196.          

CTE tt

197. 0.026 321.714 ↑ 1.9 301 1

Append (cost=4,735.24..12,957.57 rows=567 width=66) (actual time=220.868..321.714 rows=301 loops=1)

198. 0.004 220.905 ↑ 10.7 34 1

Subquery Scan on "*SELECT* 1_1" (cost=4,735.24..4,752.58 rows=365 width=49) (actual time=220.867..220.905 rows=34 loops=1)

199. 0.045 220.901 ↑ 10.7 34 1

GroupAggregate (cost=4,735.24..4,748.93 rows=365 width=81) (actual time=220.866..220.901 rows=34 loops=1)

  • Group Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
200. 0.060 220.856 ↑ 10.7 34 1

Sort (cost=4,735.24..4,736.15 rows=365 width=86) (actual time=220.853..220.856 rows=34 loops=1)

  • Sort Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
  • Sort Method: quicksort Memory: 27kB
201. 0.453 220.796 ↑ 10.7 34 1

Hash Right Join (cost=4,541.81..4,719.71 rows=365 width=86) (actual time=218.866..220.796 rows=34 loops=1)

  • Hash Cond: (h_9.sernr = sh_5.sernr)
202. 7.216 19.701 ↑ 1.1 6,211 1

HashAggregate (cost=695.45..783.95 rows=7,080 width=55) (actual time=17.416..19.701 rows=6,211 loops=1)

  • Group Key: h_9.sernr
203. 4.116 12.485 ↓ 1.0 8,192 1

Hash Join (cost=390.30..654.56 rows=8,178 width=26) (actual time=7.184..12.485 rows=8,192 loops=1)

  • Hash Cond: (rec_1.sernr = h_9.sernr)
204. 1.216 1.216 ↓ 1.0 8,192 1

Seq Scan on so_receivings rec_1 (cost=0.00..242.78 rows=8,178 width=8) (actual time=0.004..1.216 rows=8,192 loops=1)

205. 2.295 7.153 ↓ 1.0 7,108 1

Hash (cost=301.80..301.80 rows=7,080 width=22) (actual time=7.153..7.153 rows=7,108 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 441kB
206. 4.858 4.858 ↓ 1.0 7,108 1

Seq Scan on so_header h_9 (cost=0.00..301.80 rows=7,080 width=22) (actual time=0.004..4.858 rows=7,108 loops=1)

207. 0.042 200.642 ↑ 10.7 34 1

Hash (cost=3,841.80..3,841.80 rows=365 width=26) (actual time=200.642..200.642 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
208. 200.600 200.600 ↑ 10.7 34 1

Seq Scan on so_header sh_5 (cost=0.00..3,841.80 rows=365 width=26) (actual time=1.396..200.600 rows=34 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (NOT so_has_co_ref(sernr)) AND (NOT so_has_co_ref(master_sernr)))
  • Rows Removed by Filter: 7074
209. 0.021 51.682 ↓ 1.8 239 1

Subquery Scan on "*SELECT* 2_2" (cost=2,613.56..2,627.16 rows=136 width=96) (actual time=51.453..51.682 rows=239 loops=1)

210. 0.208 51.661 ↓ 1.8 239 1

GroupAggregate (cost=2,613.56..2,625.80 rows=136 width=132) (actual time=51.452..51.661 rows=239 loops=1)

  • Group Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
211. 0.397 51.453 ↓ 1.8 239 1

Sort (cost=2,613.56..2,613.90 rows=136 width=132) (actual time=51.440..51.453 rows=239 loops=1)

  • Sort Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
  • Sort Method: quicksort Memory: 43kB
212. 3.120 51.056 ↓ 1.8 239 1

Hash Right Join (cost=2,236.23..2,608.74 rows=136 width=132) (actual time=50.774..51.056 rows=239 loops=1)

  • Hash Cond: ((st_5.sernr = r_11.sernr) AND (st_5.comp_part = r_11.comp_part))
  • Join Filter: (NOT (st_5.alt_code IS DISTINCT FROM r_11.alt_code))
213. 2.148 2.148 ↓ 3.8 23,872 1

CTE Scan on so_rep st_5 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.148 rows=23,872 loops=1)

214. 0.071 45.788 ↓ 1.8 239 1

Hash (cost=2,234.19..2,234.19 rows=136 width=100) (actual time=45.788..45.788 rows=239 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
215. 2.553 45.717 ↓ 1.8 239 1

Hash Join (cost=2,073.50..2,234.19 rows=136 width=100) (actual time=40.607..45.717 rows=239 loops=1)

  • Hash Cond: (r_11.sernr = sh_6.sernr)
216. 2.580 2.580 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows r_11 (cost=0.00..142.04 rows=7,102 width=100) (actual time=0.001..2.580 rows=29,564 loops=1)

217. 0.012 40.584 ↑ 9.1 15 1

Hash (cost=2,071.80..2,071.80 rows=136 width=4) (actual time=40.584..40.584 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
218. 40.572 40.572 ↑ 9.1 15 1

Seq Scan on so_header sh_6 (cost=0.00..2,071.80 rows=136 width=4) (actual time=0.903..40.572 rows=15 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (master_sernr IS NULL) AND (NOT so_has_co_ref(sernr)))
  • Rows Removed by Filter: 7093
219. 0.068 49.101 ↑ 2.4 28 1

HashAggregate (cost=5,576.35..5,577.17 rows=66 width=96) (actual time=49.091..49.101 rows=28 loops=1)

  • Group Key: a_2.component_part, a_2.alt_code
220.          

CTE nodes_cte

221. 0.038 48.980 ↓ 1.7 109 1

Recursive Union (cost=2,962.83..5,574.53 rows=66 width=138) (actual time=48.483..48.980 rows=109 loops=1)

222. 0.016 48.502 ↓ 16.0 96 1

Subquery Scan on "*SELECT* 1" (cost=2,962.83..2,962.90 rows=6 width=138) (actual time=48.482..48.502 rows=96 loops=1)

223. 0.061 48.486 ↓ 16.0 96 1

Sort (cost=2,962.83..2,962.84 rows=6 width=142) (actual time=48.481..48.486 rows=96 loops=1)

  • Sort Key: r_10.rowno
  • Sort Method: quicksort Memory: 38kB
224.          

CTE base

225. 0.094 47.164 ↑ 1.9 72 1

GroupAggregate (cost=2,613.46..2,625.70 rows=136 width=132) (actual time=47.074..47.164 rows=72 loops=1)

  • Group Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
226. 0.129 47.070 ↑ 1.9 72 1

Sort (cost=2,613.46..2,613.80 rows=136 width=132) (actual time=47.064..47.070 rows=72 loops=1)

  • Sort Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
  • Sort Method: quicksort Memory: 30kB
227. 3.017 46.941 ↑ 1.9 72 1

Hash Right Join (cost=2,236.14..2,608.64 rows=136 width=132) (actual time=46.687..46.941 rows=72 loops=1)

  • Hash Cond: ((st_4.sernr = r_1_1.sernr) AND (st_4.comp_part = r_1_1.comp_part))
  • Join Filter: (NOT (st_4.alt_code IS DISTINCT FROM r_1_1.alt_code))
228. 2.132 2.132 ↓ 3.8 23,872 1

CTE Scan on so_rep st_4 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.132 rows=23,872 loops=1)

229. 0.030 41.792 ↑ 1.9 72 1

Hash (cost=2,234.10..2,234.10 rows=136 width=100) (actual time=41.791..41.792 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
230. 0.702 41.762 ↑ 1.9 72 1

Hash Join (cost=2,073.50..2,234.10 rows=136 width=100) (actual time=38.210..41.762 rows=72 loops=1)

  • Hash Cond: (r_1_1.sernr = sh_4.sernr)
231. 2.943 2.943 ↓ 1.4 9,891 1

CTE Scan on gr_so_rows r_1_1 (cost=0.00..142.04 rows=7,066 width=100) (actual time=0.002..2.943 rows=9,891 loops=1)

  • Filter: (alt_code IS NOT NULL)
  • Rows Removed by Filter: 19673
232. 0.010 38.117 ↑ 9.1 15 1

Hash (cost=2,071.80..2,071.80 rows=136 width=4) (actual time=38.117..38.117 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
233. 38.107 38.107 ↑ 9.1 15 1

Seq Scan on so_header sh_4 (cost=0.00..2,071.80 rows=136 width=4) (actual time=0.823..38.107 rows=15 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (master_sernr IS NULL) AND (NOT so_has_co_ref(sernr)))
  • Rows Removed by Filter: 7093
234. 0.093 48.425 ↓ 16.0 96 1

Nested Loop Left Join (cost=0.84..337.05 rows=6 width=142) (actual time=47.162..48.425 rows=96 loops=1)

235. 0.052 48.236 ↓ 16.0 96 1

Nested Loop Left Join (cost=0.56..335.17 rows=6 width=79) (actual time=47.143..48.236 rows=96 loops=1)

236. 0.069 47.704 ↓ 16.0 96 1

Nested Loop (cost=0.29..333.29 rows=6 width=75) (actual time=47.121..47.704 rows=96 loops=1)

237. 47.203 47.203 ↑ 1.9 72 1

CTE Scan on base (cost=0.00..2.72 rows=136 width=96) (actual time=47.077..47.203 rows=72 loops=1)

238. 0.432 0.432 ↑ 6.0 1 72

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows r_10 (cost=0.29..2.37 rows=6 width=43) (actual time=0.006..0.006 rows=1 loops=72)

  • Index Cond: (part_no = base.part_no)
  • Filter: (base.alt_code = alternate)
239. 0.480 0.480 ↑ 1.0 1 96

Index Scan using unique_structure_alternatine on structure_alt sap (cost=0.28..0.30 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=96)

  • Index Cond: (part_no = r_10.part_no)
  • Filter: (NOT (code IS DISTINCT FROM r_10.alternate))
240. 0.096 0.096 ↓ 0.0 0 96

Index Scan using unique_structure_alternatine on structure_alt sa_1 (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=96)

  • Index Cond: ((part_no = r_10.component_part) AND (code = r_10.alt_code))
241. 0.004 0.440 ↑ 1.0 6 2

Subquery Scan on "*SELECT* 2_1" (cost=261.02..261.10 rows=6 width=138) (actual time=0.219..0.220 rows=6 loops=2)

242. 0.018 0.436 ↑ 1.0 6 2

Sort (cost=261.02..261.04 rows=6 width=142) (actual time=0.218..0.218 rows=6 loops=2)

  • Sort Key: c_2.rowno
  • Sort Method: quicksort Memory: 25kB
243. 0.009 0.418 ↑ 1.0 6 2

Nested Loop Left Join (cost=0.56..260.94 rows=6 width=142) (actual time=0.039..0.209 rows=6 loops=2)

244. 0.059 0.396 ↑ 1.0 6 2

Nested Loop (cost=0.29..259.08 rows=6 width=83) (actual time=0.031..0.198 rows=6 loops=2)

245. 0.010 0.010 ↑ 1.1 54 2

WorkTable Scan on nodes_cte p (cost=0.00..1.20 rows=60 width=104) (actual time=0.001..0.005 rows=54 loops=2)

246. 0.327 0.327 ↓ 0.0 0 109

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows c_2 (cost=0.29..4.24 rows=6 width=43) (actual time=0.003..0.003 rows=0 loops=109)

  • Index Cond: (part_no = p.component_part)
  • Filter: (p.alt_code = alternate)
247. 0.013 0.013 ↓ 0.0 0 13

Index Scan using unique_structure_alternatine on structure_alt sa_2 (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=13)

  • Index Cond: ((part_no = c_2.component_part) AND (code = c_2.alt_code))
248. 49.033 49.033 ↓ 1.7 109 1

CTE Scan on nodes_cte a_2 (cost=0.00..1.32 rows=66 width=96) (actual time=48.485..49.033 rows=109 loops=1)

249.          

CTE il

250. 8.955 9.526 ↓ 2.5 716 1

HashAggregate (cost=53.08..131.38 rows=290 width=47) (actual time=1.167..9.526 rows=716 loops=1)

  • Group Key: t_3.artcode, t_3.alt_code
251. 0.398 0.571 ↓ 3.0 883 1

Hash Left Join (cost=1.29..50.90 rows=290 width=20) (actual time=0.027..0.571 rows=883 loops=1)

  • Hash Cond: (t_3.warehouse = w.code)
  • Filter: ((NOT COALESCE(w.is_sale_prohibited, false)) AND (NOT COALESCE(w.report_warehouse, false)))
  • Rows Removed by Filter: 275
252. 0.161 0.161 ↑ 1.0 1,158 1

Seq Scan on inv_live t_3 (cost=0.00..45.58 rows=1,158 width=24) (actual time=0.005..0.161 rows=1,158 loops=1)

253. 0.006 0.012 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=8) (actual time=0.012..0.012 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
254. 0.006 0.006 ↑ 1.0 13 1

Seq Scan on warehouses w (cost=0.00..1.13 rows=13 width=8) (actual time=0.002..0.006 rows=13 loops=1)

255.          

CTE l

256. 1.714 819.760 ↓ 1.1 2,346 1

Merge Left Join (cost=341.17..624.74 rows=2,183 width=228) (actual time=811.993..819.760 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = i.artcode)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM i.alt_code))
257. 0.903 806.182 ↓ 1.1 2,346 1

Merge Left Join (cost=323.51..540.77 rows=2,183 width=228) (actual time=800.188..806.182 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = p_1.artcode)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM p_1.alt_code))
258. 0.963 802.228 ↓ 1.1 2,346 1

Merge Left Join (cost=213.68..382.21 rows=2,183 width=164) (actual time=799.005..802.228 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = tt.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM tt.alt_code))
259. 0.474 478.991 ↑ 1.0 2,182 1

Merge Left Join (cost=176.40..231.18 rows=2,183 width=132) (actual time=476.752..478.991 rows=2,182 loops=1)

  • Merge Cond: (a_3.code = mt2.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM mt2.alt_code))
260. 1.477 474.886 ↑ 1.0 2,182 1

Merge Left Join (cost=176.37..225.49 rows=2,183 width=132) (actual time=473.132..474.886 rows=2,182 loops=1)

  • Merge Cond: (a_3.code = mt.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM mt.alt_code))
261. 6.357 8.841 ↑ 1.0 2,182 1

Sort (cost=164.73..170.19 rows=2,183 width=100) (actual time=8.588..8.841 rows=2,182 loops=1)

  • Sort Key: a_3.code
  • Sort Method: quicksort Memory: 250kB
262. 2.484 2.484 ↑ 1.0 2,182 1

CTE Scan on a a_3 (cost=0.00..43.66 rows=2,183 width=100) (actual time=0.983..2.484 rows=2,182 loops=1)

263. 0.355 464.568 ↓ 2.1 428 1

Sort (cost=11.64..12.14 rows=200 width=96) (actual time=464.537..464.568 rows=428 loops=1)

  • Sort Key: mt.part_no
  • Sort Method: quicksort Memory: 55kB
264. 464.213 464.213 ↓ 2.1 428 1

CTE Scan on mt (cost=0.00..4.00 rows=200 width=96) (actual time=462.276..464.213 rows=428 loops=1)

265. 0.013 3.631 ↓ 57.0 57 1

Materialize (cost=0.03..0.04 rows=1 width=64) (actual time=3.618..3.631 rows=57 loops=1)

266. 0.039 3.618 ↓ 57.0 57 1

Sort (cost=0.03..0.04 rows=1 width=64) (actual time=3.614..3.618 rows=57 loops=1)

  • Sort Key: mt2.part_no
  • Sort Method: quicksort Memory: 29kB
267. 3.579 3.579 ↓ 57.0 57 1

CTE Scan on mt2 (cost=0.00..0.02 rows=1 width=64) (actual time=3.506..3.579 rows=57 loops=1)

268. 0.442 322.274 ↑ 1.9 301 1

Sort (cost=37.27..38.69 rows=567 width=96) (actual time=322.249..322.274 rows=301 loops=1)

  • Sort Key: tt.part_no
  • Sort Method: quicksort Memory: 45kB
269. 321.832 321.832 ↑ 1.9 301 1

CTE Scan on tt (cost=0.00..11.34 rows=567 width=96) (actual time=220.871..321.832 rows=301 loops=1)

270. 0.061 3.051 ↓ 2.9 169 1

Materialize (cost=109.83..142.17 rows=58 width=78) (actual time=1.180..3.051 rows=169 loops=1)

271. 1.894 2.990 ↓ 1.4 83 1

GroupAggregate (cost=109.83..141.44 rows=58 width=142) (actual time=1.175..2.990 rows=83 loops=1)

  • Group Key: p_1.artcode, p_1.alt_code
272. 0.166 1.096 ↓ 1.6 94 1

Sort (cost=109.83..109.98 rows=58 width=34) (actual time=1.083..1.096 rows=94 loops=1)

  • Sort Key: p_1.artcode, p_1.alt_code
  • Sort Method: quicksort Memory: 32kB
273. 0.042 0.930 ↓ 1.6 94 1

Hash Join (cost=19.06..108.14 rows=58 width=34) (actual time=0.297..0.930 rows=94 loops=1)

  • Hash Cond: (p_1.sernr = h_10.sernr)
274. 0.760 0.760 ↑ 4.1 181 1

Seq Scan on po_rows p_1 (cost=0.00..87.10 rows=744 width=38) (actual time=0.048..0.760 rows=181 loops=1)

  • Filter: ((closed IS NULL) AND (qty > qty_delivered))
  • Rows Removed by Filter: 2467
275. 0.008 0.128 ↑ 1.0 36 1

Hash (cost=18.61..18.61 rows=36 width=4) (actual time=0.128..0.128 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
276. 0.120 0.120 ↑ 1.0 36 1

Seq Scan on po_header h_10 (cost=0.00..18.61 rows=36 width=4) (actual time=0.011..0.120 rows=36 loops=1)

  • Filter: ((confirmed IS NOT NULL) AND (delivered IS NULL))
  • Rows Removed by Filter: 435
277. 1.853 11.864 ↓ 3.0 874 1

Sort (cost=17.66..18.39 rows=290 width=96) (actual time=11.801..11.864 rows=874 loops=1)

  • Sort Key: i.artcode
  • Sort Method: quicksort Memory: 76kB
278. 10.011 10.011 ↓ 2.5 716 1

CTE Scan on il i (cost=0.00..5.80 rows=290 width=96) (actual time=1.169..10.011 rows=716 loops=1)

279.          

CTE e

280. 3.687 824.429 ↓ 10.0 2,182 1

HashAggregate (cost=92.78..97.68 rows=218 width=260) (actual time=822.538..824.429 rows=2,182 loops=1)

  • Group Key: l.part_no, l.alt_code, l.mt_qty, l.po_qty, l.inv_qty, l.minlevel, l.source
281. 820.742 820.742 ↓ 1.1 2,346 1

CTE Scan on l (cost=0.00..43.66 rows=2,183 width=228) (actual time=811.995..820.742 rows=2,346 loops=1)

282.          

CTE p2

283. 0.000 30.479 ↓ 0.0 0 1

Group (cost=6,885.50..6,889.07 rows=476 width=13) (actual time=30.479..30.479 rows=0 loops=1)

  • Group Key: r_12.component_part, r_12.alt_code
284.          

Initplan (for Group)

285. 0.000 0.000 ↓ 0.0 0

CTE Scan on _session_id (cost=0.00..0.02 rows=1 width=8) (never executed)

286. 0.034 0.034 ↑ 1.0 1 1

CTE Scan on _update_code (cost=0.00..0.02 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=1)

287. 0.006 30.478 ↓ 0.0 0 1

Sort (cost=6,885.46..6,886.65 rows=476 width=13) (actual time=30.478..30.478 rows=0 loops=1)

  • Sort Key: r_12.component_part, r_12.alt_code
  • Sort Method: quicksort Memory: 25kB
288. 0.001 30.472 ↓ 0.0 0 1

Nested Loop (cost=0.29..6,864.29 rows=476 width=13) (actual time=30.472..30.472 rows=0 loops=1)

289. 30.471 30.471 ↓ 0.0 0 1

Seq Scan on co_tool_data ct (cost=0.00..6,428.66 rows=322 width=15) (actual time=30.471..30.471 rows=0 loops=1)

  • Filter: ((update_code = $43) AND (session_id = ($42)::text))
  • Rows Removed by Filter: 138183
290. 0.000 0.000 ↓ 0.0 0

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows r_12 (cost=0.29..1.29 rows=6 width=34) (never executed)

  • Index Cond: (part_no = ct.comp_part)
  • Filter: (NOT (ct.alt_code IS DISTINCT FROM alternate))
291.          

CTE cr_data

292. 10.758 934.330 ↓ 1.2 12,414 1

Merge Left Join (cost=6,837.99..7,325.54 rows=10,629 width=338) (actual time=917.997..934.330 rows=12,414 loops=1)

  • Merge Cond: (c_4.comp_part = b1.comp_part)
  • Join Filter: (NOT (c_4.alt_code IS DISTINCT FROM b1.alt_code))
293.          

Initplan (for Merge Left Join)

294. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on up_id (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

295. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _comp_part (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

296. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _part_no (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

297. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on up_id up_id_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

298. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

299. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

300. 0.000 0.000 ↓ 0.0 0

CTE Scan on _main (cost=0.00..0.02 rows=1 width=1) (never executed)

301. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _main _main_1 (cost=0.00..0.02 rows=1 width=1) (actual time=0.002..0.002 rows=1 loops=1)

302. 0.000 0.000 ↓ 0.0 0

CTE Scan on _comp_part _comp_part_2 (cost=0.00..0.02 rows=1 width=32) (never executed)

303. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

304. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

305. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

306. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

307. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on m_depth (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

308. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

309. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _qty (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

310. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _main _main_2 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

311. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

312. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

313. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

314. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _session_id _session_id_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

315. 3.872 87.579 ↓ 1.2 12,414 1

Merge Left Join (cost=6,824.74..6,920.68 rows=10,629 width=162) (actual time=82.739..87.579 rows=12,414 loops=1)

  • Merge Cond: (c_4.comp_part = p_2.component_part)
  • Join Filter: (NOT (c_4.alt_code IS DISTINCT FROM p_2.alt_code))
316. 19.899 53.213 ↓ 1.2 12,414 1

Sort (cost=6,794.06..6,820.63 rows=10,629 width=130) (actual time=52.240..53.213 rows=12,414 loops=1)

  • Sort Key: c_4.comp_part
  • Sort Method: quicksort Memory: 3618kB
317. 33.314 33.314 ↓ 1.2 12,414 1

Seq Scan on co_tool_data c_4 (cost=0.00..6,083.20 rows=10,629 width=130) (actual time=2.170..33.314 rows=12,414 loops=1)

  • Filter: (session_id = ($67)::text)
  • Rows Removed by Filter: 125769
318. 0.014 30.494 ↓ 0.0 0 1

Sort (cost=30.69..31.88 rows=476 width=64) (actual time=30.494..30.494 rows=0 loops=1)

  • Sort Key: p_2.component_part
  • Sort Method: quicksort Memory: 25kB
319. 30.480 30.480 ↓ 0.0 0 1

CTE Scan on p2 p_2 (cost=0.00..9.52 rows=476 width=64) (actual time=30.480..30.480 rows=0 loops=1)

320. 7.619 835.975 ↓ 57.3 12,482 1

Sort (cost=12.83..13.37 rows=218 width=160) (actual time=835.218..835.975 rows=12,482 loops=1)

  • Sort Key: b1.comp_part
  • Sort Method: quicksort Memory: 264kB
321. 828.356 828.356 ↓ 10.0 2,182 1

CTE Scan on b1 (cost=0.00..4.36 rows=218 width=160) (actual time=822.546..828.356 rows=2,182 loops=1)

322.          

CTE base

323. 950.141 950.145 ↓ 6,791.0 6,791 1

CTE Scan on cr_data (cost=0.14..398.73 rows=1 width=648) (actual time=919.855..950.145 rows=6,791 loops=1)

  • Filter: ((depth >= $69) AND (depth = $70) AND CASE WHEN (($72 = $73) AND (NOT $74)) THEN (comp_part = $75) ELSE true END AND (session_id = ($71)::text))
  • Rows Removed by Filter: 5623
324.          

Initplan (for CTE Scan)

325. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

326. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on counter (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

327. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _session_id _session_id_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

328. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on counter counter_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

329. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

330. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _first (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

331. 0.000 0.000 ↓ 0.0 0

CTE Scan on _comp_part _comp_part_5 (cost=0.00..0.02 rows=1 width=32) (never executed)

332.          

CTE base_extra

333. 24.307 1,418.910 ↓ 4,438.5 8,877 1

Unique (cost=253.47..253.66 rows=2 width=648) (actual time=1,391.361..1,418.910 rows=8,877 loops=1)

334. 342.371 1,394.603 ↓ 8,731.5 17,463 1

Sort (cost=253.47..253.48 rows=2 width=648) (actual time=1,391.359..1,394.603 rows=17,463 loops=1)

  • Sort Key: b.id, b.session_id, b.part_no, b.alternate, b.comp_part, b.alt_code, b.mt, b.qty_pa, b.source, b.needed_qty, b.to_prod, b.to_purch, b.to_buy, b.co_rows_id, b.qty_from_parent, b.depth, b.man, b.changed_by_user, b.is_related, b.qty_to_child, b.edit_row, b.level, b.up9, b.up_null, b.comp_equal_up_part, b.comp_equal_up_comp_part, b.depth_smaller, b.depth_bigger, b.up_qty, b._main, b.up_comp, b.up_id, b.up_part, b._need, b.minlevel, b.update_code, b.parent_update
  • Sort Method: external sort Disk: 3544kB
335. 1.484 1,052.232 ↓ 8,731.5 17,463 1

Append (cost=0.00..253.46 rows=2 width=648) (actual time=919.857..1,052.232 rows=17,463 loops=1)

336. 952.182 952.182 ↓ 6,791.0 6,791 1

CTE Scan on base b (cost=0.00..0.02 rows=1 width=648) (actual time=919.856..952.182 rows=6,791 loops=1)

337. 93.631 98.566 ↓ 10,672.0 10,672 1

Hash Join (cost=0.06..253.42 rows=1 width=648) (actual time=3.443..98.566 rows=10,672 loops=1)

  • Hash Cond: (c_5.comp_part = b_1.comp_part)
  • Join Filter: ((NOT (b_1.alt_code IS DISTINCT FROM c_5.alt_code)) AND CASE WHEN (NOT $77) THEN (b_1.depth <> c_5.depth) ELSE (b_1.id <> c_5.id) END)
  • Rows Removed by Join Filter: 760157
338.          

Initplan (for Hash Join)

339. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _main _main_3 (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

340. 1.653 1.653 ↓ 1.2 12,414 1

CTE Scan on cr_data c_5 (cost=0.00..212.58 rows=10,629 width=648) (actual time=0.001..1.653 rows=12,414 loops=1)

341. 1.354 3.281 ↓ 6,791.0 6,791 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=3.281..3.281 rows=6,791 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 469kB
342. 1.927 1.927 ↓ 6,791.0 6,791 1

CTE Scan on base b_1 (cost=0.00..0.02 rows=1 width=76) (actual time=0.002..1.927 rows=6,791 loops=1)

  • Filter: (part_no <> comp_part)
343.          

CTE _qty

344. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

345.          

CTE up_id

346. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

347.          

CTE m_depth

348. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

349.          

CTE counter

350. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

351.          

CTE _main

352. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

353.          

CTE _first

354. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

355.          

CTE _update_code

356. 0.025 0.025 ↑ 1.0 1 1

Values Scan on ""*VALUES*"" (cost=0.00..0.01 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=1)

357.          

CTE b1

358. 1.452 827.212 ↓ 10.0 2,182 1

Subquery Scan on c_3 (cost=41,714.10..41,723.91 rows=218 width=160) (actual time=822.543..827.212 rows=2,182 loops=1)

359. 825.760 825.760 ↓ 10.0 2,182 1

CTE Scan on e (cost=41,714.10..41,718.46 rows=218 width=292) (actual time=822.540..825.760 rows=2,182 loops=1)

360.          

CTE a

361. 0.507 1.823 ↑ 1.0 2,182 1

Hash Right Join (cost=115.41..173.47 rows=2,183 width=25) (actual time=0.981..1.823 rows=2,182 loops=1)

  • Hash Cond: (sa.part_no = a.code)
362. 0.355 0.355 ↑ 1.0 1,509 1

Seq Scan on structure_alt sa (cost=0.00..54.09 rows=1,509 width=22) (actual time=0.005..0.355 rows=1,509 loops=1)

363. 0.415 0.961 ↑ 1.0 2,182 1

Hash (cost=88.12..88.12 rows=2,183 width=19) (actual time=0.961..0.961 rows=2,182 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 135kB
364. 0.546 0.546 ↑ 1.0 2,182 1

Seq Scan on articles a (cost=0.00..88.12 rows=2,183 width=19) (actual time=0.005..0.546 rows=2,182 loops=1)

  • Filter: (NOT closed)
  • Rows Removed by Filter: 30
365.          

CTE so_rep

366. 2.015 326.081 ↓ 3.8 23,872 1

Subquery Scan on so_reportings_total_agg (cost=9,133.31..9,917.06 rows=6,204 width=50) (actual time=290.299..326.081 rows=23,872 loops=1)

367. 19.965 324.066 ↓ 3.8 23,872 1

GroupAggregate (cost=9,133.31..9,855.02 rows=6,204 width=150) (actual time=290.297..324.066 rows=23,872 loops=1)

  • Group Key: r.sernr, r.comp_part, r.alt_code, ((COALESCE((rs.value ->> 'mat_auto_report_calc_round_to'::text), '2'::text))::integer)
368. 91.061 304.101 ↑ 1.0 35,534 1

Sort (cost=9,133.31..9,222.58 rows=35,706 width=525) (actual time=290.277..304.101 rows=35,534 loops=1)

  • Sort Key: r.sernr, r.comp_part, r.alt_code, ((COALESCE((rs.value ->> 'mat_auto_report_calc_round_to'::text), '2'::text))::integer)
  • Sort Method: external merge Disk: 28608kB
369. 208.883 213.040 ↑ 1.0 35,534 1

Nested Loop Left Join (cost=0.00..1,621.25 rows=35,706 width=525) (actual time=0.056..213.040 rows=35,534 loops=1)

370. 4.157 4.157 ↑ 1.0 35,534 1

Seq Scan on so_reportings r (cost=0.00..906.06 rows=35,706 width=23) (actual time=0.006..4.157 rows=35,534 loops=1)

371. 0.000 0.000 ↑ 1.0 1 35,534

Materialize (cost=0.00..1.07 rows=1 width=498) (actual time=0.000..0.000 rows=1 loops=35,534)

372. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on reg_settings rs (cost=0.00..1.06 rows=1 width=498) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (register = 'ProdOrder'::text)
  • Rows Removed by Filter: 4
373.          

CTE gr_so_rows

374. 33.599 36.592 ↓ 4.2 29,564 1

HashAggregate (cost=1,105.70..1,194.48 rows=7,102 width=50) (actual time=23.426..36.592 rows=29,564 loops=1)

  • Group Key: r_1.sernr, r_1.comp_part, r_1.alt_code
375. 2.993 2.993 ↓ 1.0 29,756 1

Seq Scan on so_rows r_1 (cost=0.00..808.35 rows=29,735 width=23) (actual time=0.004..2.993 rows=29,756 loops=1)

376.          

CTE st_total

377. 0.539 13.249 ↑ 1.1 6,211 1

Subquery Scan on so_receivings_total (cost=695.45..854.75 rows=7,080 width=36) (actual time=10.836..13.249 rows=6,211 loops=1)

378. 5.562 12.710 ↑ 1.1 6,211 1

HashAggregate (cost=695.45..783.95 rows=7,080 width=55) (actual time=10.835..12.710 rows=6,211 loops=1)

  • Group Key: h.sernr
379. 2.551 7.148 ↓ 1.0 8,192 1

Hash Join (cost=390.30..654.56 rows=8,178 width=26) (actual time=3.855..7.148 rows=8,192 loops=1)

  • Hash Cond: (rec.sernr = h.sernr)
380. 0.790 0.790 ↓ 1.0 8,192 1

Seq Scan on so_receivings rec (cost=0.00..242.78 rows=8,178 width=8) (actual time=0.003..0.790 rows=8,192 loops=1)

381. 1.264 3.807 ↓ 1.0 7,108 1

Hash (cost=301.80..301.80 rows=7,080 width=22) (actual time=3.807..3.807 rows=7,108 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 441kB
382. 2.543 2.543 ↓ 1.0 7,108 1

Seq Scan on so_header h (cost=0.00..301.80 rows=7,080 width=22) (actual time=0.002..2.543 rows=7,108 loops=1)

383.          

CTE to_deliver

384. 0.206 31.769 ↓ 2.9 1,157 1

Subquery Scan on co_rows_to_deliver (cost=6,209.15..6,281.05 rows=400 width=36) (actual time=30.083..31.769 rows=1,157 loops=1)

385. 1.410 31.563 ↓ 2.9 1,157 1

GroupAggregate (cost=6,209.15..6,276.05 rows=400 width=361) (actual time=30.081..31.563 rows=1,157 loops=1)

  • Group Key: rows.sernr, rows.rowno, rows.artcode, rows.artname, rows.batch, rows.alt_code, rows.alt_desc, rows.unit, rows.unit2, rows.qty, rows.qty2, rows.unitkoef, rows.warehouse, rows.location, rows.unit_primary, rows.id, rows.co_sernr, rows.parent_rowno
386. 2.148 30.153 ↓ 1.3 1,485 1

Sort (cost=6,209.15..6,212.00 rows=1,141 width=297) (actual time=30.070..30.153 rows=1,485 loops=1)

  • Sort Key: rows.sernr, rows.rowno, rows.artcode, rows.artname, rows.batch, rows.alt_code, rows.alt_desc, rows.unit, rows.unit2, rows.qty, rows.qty2, rows.unitkoef, rows.warehouse, rows.location, rows.unit_primary, rows.id, rows.co_sernr, rows.parent_rowno
  • Sort Method: quicksort Memory: 386kB
387. 0.360 28.005 ↓ 1.3 1,485 1

Subquery Scan on rows (cost=6,136.95..6,151.21 rows=1,141 width=297) (actual time=27.564..28.005 rows=1,485 loops=1)

388. 0.987 27.645 ↓ 1.3 1,485 1

Sort (cost=6,136.95..6,139.80 rows=1,141 width=382) (actual time=27.562..27.645 rows=1,485 loops=1)

  • Sort Key: r_2.sernr, r_2.rowno
  • Sort Method: quicksort Memory: 426kB
389. 2.905 26.658 ↓ 1.3 1,485 1

HashAggregate (cost=6,061.89..6,079.01 rows=1,141 width=382) (actual time=25.705..26.658 rows=1,485 loops=1)

  • Group Key: str.alt_desc, d.to_deliver_qty, dh.confirmed, dh.sent_to_collection, r_2.id, h_1.sernr, cor.rowno
390. 9.060 23.753 ↓ 1.3 1,485 1

Nested Loop (cost=624.98..6,024.81 rows=1,141 width=290) (actual time=5.529..23.753 rows=1,485 loops=1)

  • Join Filter: CASE WHEN ((rs_1.value ->> 'co_rows_view'::text) = '1'::text) THEN CASE WHEN (r_2.parent IS NOT NULL) THEN (r_2.parent IS NOT NULL) WHEN ((SubPlan 15) = 0) THEN true ELSE (cor.id IS NOT NULL) END ELSE (r_2.rowno > 0) END
  • Rows Removed by Join Filter: 1149
391. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on reg_settings rs_1 (cost=0.00..1.06 rows=1 width=498) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: (register = 'Invoice'::text)
  • Rows Removed by Filter: 4
392. 0.744 11.231 ↓ 1.2 2,634 1

Hash Left Join (cost=624.98..706.69 rows=2,282 width=298) (actual time=5.506..11.231 rows=2,634 loops=1)

  • Hash Cond: (r_2.parent = cor.id)
393. 1.160 9.721 ↓ 1.2 2,634 1

Hash Left Join (cost=490.88..566.60 rows=2,282 width=290) (actual time=4.720..9.721 rows=2,634 loops=1)

  • Hash Cond: (r_2.artcode = str.part_no)
  • Join Filter: (NOT (str.code IS DISTINCT FROM r_2.alt_code))
394. 0.706 7.996 ↓ 1.2 2,634 1

Hash Left Join (cost=417.92..456.56 rows=2,282 width=267) (actual time=4.147..7.996 rows=2,634 loops=1)

  • Hash Cond: (d.sernr = dh.sernr)
395. 1.050 7.059 ↓ 1.2 2,634 1

Hash Left Join (cost=383.73..416.33 rows=2,282 width=255) (actual time=3.907..7.059 rows=2,634 loops=1)

  • Hash Cond: (r_2.artcode = a_1.code)
396. 0.762 5.150 ↓ 1.2 2,634 1

Hash Join (cost=267.96..294.56 rows=2,282 width=251) (actual time=3.029..5.150 rows=2,634 loops=1)

  • Hash Cond: (r_2.sernr = h_1.sernr)
397. 1.024 4.241 ↓ 1.1 2,634 1

Hash Right Join (cost=244.93..265.44 rows=2,295 width=247) (actual time=2.870..4.241 rows=2,634 loops=1)

  • Hash Cond: (d.co_rows_id = r_2.id)
398. 0.380 1.479 ↓ 1.0 2,567 1

Hash Right Join (cost=115.53..129.32 rows=2,557 width=45) (actual time=1.120..1.479 rows=2,567 loops=1)

  • Hash Cond: (c.del_row_id = d.id)
399. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on collection_rows c (cost=0.00..13.00 rows=300 width=36) (actual time=0.001..0.001 rows=0 loops=1)

400. 0.414 1.098 ↓ 1.0 2,567 1

Hash (cost=83.57..83.57 rows=2,557 width=17) (actual time=1.098..1.098 rows=2,567 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 159kB
401. 0.684 0.684 ↓ 1.0 2,567 1

Seq Scan on del_rows d (cost=0.00..83.57 rows=2,557 width=17) (actual time=0.004..0.684 rows=2,567 loops=1)

402. 0.758 1.738 ↓ 1.0 2,306 1

Hash (cost=100.71..100.71 rows=2,295 width=206) (actual time=1.738..1.738 rows=2,306 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 286kB
403. 0.980 0.980 ↓ 1.0 2,306 1

Seq Scan on co_rows r_2 (cost=0.00..100.71 rows=2,295 width=206) (actual time=0.004..0.980 rows=2,306 loops=1)

  • Filter: (closed IS NULL)
  • Rows Removed by Filter: 380
404. 0.042 0.147 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.147..0.147 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
405. 0.105 0.105 ↑ 1.0 350 1

Seq Scan on co_header h_1 (cost=0.00..18.58 rows=356 width=4) (actual time=0.002..0.105 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
406. 0.430 0.859 ↑ 1.0 2,212 1

Hash (cost=88.12..88.12 rows=2,212 width=19) (actual time=0.859..0.859 rows=2,212 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 145kB
407. 0.429 0.429 ↑ 1.0 2,212 1

Seq Scan on articles a_1 (cost=0.00..88.12 rows=2,212 width=19) (actual time=0.002..0.429 rows=2,212 loops=1)

408. 0.078 0.231 ↑ 1.0 539 1

Hash (cost=27.42..27.42 rows=542 width=20) (actual time=0.231..0.231 rows=539 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
409. 0.153 0.153 ↑ 1.0 539 1

Seq Scan on del_header dh (cost=0.00..27.42 rows=542 width=20) (actual time=0.003..0.153 rows=539 loops=1)

410. 0.342 0.565 ↑ 1.0 1,509 1

Hash (cost=54.09..54.09 rows=1,509 width=41) (actual time=0.565..0.565 rows=1,509 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 126kB
411. 0.223 0.223 ↑ 1.0 1,509 1

Seq Scan on structure_alt str (cost=0.00..54.09 rows=1,509 width=41) (actual time=0.002..0.223 rows=1,509 loops=1)

412. 0.392 0.766 ↓ 1.0 2,686 1

Hash (cost=100.71..100.71 rows=2,671 width=8) (actual time=0.766..0.766 rows=2,686 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
413. 0.374 0.374 ↓ 1.0 2,686 1

Seq Scan on co_rows cor (cost=0.00..100.71 rows=2,671 width=8) (actual time=0.005..0.374 rows=2,686 loops=1)

414.          

SubPlan (for Nested Loop)

415. 1.152 3.456 ↑ 1.0 1 1,152

Aggregate (cost=2.30..2.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,152)

416. 2.304 2.304 ↑ 1.0 1 1,152

Index Scan using co_rows_parent_idx on co_rows cr (cost=0.28..2.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,152)

  • Index Cond: (parent = r_2.id)
417.          

CTE mt

418. 0.369 464.029 ↓ 2.1 428 1

GroupAggregate (cost=9,199.44..9,269.87 rows=200 width=61) (actual time=462.273..464.029 rows=428 loops=1)

  • Group Key: r_3.artcode, r_3.alt_code
419. 0.348 463.660 ↑ 1.1 792 1

Merge Append (cost=9,199.44..9,260.78 rows=878 width=61) (actual time=462.261..463.660 rows=792 loops=1)

  • Sort Key: r_3.artcode, r_3.alt_code
420. 0.001 1.058 ↓ 0.0 0 1

GroupAggregate (cost=6,244.45..6,250.99 rows=235 width=53) (actual time=1.058..1.058 rows=0 loops=1)

  • Group Key: r_3.artcode, r_3.alt_code
421. 0.005 1.057 ↓ 0.0 0 1

Sort (cost=6,244.45..6,245.20 rows=301 width=31) (actual time=1.057..1.057 rows=0 loops=1)

  • Sort Key: r_3.artcode, r_3.alt_code
  • Sort Method: quicksort Memory: 25kB
422. 0.008 1.052 ↓ 0.0 0 1

Hash Join (cost=6,168.01..6,232.06 rows=301 width=31) (actual time=1.052..1.052 rows=0 loops=1)

  • Hash Cond: (r_3.sernr = h_2.sernr)
423. 0.010 0.861 ↓ 0.0 0 1

Hash Right Join (cost=6,144.98..6,208.22 rows=303 width=35) (actual time=0.861..0.861 rows=0 loops=1)

  • Hash Cond: (rows_1.id = r_3.id)
424. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=6,064.56..6,122.76 rows=400 width=361) (never executed)

  • Group Key: rows_1.sernr, rows_1.rowno, rows_1.artcode, rows_1.artname, rows_1.batch, rows_1.alt_code, rows_1.alt_desc, rows_1.unit, rows_1.unit2, rows_1.qty, rows_1.qty2, rows_1.unitkoef, rows_1.warehouse, rows_1.location, rows_1.unit_primary, rows_1.id, rows_1.co_sernr, rows_1.parent_rowno
425. 0.000 0.000 ↓ 0.0 0

Sort (cost=6,064.56..6,067.41 rows=1,141 width=233) (never executed)

  • Sort Key: rows_1.sernr, rows_1.rowno, rows_1.artcode, rows_1.artname, rows_1.batch, rows_1.alt_code, rows_1.alt_desc, rows_1.unit, rows_1.unit2, rows_1.qty, rows_1.qty2, rows_1.unitkoef, rows_1.warehouse, rows_1.location, rows_1.unit_primary, rows_1.id, rows_1.co_sernr, rows_1.parent_rowno
426. 0.000 0.000 ↓ 0.0 0

Subquery Scan on rows_1 (cost=5,992.35..6,006.62 rows=1,141 width=233) (never executed)

427. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,992.35..5,995.21 rows=1,141 width=382) (never executed)

  • Sort Key: r_4.sernr, r_4.rowno
428. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,923.00..5,934.41 rows=1,141 width=382) (never executed)

  • Group Key: str_1.alt_desc, d_1.to_deliver_qty, dh_1.confirmed, dh_1.sent_to_collection, r_4.id, h_3.sernr, cor_1.rowno
429. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=509.20..5,903.04 rows=1,141 width=254) (never executed)

  • Join Filter: CASE WHEN ((rs_2.value ->> 'co_rows_view'::text) = '1'::text) THEN CASE WHEN (r_4.parent IS NOT NULL) THEN (r_4.parent IS NOT NULL) WHEN ((SubPlan 17) = 0) THEN true ELSE (cor_1.id IS NOT NULL) END ELSE (r_4.rowno > 0) END
430. 0.000 0.000 ↓ 0.0 0

Seq Scan on reg_settings rs_2 (cost=0.00..1.06 rows=1 width=498) (never executed)

  • Filter: (register = 'Invoice'::text)
431. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=509.20..584.91 rows=2,282 width=262) (never executed)

  • Hash Cond: (r_4.parent = cor_1.id)
432. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=375.11..444.83 rows=2,282 width=254) (never executed)

  • Hash Cond: (r_4.artcode = str_1.part_no)
  • Join Filter: (NOT (str_1.code IS DISTINCT FROM r_4.alt_code))
433. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=302.15..334.79 rows=2,282 width=231) (never executed)

  • Hash Cond: (d_1.sernr = dh_1.sernr)
434. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=267.96..294.56 rows=2,282 width=219) (never executed)

  • Hash Cond: (r_4.sernr = h_3.sernr)
435. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=244.93..265.44 rows=2,295 width=215) (never executed)

  • Hash Cond: (d_1.co_rows_id = r_4.id)
436. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=115.53..129.32 rows=2,557 width=13) (never executed)

  • Hash Cond: (c_1.del_row_id = d_1.id)
437. 0.000 0.000 ↓ 0.0 0

Seq Scan on collection_rows c_1 (cost=0.00..13.00 rows=300 width=4) (never executed)

438. 0.000 0.000 ↓ 0.0 0

Hash (cost=83.57..83.57 rows=2,557 width=17) (never executed)

439. 0.000 0.000 ↓ 0.0 0

Seq Scan on del_rows d_1 (cost=0.00..83.57 rows=2,557 width=17) (never executed)

440. 0.000 0.000 ↓ 0.0 0

Hash (cost=100.71..100.71 rows=2,295 width=206) (never executed)

441. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_rows r_4 (cost=0.00..100.71 rows=2,295 width=206) (never executed)

  • Filter: (closed IS NULL)
442. 0.000 0.000 ↓ 0.0 0

Hash (cost=18.58..18.58 rows=356 width=4) (never executed)

443. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_header h_3 (cost=0.00..18.58 rows=356 width=4) (never executed)

  • Filter: (confirmed IS NOT NULL)
444. 0.000 0.000 ↓ 0.0 0

Hash (cost=27.42..27.42 rows=542 width=20) (never executed)

445. 0.000 0.000 ↓ 0.0 0

Seq Scan on del_header dh_1 (cost=0.00..27.42 rows=542 width=20) (never executed)

446. 0.000 0.000 ↓ 0.0 0

Hash (cost=54.09..54.09 rows=1,509 width=41) (never executed)

447. 0.000 0.000 ↓ 0.0 0

Seq Scan on structure_alt str_1 (cost=0.00..54.09 rows=1,509 width=41) (never executed)

448. 0.000 0.000 ↓ 0.0 0

Hash (cost=100.71..100.71 rows=2,671 width=8) (never executed)

449. 0.000 0.000 ↓ 0.0 0

Seq Scan on co_rows cor_1 (cost=0.00..100.71 rows=2,671 width=8) (never executed)

450.          

SubPlan (for Nested Loop)

451. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.30..2.31 rows=1 width=8) (never executed)

452. 0.000 0.000 ↓ 0.0 0

Index Scan using co_rows_parent_idx on co_rows cr_1 (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Index Cond: (parent = r_4.id)
453. 0.000 0.851 ↓ 0.0 0 1

Hash (cost=76.63..76.63 rows=303 width=34) (actual time=0.851..0.851 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
454. 0.851 0.851 ↓ 0.0 0 1

Index Scan using co_rows_parent_idx on co_rows r_3 (cost=0.28..76.63 rows=303 width=34) (actual time=0.851..0.851 rows=0 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((closed IS NULL) AND (alt_code IS NOT NULL) AND (so_ref IS NULL) AND (batch IS NULL))
  • Rows Removed by Filter: 1333
455. 0.049 0.183 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.183..0.183 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
456. 0.134 0.134 ↑ 1.0 350 1

Seq Scan on co_header h_2 (cost=0.00..18.58 rows=356 width=4) (actual time=0.006..0.134 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
457. 0.017 50.189 ↑ 1.5 184 1

Subquery Scan on "*SELECT* 2" (cost=526.02..540.88 rows=283 width=49) (actual time=49.985..50.189 rows=184 loops=1)

458. 0.181 50.172 ↑ 1.5 184 1

GroupAggregate (cost=526.02..538.05 rows=283 width=102) (actual time=49.984..50.172 rows=184 loops=1)

  • Group Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
459. 0.384 49.991 ↑ 1.5 185 1

Sort (cost=526.02..526.73 rows=283 width=107) (actual time=49.973..49.991 rows=185 loops=1)

  • Sort Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
  • Sort Method: quicksort Memory: 47kB
460. 0.599 49.607 ↑ 1.5 185 1

Hash Right Join (cost=343.51..514.49 rows=283 width=107) (actual time=45.149..49.607 rows=185 loops=1)

  • Hash Cond: (st.sernr = sh.sernr)
461. 14.747 14.747 ↑ 1.1 6,211 1

CTE Scan on st_total st (cost=0.00..141.60 rows=7,080 width=36) (actual time=10.838..14.747 rows=6,211 loops=1)

462. 0.060 34.261 ↑ 1.5 185 1

Hash (cost=339.98..339.98 rows=283 width=79) (actual time=34.261..34.261 rows=185 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
463. 0.163 34.201 ↑ 1.5 185 1

Nested Loop (cost=107.96..339.98 rows=283 width=79) (actual time=33.444..34.201 rows=185 loops=1)

464. 0.045 33.668 ↑ 1.7 185 1

Hash Join (cost=107.67..117.57 rows=318 width=53) (actual time=33.425..33.668 rows=185 loops=1)

  • Hash Cond: (r_5.sernr = h_4.sernr)
465. 0.363 33.476 ↑ 1.7 185 1

Hash Right Join (cost=84.64..93.69 rows=320 width=57) (actual time=33.272..33.476 rows=185 loops=1)

  • Hash Cond: (t.id = r_5.id)
  • Filter: CASE WHEN (r_5.closed IS NOT NULL) THEN true ELSE (t.qty > '0'::numeric) END
  • Rows Removed by Filter: 1110
466. 32.046 32.046 ↓ 2.9 1,157 1

CTE Scan on to_deliver t (cost=0.00..8.00 rows=400 width=36) (actual time=30.085..32.046 rows=1,157 loops=1)

467. 0.234 1.067 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=29) (actual time=1.066..1.067 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
468. 0.833 0.833 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_5 (cost=0.28..76.63 rows=641 width=29) (actual time=0.034..0.833 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
469. 0.043 0.147 ↑ 1.0 350 1

Hash (cost=18.58..18.58 rows=356 width=4) (actual time=0.147..0.147 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
470. 0.104 0.104 ↑ 1.0 350 1

Seq Scan on co_header h_4 (cost=0.00..18.58 rows=356 width=4) (actual time=0.003..0.104 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
471. 0.370 0.370 ↑ 1.0 1 185

Index Scan using so_header_pkey on so_header sh (cost=0.28..0.70 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=185)

  • Index Cond: (sernr = r_5.so_ref)
  • Filter: ((planned IS NOT NULL) AND (cancelled IS NULL))
472. 0.047 393.441 ↓ 6.7 497 1

Subquery Scan on "*SELECT* 3" (cost=842.63..853.55 rows=74 width=96) (actual time=392.653..393.441 rows=497 loops=1)

473. 0.696 393.394 ↓ 6.7 497 1

GroupAggregate (cost=842.63..852.81 rows=74 width=128) (actual time=392.652..393.394 rows=497 loops=1)

  • Group Key: sr.comp_part, sr.alt_code, st_1.qty
474. 1.343 392.698 ↓ 9.4 696 1

Sort (cost=842.63..842.82 rows=74 width=128) (actual time=392.642..392.698 rows=696 loops=1)

  • Sort Key: sr.comp_part, sr.alt_code, st_1.qty
  • Sort Method: quicksort Memory: 76kB
475. 0.583 391.355 ↓ 9.4 696 1

Nested Loop (cost=562.50..840.34 rows=74 width=128) (actual time=387.006..391.355 rows=696 loops=1)

476. 4.206 390.076 ↓ 9.4 696 1

Hash Right Join (cost=562.36..826.16 rows=74 width=132) (actual time=386.973..390.076 rows=696 loops=1)

  • Hash Cond: ((st_1.sernr = sr.sernr) AND (st_1.comp_part = sr.comp_part))
  • Join Filter: (NOT (st_1.alt_code IS DISTINCT FROM sr.alt_code))
477. 333.862 333.862 ↓ 3.8 23,872 1

CTE Scan on so_rep st_1 (cost=0.00..124.08 rows=6,204 width=100) (actual time=290.301..333.862 rows=23,872 loops=1)

478. 0.219 52.008 ↓ 9.4 696 1

Hash (cost=561.25..561.25 rows=74 width=104) (actual time=52.008..52.008 rows=696 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
479. 2.695 51.789 ↓ 9.4 696 1

Hash Join (cost=391.83..561.25 rows=74 width=104) (actual time=26.541..51.789 rows=696 loops=1)

  • Hash Cond: (sr.sernr = sh_1.sernr)
480. 46.007 46.007 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows sr (cost=0.00..142.04 rows=7,102 width=100) (actual time=23.428..46.007 rows=29,564 loops=1)

481. 0.011 3.087 ↑ 1.7 43 1

Hash (cost=390.91..390.91 rows=74 width=16) (actual time=3.087..3.087 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
482. 0.032 3.076 ↑ 1.7 43 1

Hash Join (cost=84.64..390.91 rows=74 width=16) (actual time=2.182..3.076 rows=43 loops=1)

  • Hash Cond: (sh_1.sernr = r_6.so_ref)
483. 1.918 1.918 ↑ 5.5 148 1

Seq Scan on so_header sh_1 (cost=0.00..301.80 rows=821 width=4) (actual time=0.095..1.918 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
484. 0.173 1.126 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=12) (actual time=1.126..1.126 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
485. 0.953 0.953 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_6 (cost=0.28..76.63 rows=641 width=12) (actual time=0.043..0.953 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
486. 0.696 0.696 ↑ 1.0 1 696

Index Scan using co_header_pkey on co_header h_5 (cost=0.15..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=696)

  • Index Cond: (sernr = r_6.sernr)
  • Filter: (confirmed IS NOT NULL)
487. 0.106 4.685 ↑ 2.0 71 1

Sort (cost=615.93..616.29 rows=143 width=49) (actual time=4.680..4.685 rows=71 loops=1)

  • Sort Key: ""*SELECT* 4"".part_no, ""*SELECT* 4"".alt_code
  • Sort Method: quicksort Memory: 30kB
488. 0.009 4.579 ↑ 2.0 71 1

Subquery Scan on "*SELECT* 4" (cost=607.60..610.81 rows=143 width=49) (actual time=4.540..4.579 rows=71 loops=1)

489. 0.034 4.570 ↑ 2.0 71 1

Group (cost=607.60..609.38 rows=143 width=90) (actual time=4.539..4.570 rows=71 loops=1)

  • Group Key: st_2.received, sh_2.sernr
490. 0.042 4.536 ↑ 2.0 71 1

Sort (cost=607.60..607.95 rows=143 width=58) (actual time=4.532..4.536 rows=71 loops=1)

  • Sort Key: st_2.received, sh_2.sernr
  • Sort Method: quicksort Memory: 30kB
491. 0.062 4.494 ↑ 2.0 71 1

Nested Loop (cost=405.46..602.48 rows=143 width=58) (actual time=3.456..4.494 rows=71 loops=1)

492. 0.493 4.361 ↑ 2.0 71 1

Hash Right Join (cost=405.31..574.90 rows=144 width=62) (actual time=3.447..4.361 rows=71 loops=1)

  • Hash Cond: (st_2.sernr = sh_2.sernr)
493. 0.441 0.441 ↑ 1.1 6,211 1

CTE Scan on st_total st_2 (cost=0.00..141.60 rows=7,080 width=36) (actual time=0.001..0.441 rows=6,211 loops=1)

494. 0.018 3.427 ↑ 2.0 71 1

Hash (cost=403.51..403.51 rows=144 width=30) (actual time=3.427..3.427 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
495. 0.123 3.409 ↑ 2.0 71 1

Hash Right Join (cost=393.79..403.51 rows=144 width=30) (actual time=3.328..3.409 rows=71 loops=1)

  • Hash Cond: (t_1.id = r_7.id)
496. 0.094 0.094 ↓ 2.9 1,157 1

CTE Scan on to_deliver t_1 (cost=0.00..8.00 rows=400 width=4) (actual time=0.001..0.094 rows=1,157 loops=1)

497. 0.022 3.192 ↑ 2.0 71 1

Hash (cost=391.99..391.99 rows=144 width=34) (actual time=3.192..3.192 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
498. 0.038 3.170 ↑ 2.0 71 1

Hash Join (cost=84.64..391.99 rows=144 width=34) (actual time=1.566..3.170 rows=71 loops=1)

  • Hash Cond: (sh_2.master_sernr = r_7.so_ref)
499. 1.966 1.966 ↑ 5.5 148 1

Seq Scan on so_header sh_2 (cost=0.00..301.80 rows=821 width=30) (actual time=0.105..1.966 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
500. 0.204 1.166 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=16) (actual time=1.166..1.166 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
501. 0.962 0.962 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_7 (cost=0.28..76.63 rows=641 width=16) (actual time=0.037..0.962 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
502. 0.071 0.071 ↑ 1.0 1 71

Index Scan using co_header_pkey on co_header h_6 (cost=0.15..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=71)

  • Index Cond: (sernr = r_7.sernr)
  • Filter: (confirmed IS NOT NULL)
503. 0.005 13.939 ↑ 3.6 40 1

Subquery Scan on "*SELECT* 5" (cost=970.34..984.29 rows=143 width=96) (actual time=13.879..13.939 rows=40 loops=1)

504. 0.064 13.934 ↑ 3.6 40 1

GroupAggregate (cost=970.34..982.86 rows=143 width=128) (actual time=13.878..13.934 rows=40 loops=1)

  • Group Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
505. 0.109 13.870 ↑ 1.5 94 1

Sort (cost=970.34..970.70 rows=143 width=128) (actual time=13.864..13.870 rows=94 loops=1)

  • Sort Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
  • Sort Method: quicksort Memory: 30kB
506. 0.001 13.761 ↑ 1.5 94 1

Nested Loop (cost=565.28..965.22 rows=143 width=128) (actual time=13.418..13.761 rows=94 loops=1)

507. 3.364 13.572 ↑ 1.5 94 1

Hash Right Join (cost=565.13..937.64 rows=144 width=132) (actual time=13.395..13.572 rows=94 loops=1)

  • Hash Cond: ((st_3.sernr = sr_1.sernr) AND (st_3.comp_part = sr_1.comp_part))
  • Join Filter: (NOT (st_3.alt_code IS DISTINCT FROM sr_1.alt_code))
508. 2.305 2.305 ↓ 3.8 23,872 1

CTE Scan on so_rep st_3 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.305 rows=23,872 loops=1)

509. 0.027 7.903 ↑ 1.5 94 1

Hash (cost=562.97..562.97 rows=144 width=104) (actual time=7.903..7.903 rows=94 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
510. 0.090 7.876 ↑ 1.5 94 1

Hash Join (cost=396.71..562.97 rows=144 width=104) (actual time=2.940..7.876 rows=94 loops=1)

  • Hash Cond: (sh_3.master_sernr = r_8.so_ref)
511. 2.591 6.751 ↓ 1.3 1,061 1

Hash Join (cost=312.06..472.76 rows=824 width=104) (actual time=1.692..6.751 rows=1,061 loops=1)

  • Hash Cond: (sr_1.sernr = sh_3.sernr)
512. 2.484 2.484 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows sr_1 (cost=0.00..142.04 rows=7,102 width=100) (actual time=0.003..2.484 rows=29,564 loops=1)

513. 0.022 1.676 ↑ 5.5 148 1

Hash (cost=301.80..301.80 rows=821 width=8) (actual time=1.676..1.676 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
514. 1.654 1.654 ↑ 5.5 148 1

Seq Scan on so_header sh_3 (cost=0.00..301.80 rows=821 width=8) (actual time=0.075..1.654 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
515. 0.186 1.035 ↓ 2.0 1,295 1

Hash (cost=76.63..76.63 rows=641 width=12) (actual time=1.035..1.035 rows=1,295 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
516. 0.849 0.849 ↓ 2.0 1,295 1

Index Scan using co_rows_parent_idx on co_rows r_8 (cost=0.28..76.63 rows=641 width=12) (actual time=0.039..0.849 rows=1,295 loops=1)

  • Index Cond: (parent IS NOT NULL)
  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NOT NULL))
  • Rows Removed by Filter: 38
517. 0.188 0.188 ↑ 1.0 1 94

Index Scan using co_header_pkey on co_header h_7 (cost=0.15..0.19 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=94)

  • Index Cond: (sernr = r_8.sernr)
  • Filter: (confirmed IS NOT NULL)
518.          

CTE mt2

519. 0.055 3.556 ↓ 57.0 57 1

GroupAggregate (cost=212.04..212.07 rows=1 width=53) (actual time=3.504..3.556 rows=57 loops=1)

  • Group Key: r_9.artcode, r_9.alt_code
520. 0.120 3.501 ↓ 83.0 83 1

Sort (cost=212.04..212.05 rows=1 width=58) (actual time=3.497..3.501 rows=83 loops=1)

  • Sort Key: r_9.artcode, r_9.alt_code
  • Sort Method: quicksort Memory: 31kB
521. 0.089 3.381 ↓ 83.0 83 1

Nested Loop (cost=202.49..212.03 rows=1 width=58) (actual time=3.122..3.381 rows=83 loops=1)

522. 0.114 3.201 ↓ 91.0 91 1

Hash Right Join (cost=202.34..211.85 rows=1 width=62) (actual time=3.107..3.201 rows=91 loops=1)

  • Hash Cond: (t_2.id = r_9.id)
523. 0.085 0.085 ↓ 2.9 1,157 1

CTE Scan on to_deliver t_2 (cost=0.00..8.00 rows=400 width=36) (actual time=0.001..0.085 rows=1,157 loops=1)

524. 0.024 3.002 ↓ 91.0 91 1

Hash (cost=202.33..202.33 rows=1 width=34) (actual time=3.002..3.002 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
525. 0.804 2.978 ↓ 91.0 91 1

Merge Left Join (cost=0.56..202.33 rows=1 width=34) (actual time=0.281..2.978 rows=91 loops=1)

  • Merge Cond: (r_9.id = r2.parent)
  • Filter: (r2.id IS NULL)
  • Rows Removed by Filter: 1333
526. 1.531 1.531 ↓ 1.0 1,390 1

Index Scan using co_rows_pkey on co_rows r_9 (cost=0.28..125.34 rows=1,380 width=34) (actual time=0.054..1.531 rows=1,390 loops=1)

  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NULL))
  • Rows Removed by Filter: 1296
527. 0.643 0.643 ↑ 2.0 1,334 1

Index Scan using co_rows_parent_idx on co_rows r2 (cost=0.28..127.34 rows=2,671 width=8) (actual time=0.043..0.643 rows=1,334 loops=1)

528. 0.091 0.091 ↑ 1.0 1 91

Index Scan using co_header_pkey on co_header h_8 (cost=0.15..0.18 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=91)

  • Index Cond: (sernr = r_9.sernr)
  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 0
529.          

CTE tt

530. 0.026 321.714 ↑ 1.9 301 1

Append (cost=4,735.24..12,957.57 rows=567 width=66) (actual time=220.868..321.714 rows=301 loops=1)

531. 0.004 220.905 ↑ 10.7 34 1

Subquery Scan on "*SELECT* 1_1" (cost=4,735.24..4,752.58 rows=365 width=49) (actual time=220.867..220.905 rows=34 loops=1)

532. 0.045 220.901 ↑ 10.7 34 1

GroupAggregate (cost=4,735.24..4,748.93 rows=365 width=81) (actual time=220.866..220.901 rows=34 loops=1)

  • Group Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
533. 0.060 220.856 ↑ 10.7 34 1

Sort (cost=4,735.24..4,736.15 rows=365 width=86) (actual time=220.853..220.856 rows=34 loops=1)

  • Sort Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
  • Sort Method: quicksort Memory: 27kB
534. 0.453 220.796 ↑ 10.7 34 1

Hash Right Join (cost=4,541.81..4,719.71 rows=365 width=86) (actual time=218.866..220.796 rows=34 loops=1)

  • Hash Cond: (h_9.sernr = sh_5.sernr)
535. 7.216 19.701 ↑ 1.1 6,211 1

HashAggregate (cost=695.45..783.95 rows=7,080 width=55) (actual time=17.416..19.701 rows=6,211 loops=1)

  • Group Key: h_9.sernr
536. 4.116 12.485 ↓ 1.0 8,192 1

Hash Join (cost=390.30..654.56 rows=8,178 width=26) (actual time=7.184..12.485 rows=8,192 loops=1)

  • Hash Cond: (rec_1.sernr = h_9.sernr)
537. 1.216 1.216 ↓ 1.0 8,192 1

Seq Scan on so_receivings rec_1 (cost=0.00..242.78 rows=8,178 width=8) (actual time=0.004..1.216 rows=8,192 loops=1)

538. 2.295 7.153 ↓ 1.0 7,108 1

Hash (cost=301.80..301.80 rows=7,080 width=22) (actual time=7.153..7.153 rows=7,108 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 441kB
539. 4.858 4.858 ↓ 1.0 7,108 1

Seq Scan on so_header h_9 (cost=0.00..301.80 rows=7,080 width=22) (actual time=0.004..4.858 rows=7,108 loops=1)

540. 0.042 200.642 ↑ 10.7 34 1

Hash (cost=3,841.80..3,841.80 rows=365 width=26) (actual time=200.642..200.642 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
541. 200.600 200.600 ↑ 10.7 34 1

Seq Scan on so_header sh_5 (cost=0.00..3,841.80 rows=365 width=26) (actual time=1.396..200.600 rows=34 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (NOT so_has_co_ref(sernr)) AND (NOT so_has_co_ref(master_sernr)))
  • Rows Removed by Filter: 7074
542. 0.021 51.682 ↓ 1.8 239 1

Subquery Scan on "*SELECT* 2_2" (cost=2,613.56..2,627.16 rows=136 width=96) (actual time=51.453..51.682 rows=239 loops=1)

543. 0.208 51.661 ↓ 1.8 239 1

GroupAggregate (cost=2,613.56..2,625.80 rows=136 width=132) (actual time=51.452..51.661 rows=239 loops=1)

  • Group Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
544. 0.397 51.453 ↓ 1.8 239 1

Sort (cost=2,613.56..2,613.90 rows=136 width=132) (actual time=51.440..51.453 rows=239 loops=1)

  • Sort Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
  • Sort Method: quicksort Memory: 43kB
545. 3.120 51.056 ↓ 1.8 239 1

Hash Right Join (cost=2,236.23..2,608.74 rows=136 width=132) (actual time=50.774..51.056 rows=239 loops=1)

  • Hash Cond: ((st_5.sernr = r_11.sernr) AND (st_5.comp_part = r_11.comp_part))
  • Join Filter: (NOT (st_5.alt_code IS DISTINCT FROM r_11.alt_code))
546. 2.148 2.148 ↓ 3.8 23,872 1

CTE Scan on so_rep st_5 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.148 rows=23,872 loops=1)

547. 0.071 45.788 ↓ 1.8 239 1

Hash (cost=2,234.19..2,234.19 rows=136 width=100) (actual time=45.788..45.788 rows=239 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
548. 2.553 45.717 ↓ 1.8 239 1

Hash Join (cost=2,073.50..2,234.19 rows=136 width=100) (actual time=40.607..45.717 rows=239 loops=1)

  • Hash Cond: (r_11.sernr = sh_6.sernr)
549. 2.580 2.580 ↓ 4.2 29,564 1

CTE Scan on gr_so_rows r_11 (cost=0.00..142.04 rows=7,102 width=100) (actual time=0.001..2.580 rows=29,564 loops=1)

550. 0.012 40.584 ↑ 9.1 15 1

Hash (cost=2,071.80..2,071.80 rows=136 width=4) (actual time=40.584..40.584 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
551. 40.572 40.572 ↑ 9.1 15 1

Seq Scan on so_header sh_6 (cost=0.00..2,071.80 rows=136 width=4) (actual time=0.903..40.572 rows=15 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (master_sernr IS NULL) AND (NOT so_has_co_ref(sernr)))
  • Rows Removed by Filter: 7093
552. 0.068 49.101 ↑ 2.4 28 1

HashAggregate (cost=5,576.35..5,577.17 rows=66 width=96) (actual time=49.091..49.101 rows=28 loops=1)

  • Group Key: a_2.component_part, a_2.alt_code
553.          

CTE nodes_cte

554. 0.038 48.980 ↓ 1.7 109 1

Recursive Union (cost=2,962.83..5,574.53 rows=66 width=138) (actual time=48.483..48.980 rows=109 loops=1)

555. 0.016 48.502 ↓ 16.0 96 1

Subquery Scan on "*SELECT* 1" (cost=2,962.83..2,962.90 rows=6 width=138) (actual time=48.482..48.502 rows=96 loops=1)

556. 0.061 48.486 ↓ 16.0 96 1

Sort (cost=2,962.83..2,962.84 rows=6 width=142) (actual time=48.481..48.486 rows=96 loops=1)

  • Sort Key: r_10.rowno
  • Sort Method: quicksort Memory: 38kB
557.          

CTE base

558. 0.094 47.164 ↑ 1.9 72 1

GroupAggregate (cost=2,613.46..2,625.70 rows=136 width=132) (actual time=47.074..47.164 rows=72 loops=1)

  • Group Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
559. 0.129 47.070 ↑ 1.9 72 1

Sort (cost=2,613.46..2,613.80 rows=136 width=132) (actual time=47.064..47.070 rows=72 loops=1)

  • Sort Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
  • Sort Method: quicksort Memory: 30kB
560. 3.017 46.941 ↑ 1.9 72 1

Hash Right Join (cost=2,236.14..2,608.64 rows=136 width=132) (actual time=46.687..46.941 rows=72 loops=1)

  • Hash Cond: ((st_4.sernr = r_1_1.sernr) AND (st_4.comp_part = r_1_1.comp_part))
  • Join Filter: (NOT (st_4.alt_code IS DISTINCT FROM r_1_1.alt_code))
561. 2.132 2.132 ↓ 3.8 23,872 1

CTE Scan on so_rep st_4 (cost=0.00..124.08 rows=6,204 width=100) (actual time=0.001..2.132 rows=23,872 loops=1)

562. 0.030 41.792 ↑ 1.9 72 1

Hash (cost=2,234.10..2,234.10 rows=136 width=100) (actual time=41.791..41.792 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
563. 0.702 41.762 ↑ 1.9 72 1

Hash Join (cost=2,073.50..2,234.10 rows=136 width=100) (actual time=38.210..41.762 rows=72 loops=1)

  • Hash Cond: (r_1_1.sernr = sh_4.sernr)
564. 2.943 2.943 ↓ 1.4 9,891 1

CTE Scan on gr_so_rows r_1_1 (cost=0.00..142.04 rows=7,066 width=100) (actual time=0.002..2.943 rows=9,891 loops=1)

  • Filter: (alt_code IS NOT NULL)
  • Rows Removed by Filter: 19673
565. 0.010 38.117 ↑ 9.1 15 1

Hash (cost=2,071.80..2,071.80 rows=136 width=4) (actual time=38.117..38.117 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
566. 38.107 38.107 ↑ 9.1 15 1

Seq Scan on so_header sh_4 (cost=0.00..2,071.80 rows=136 width=4) (actual time=0.823..38.107 rows=15 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL) AND (master_sernr IS NULL) AND (NOT so_has_co_ref(sernr)))
  • Rows Removed by Filter: 7093
567. 0.093 48.425 ↓ 16.0 96 1

Nested Loop Left Join (cost=0.84..337.05 rows=6 width=142) (actual time=47.162..48.425 rows=96 loops=1)

568. 0.052 48.236 ↓ 16.0 96 1

Nested Loop Left Join (cost=0.56..335.17 rows=6 width=79) (actual time=47.143..48.236 rows=96 loops=1)

569. 0.069 47.704 ↓ 16.0 96 1

Nested Loop (cost=0.29..333.29 rows=6 width=75) (actual time=47.121..47.704 rows=96 loops=1)

570. 47.203 47.203 ↑ 1.9 72 1

CTE Scan on base (cost=0.00..2.72 rows=136 width=96) (actual time=47.077..47.203 rows=72 loops=1)

571. 0.432 0.432 ↑ 6.0 1 72

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows r_10 (cost=0.29..2.37 rows=6 width=43) (actual time=0.006..0.006 rows=1 loops=72)

  • Index Cond: (part_no = base.part_no)
  • Filter: (base.alt_code = alternate)
572. 0.480 0.480 ↑ 1.0 1 96

Index Scan using unique_structure_alternatine on structure_alt sap (cost=0.28..0.30 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=96)

  • Index Cond: (part_no = r_10.part_no)
  • Filter: (NOT (code IS DISTINCT FROM r_10.alternate))
573. 0.096 0.096 ↓ 0.0 0 96

Index Scan using unique_structure_alternatine on structure_alt sa_1 (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=96)

  • Index Cond: ((part_no = r_10.component_part) AND (code = r_10.alt_code))
574. 0.004 0.440 ↑ 1.0 6 2

Subquery Scan on "*SELECT* 2_1" (cost=261.02..261.10 rows=6 width=138) (actual time=0.219..0.220 rows=6 loops=2)

575. 0.018 0.436 ↑ 1.0 6 2

Sort (cost=261.02..261.04 rows=6 width=142) (actual time=0.218..0.218 rows=6 loops=2)

  • Sort Key: c_2.rowno
  • Sort Method: quicksort Memory: 25kB
576. 0.009 0.418 ↑ 1.0 6 2

Nested Loop Left Join (cost=0.56..260.94 rows=6 width=142) (actual time=0.039..0.209 rows=6 loops=2)

577. 0.059 0.396 ↑ 1.0 6 2

Nested Loop (cost=0.29..259.08 rows=6 width=83) (actual time=0.031..0.198 rows=6 loops=2)

578. 0.010 0.010 ↑ 1.1 54 2

WorkTable Scan on nodes_cte p (cost=0.00..1.20 rows=60 width=104) (actual time=0.001..0.005 rows=54 loops=2)

579. 0.327 0.327 ↓ 0.0 0 109

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows c_2 (cost=0.29..4.24 rows=6 width=43) (actual time=0.003..0.003 rows=0 loops=109)

  • Index Cond: (part_no = p.component_part)
  • Filter: (p.alt_code = alternate)
580. 0.013 0.013 ↓ 0.0 0 13

Index Scan using unique_structure_alternatine on structure_alt sa_2 (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=13)

  • Index Cond: ((part_no = c_2.component_part) AND (code = c_2.alt_code))
581. 49.033 49.033 ↓ 1.7 109 1

CTE Scan on nodes_cte a_2 (cost=0.00..1.32 rows=66 width=96) (actual time=48.485..49.033 rows=109 loops=1)

582.          

CTE il

583. 8.955 9.526 ↓ 2.5 716 1

HashAggregate (cost=53.08..131.38 rows=290 width=47) (actual time=1.167..9.526 rows=716 loops=1)

  • Group Key: t_3.artcode, t_3.alt_code
584. 0.398 0.571 ↓ 3.0 883 1

Hash Left Join (cost=1.29..50.90 rows=290 width=20) (actual time=0.027..0.571 rows=883 loops=1)

  • Hash Cond: (t_3.warehouse = w.code)
  • Filter: ((NOT COALESCE(w.is_sale_prohibited, false)) AND (NOT COALESCE(w.report_warehouse, false)))
  • Rows Removed by Filter: 275
585. 0.161 0.161 ↑ 1.0 1,158 1

Seq Scan on inv_live t_3 (cost=0.00..45.58 rows=1,158 width=24) (actual time=0.005..0.161 rows=1,158 loops=1)

586. 0.006 0.012 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=8) (actual time=0.012..0.012 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
587. 0.006 0.006 ↑ 1.0 13 1

Seq Scan on warehouses w (cost=0.00..1.13 rows=13 width=8) (actual time=0.002..0.006 rows=13 loops=1)

588.          

CTE l

589. 1.714 819.760 ↓ 1.1 2,346 1

Merge Left Join (cost=341.17..624.74 rows=2,183 width=228) (actual time=811.993..819.760 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = i.artcode)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM i.alt_code))
590. 0.903 806.182 ↓ 1.1 2,346 1

Merge Left Join (cost=323.51..540.77 rows=2,183 width=228) (actual time=800.188..806.182 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = p_1.artcode)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM p_1.alt_code))
591. 0.963 802.228 ↓ 1.1 2,346 1

Merge Left Join (cost=213.68..382.21 rows=2,183 width=164) (actual time=799.005..802.228 rows=2,346 loops=1)

  • Merge Cond: (a_3.code = tt.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM tt.alt_code))
592. 0.474 478.991 ↑ 1.0 2,182 1

Merge Left Join (cost=176.40..231.18 rows=2,183 width=132) (actual time=476.752..478.991 rows=2,182 loops=1)

  • Merge Cond: (a_3.code = mt2.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM mt2.alt_code))
593. 1.477 474.886 ↑ 1.0 2,182 1

Merge Left Join (cost=176.37..225.49 rows=2,183 width=132) (actual time=473.132..474.886 rows=2,182 loops=1)

  • Merge Cond: (a_3.code = mt.part_no)
  • Join Filter: (NOT (a_3.alt_code IS DISTINCT FROM mt.alt_code))
594. 6.357 8.841 ↑ 1.0 2,182 1

Sort (cost=164.73..170.19 rows=2,183 width=100) (actual time=8.588..8.841 rows=2,182 loops=1)

  • Sort Key: a_3.code
  • Sort Method: quicksort Memory: 250kB
595. 2.484 2.484 ↑ 1.0 2,182 1

CTE Scan on a a_3 (cost=0.00..43.66 rows=2,183 width=100) (actual time=0.983..2.484 rows=2,182 loops=1)

596. 0.355 464.568 ↓ 2.1 428 1

Sort (cost=11.64..12.14 rows=200 width=96) (actual time=464.537..464.568 rows=428 loops=1)

  • Sort Key: mt.part_no
  • Sort Method: quicksort Memory: 55kB
597. 464.213 464.213 ↓ 2.1 428 1

CTE Scan on mt (cost=0.00..4.00 rows=200 width=96) (actual time=462.276..464.213 rows=428 loops=1)

598. 0.013 3.631 ↓ 57.0 57 1

Materialize (cost=0.03..0.04 rows=1 width=64) (actual time=3.618..3.631 rows=57 loops=1)

599. 0.039 3.618 ↓ 57.0 57 1

Sort (cost=0.03..0.04 rows=1 width=64) (actual time=3.614..3.618 rows=57 loops=1)

  • Sort Key: mt2.part_no
  • Sort Method: quicksort Memory: 29kB
600. 3.579 3.579 ↓ 57.0 57 1

CTE Scan on mt2 (cost=0.00..0.02 rows=1 width=64) (actual time=3.506..3.579 rows=57 loops=1)

601. 0.442 322.274 ↑ 1.9 301 1

Sort (cost=37.27..38.69 rows=567 width=96) (actual time=322.249..322.274 rows=301 loops=1)

  • Sort Key: tt.part_no
  • Sort Method: quicksort Memory: 45kB
602. 321.832 321.832 ↑ 1.9 301 1

CTE Scan on tt (cost=0.00..11.34 rows=567 width=96) (actual time=220.871..321.832 rows=301 loops=1)

603. 0.061 3.051 ↓ 2.9 169 1

Materialize (cost=109.83..142.17 rows=58 width=78) (actual time=1.180..3.051 rows=169 loops=1)

604. 1.894 2.990 ↓ 1.4 83 1

GroupAggregate (cost=109.83..141.44 rows=58 width=142) (actual time=1.175..2.990 rows=83 loops=1)

  • Group Key: p_1.artcode, p_1.alt_code
605. 0.166 1.096 ↓ 1.6 94 1

Sort (cost=109.83..109.98 rows=58 width=34) (actual time=1.083..1.096 rows=94 loops=1)

  • Sort Key: p_1.artcode, p_1.alt_code
  • Sort Method: quicksort Memory: 32kB
606. 0.042 0.930 ↓ 1.6 94 1

Hash Join (cost=19.06..108.14 rows=58 width=34) (actual time=0.297..0.930 rows=94 loops=1)

  • Hash Cond: (p_1.sernr = h_10.sernr)
607. 0.760 0.760 ↑ 4.1 181 1

Seq Scan on po_rows p_1 (cost=0.00..87.10 rows=744 width=38) (actual time=0.048..0.760 rows=181 loops=1)

  • Filter: ((closed IS NULL) AND (qty > qty_delivered))
  • Rows Removed by Filter: 2467
608. 0.008 0.128 ↑ 1.0 36 1

Hash (cost=18.61..18.61 rows=36 width=4) (actual time=0.128..0.128 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
609. 0.120 0.120 ↑ 1.0 36 1

Seq Scan on po_header h_10 (cost=0.00..18.61 rows=36 width=4) (actual time=0.011..0.120 rows=36 loops=1)

  • Filter: ((confirmed IS NOT NULL) AND (delivered IS NULL))
  • Rows Removed by Filter: 435
610. 1.853 11.864 ↓ 3.0 874 1

Sort (cost=17.66..18.39 rows=290 width=96) (actual time=11.801..11.864 rows=874 loops=1)

  • Sort Key: i.artcode
  • Sort Method: quicksort Memory: 76kB
611. 10.011 10.011 ↓ 2.5 716 1

CTE Scan on il i (cost=0.00..5.80 rows=290 width=96) (actual time=1.169..10.011 rows=716 loops=1)

612.          

CTE e

613. 3.687 824.429 ↓ 10.0 2,182 1

HashAggregate (cost=92.78..97.68 rows=218 width=260) (actual time=822.538..824.429 rows=2,182 loops=1)

  • Group Key: l.part_no, l.alt_code, l.mt_qty, l.po_qty, l.inv_qty, l.minlevel, l.source
614. 820.742 820.742 ↓ 1.1 2,346 1

CTE Scan on l (cost=0.00..43.66 rows=2,183 width=228) (actual time=811.995..820.742 rows=2,346 loops=1)

615.          

CTE p2

616. 0.000 30.479 ↓ 0.0 0 1

Group (cost=6,885.50..6,889.07 rows=476 width=13) (actual time=30.479..30.479 rows=0 loops=1)

  • Group Key: r_12.component_part, r_12.alt_code
617.          

Initplan (for Group)

618. 0.000 0.000 ↓ 0.0 0

CTE Scan on _session_id (cost=0.00..0.02 rows=1 width=8) (never executed)

619. 0.034 0.034 ↑ 1.0 1 1

CTE Scan on _update_code (cost=0.00..0.02 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=1)

620. 0.006 30.478 ↓ 0.0 0 1

Sort (cost=6,885.46..6,886.65 rows=476 width=13) (actual time=30.478..30.478 rows=0 loops=1)

  • Sort Key: r_12.component_part, r_12.alt_code
  • Sort Method: quicksort Memory: 25kB
621. 0.001 30.472 ↓ 0.0 0 1

Nested Loop (cost=0.29..6,864.29 rows=476 width=13) (actual time=30.472..30.472 rows=0 loops=1)

622. 30.471 30.471 ↓ 0.0 0 1

Seq Scan on co_tool_data ct (cost=0.00..6,428.66 rows=322 width=15) (actual time=30.471..30.471 rows=0 loops=1)

  • Filter: ((update_code = $43) AND (session_id = ($42)::text))
  • Rows Removed by Filter: 138183
623. 0.000 0.000 ↓ 0.0 0

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows r_12 (cost=0.29..1.29 rows=6 width=34) (never executed)

  • Index Cond: (part_no = ct.comp_part)
  • Filter: (NOT (ct.alt_code IS DISTINCT FROM alternate))
624.          

CTE cr_data

625. 10.758 934.330 ↓ 1.2 12,414 1

Merge Left Join (cost=6,837.99..7,325.54 rows=10,629 width=338) (actual time=917.997..934.330 rows=12,414 loops=1)

  • Merge Cond: (c_4.comp_part = b1.comp_part)
  • Join Filter: (NOT (c_4.alt_code IS DISTINCT FROM b1.alt_code))
626.          

Initplan (for Merge Left Join)

627. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on up_id (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

628. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _comp_part (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

629. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _part_no (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

630. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on up_id up_id_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

631. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

632. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

633. 0.000 0.000 ↓ 0.0 0

CTE Scan on _main (cost=0.00..0.02 rows=1 width=1) (never executed)

634. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _main _main_1 (cost=0.00..0.02 rows=1 width=1) (actual time=0.002..0.002 rows=1 loops=1)

635. 0.000 0.000 ↓ 0.0 0

CTE Scan on _comp_part _comp_part_2 (cost=0.00..0.02 rows=1 width=32) (never executed)

636. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

637. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

638. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

639. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

640. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on m_depth (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

641. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

642. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _qty (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

643. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _main _main_2 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

644. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _comp_part _comp_part_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

645. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on up_id up_id_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

646. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on _part_no _part_no_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

647. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on _session_id _session_id_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

648. 3.872 87.579 ↓ 1.2 12,414 1

Merge Left Join (cost=6,824.74..6,920.68 rows=10,629 width=162) (actual time=82.739..87.579 rows=12,414 loops=1)

  • Merge Cond: (c_4.comp_part = p_2.component_part)
  • Join Filter: (NOT (c_4.alt_code IS DISTINCT FROM p_2.alt_code))
649. 19.899 53.213 ↓ 1.2 12,414 1

Sort (cost=6,794.06..6,820.63 rows=10,629 width=130) (actual time=52.240..53.213 rows=12,414 loops=1)

  • Sort Key: c_4.comp_part
  • Sort Method: quicksort Memory: 3618kB
650. 33.314 33.314 ↓ 1.2 12,414 1

Seq Scan on co_tool_data c_4 (cost=0.00..6,083.20 rows=10,629 width=130) (actual time=2.170..33.314 rows=12,414 loops=1)

  • Filter: (session_id = ($67)::text)
  • Rows Removed by Filter: 125769
651. 0.014 30.494 ↓ 0.0 0 1

Sort (cost=30.69..31.88 rows=476 width=64) (actual time=30.494..30.494 rows=0 loops=1)

  • Sort Key: p_2.component_part
  • Sort Method: quicksort Memory: 25kB
652. 30.480 30.480 ↓ 0.0 0 1

CTE Scan on p2 p_2 (cost=0.00..9.52 rows=476 width=64) (actual time=30.480..30.480 rows=0 loops=1)

653. 7.619 835.975 ↓ 57.3 12,482 1

Sort (cost=12.83..13.37 rows=218 width=160) (actual time=835.218..835.975 rows=12,482 loops=1)

  • Sort Key: b1.comp_part
  • Sort Method: quicksort Memory: 264kB
654. 828.356 828.356 ↓ 10.0 2,182 1

CTE Scan on b1 (cost=0.00..4.36 rows=218 width=160) (actual time=822.546..828.356 rows=2,182 loops=1)

655.          

CTE base

656. 950.141 950.145 ↓ 6,791.0 6,791 1

CTE Scan on cr_data (cost=0.14..398.73 rows=1 width=648) (actual time=919.855..950.145 rows=6,791 loops=1)

  • Filter: ((depth >= $69) AND (depth = $70) AND CASE WHEN (($72 = $73) AND (NOT $74)) THEN (comp_part = $75) ELSE true END AND (session_id = ($71)::text))
  • Rows Removed by Filter: 5623
657.          

Initplan (for CTE Scan)

658. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

659. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on counter (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

660. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _session_id _session_id_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

661. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on counter counter_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

662. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on m_depth m_depth_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

663. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _first (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

664. 0.000 0.000 ↓ 0.0 0

CTE Scan on _comp_part _comp_part_5 (cost=0.00..0.02 rows=1 width=32) (never executed)

665.          

CTE base_extra

666. 24.307 1,418.910 ↓ 4,438.5 8,877 1

Unique (cost=253.47..253.66 rows=2 width=648) (actual time=1,391.361..1,418.910 rows=8,877 loops=1)

667. 342.371 1,394.603 ↓ 8,731.5 17,463 1

Sort (cost=253.47..253.48 rows=2 width=648) (actual time=1,391.359..1,394.603 rows=17,463 loops=1)

  • Sort Key: b.id, b.session_id, b.part_no, b.alternate, b.comp_part, b.alt_code, b.mt, b.qty_pa, b.source, b.needed_qty, b.to_prod, b.to_purch, b.to_buy, b.co_rows_id, b.qty_from_parent, b.depth, b.man, b.changed_by_user, b.is_related, b.qty_to_child, b.edit_row, b.level, b.up9, b.up_null, b.comp_equal_up_part, b.comp_equal_up_comp_part, b.depth_smaller, b.depth_bigger, b.up_qty, b._main, b.up_comp, b.up_id, b.up_part, b._need, b.minlevel, b.update_code, b.parent_update
  • Sort Method: external sort Disk: 3544kB
668. 1.484 1,052.232 ↓ 8,731.5 17,463 1

Append (cost=0.00..253.46 rows=2 width=648) (actual time=919.857..1,052.232 rows=17,463 loops=1)

669. 952.182 952.182 ↓ 6,791.0 6,791 1

CTE Scan on base b (cost=0.00..0.02 rows=1 width=648) (actual time=919.856..952.182 rows=6,791 loops=1)

670. 93.631 98.566 ↓ 10,672.0 10,672 1

Hash Join (cost=0.06..253.42 rows=1 width=648) (actual time=3.443..98.566 rows=10,672 loops=1)

  • Hash Cond: (c_5.comp_part = b_1.comp_part)
  • Join Filter: ((NOT (b_1.alt_code IS DISTINCT FROM c_5.alt_code)) AND CASE WHEN (NOT $77) THEN (b_1.depth <> c_5.depth) ELSE (b_1.id <> c_5.id) END)
  • Rows Removed by Join Filter: 760157
671.          

Initplan (for Hash Join)

672. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _main _main_3 (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

673. 1.653 1.653 ↓ 1.2 12,414 1

CTE Scan on cr_data c_5 (cost=0.00..212.58 rows=10,629 width=648) (actual time=0.001..1.653 rows=12,414 loops=1)

674. 1.354 3.281 ↓ 6,791.0 6,791 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=3.281..3.281 rows=6,791 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 469kB
675. 1.927 1.927 ↓ 6,791.0 6,791 1

CTE Scan on base b_1 (cost=0.00..0.02 rows=1 width=76) (actual time=0.002..1.927 rows=6,791 loops=1)

  • Filter: (part_no <> comp_part)
Planning time : 24.695 ms
Execution time : 1,437.614 ms