explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2gNG

Settings
# exclusive inclusive rows x rows loops node
1. 6.127 1,745.862 ↓ 12,481.0 12,481 1

Sort (cost=102,143.14..102,143.15 rows=1 width=307) (actual time=1,745.322..1,745.862 rows=12,481 loops=1)

  • Sort Key: b.id
  • Sort Method: quicksort Memory: 2139kB
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.001..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 _qty

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

10.          

CTE force_prod

11. 0.001 0.001 ↑ 1.0 1 1

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

12.          

CTE force_purch

13. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=1) (never executed)

14.          

CTE up_id

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

16.          

CTE m_depth

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

18.          

CTE counter

19. 0.001 0.001 ↑ 1.0 1 1

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

20.          

CTE _main

21. 0.001 0.001 ↑ 1.0 1 1

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

22.          

CTE _first

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

24.          

CTE _update_code

25. 0.021 0.021 ↑ 1.0 1 1

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

26.          

CTE b1

27. 1.202 1,176.030 ↓ 10.0 2,182 1

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

28. 1,174.828 1,174.828 ↓ 10.0 2,182 1

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

29.          

CTE a

30. 0.703 2.170 ↑ 1.0 2,182 1

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

  • Hash Cond: (sa.part_no = a.code)
31. 0.482 0.482 ↑ 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.482 rows=1,509 loops=1)

32. 0.421 0.985 ↑ 1.0 2,182 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 135kB
33. 0.564 0.564 ↑ 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.564 rows=2,182 loops=1)

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

CTE so_rep

35. 4.044 437.328 ↓ 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=373.664..437.328 rows=23,872 loops=1)

36. 39.320 433.284 ↓ 3.8 23,872 1

GroupAggregate (cost=9,133.31..9,855.02 rows=6,204 width=150) (actual time=373.663..433.284 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)
37. 119.176 393.964 ↑ 1.0 35,534 1

Sort (cost=9,133.31..9,222.58 rows=35,706 width=525) (actual time=373.642..393.964 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
38. 269.375 274.788 ↑ 1.0 35,534 1

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

39. 5.413 5.413 ↑ 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..5.413 rows=35,534 loops=1)

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

41. 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
42.          

CTE gr_so_rows

43. 34.047 37.132 ↓ 4.2 29,564 1

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

  • Group Key: r_1.sernr, r_1.comp_part, r_1.alt_code
44. 3.085 3.085 ↓ 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..3.085 rows=29,756 loops=1)

45.          

CTE st_total

46. 0.503 14.077 ↑ 1.1 6,211 1

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

47. 5.870 13.574 ↑ 1.1 6,211 1

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

  • Group Key: h.sernr
48. 2.754 7.704 ↓ 1.0 8,192 1

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

  • Hash Cond: (rec.sernr = h.sernr)
49. 0.914 0.914 ↓ 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.914 rows=8,192 loops=1)

50. 1.319 4.036 ↓ 1.0 7,108 1

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

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

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

52.          

CTE to_deliver

53. 0.224 40.153 ↓ 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=38.403..40.153 rows=1,157 loops=1)

54. 1.466 39.929 ↓ 2.9 1,157 1

GroupAggregate (cost=6,209.15..6,276.05 rows=400 width=361) (actual time=38.399..39.929 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
55. 2.216 38.463 ↓ 1.3 1,485 1

Sort (cost=6,209.15..6,212.00 rows=1,141 width=297) (actual time=38.381..38.463 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
56. 0.366 36.247 ↓ 1.3 1,485 1

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

57. 1.005 35.881 ↓ 1.3 1,485 1

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

  • Sort Key: r_2.sernr, r_2.rowno
  • Sort Method: quicksort Memory: 426kB
58. 3.994 34.876 ↓ 1.3 1,485 1

HashAggregate (cost=6,061.89..6,079.01 rows=1,141 width=382) (actual time=33.765..34.876 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
59. 12.138 30.882 ↓ 1.3 1,485 1

Nested Loop (cost=624.98..6,024.81 rows=1,141 width=290) (actual time=7.912..30.882 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 17) = 0) THEN true ELSE (cor.id IS NOT NULL) END ELSE (r_2.rowno > 0) END
  • Rows Removed by Join Filter: 1149
60. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (register = 'Invoice'::text)
  • Rows Removed by Filter: 4
61. 0.968 15.280 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.parent = cor.id)
62. 1.524 12.957 ↓ 1.2 2,634 1

Hash Left Join (cost=490.88..566.60 rows=2,282 width=290) (actual time=6.491..12.957 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))
63. 0.888 10.526 ↓ 1.2 2,634 1

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

  • Hash Cond: (d.sernr = dh.sernr)
64. 1.346 9.252 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.artcode = a_1.code)
65. 0.938 6.547 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.sernr = h_1.sernr)
66. 1.357 5.418 ↓ 1.1 2,634 1

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

  • Hash Cond: (d.co_rows_id = r_2.id)
67. 0.553 2.245 ↓ 1.0 2,567 1

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

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

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

69. 0.624 1.690 ↓ 1.0 2,567 1

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

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

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

71. 0.816 1.816 ↓ 1.0 2,306 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 286kB
72. 1.000 1.000 ↓ 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.005..1.000 rows=2,306 loops=1)

  • Filter: (closed IS NULL)
  • Rows Removed by Filter: 380
73. 0.059 0.191 ↑ 1.0 350 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
74. 0.132 0.132 ↑ 1.0 350 1

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

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
75. 0.625 1.359 ↑ 1.0 2,212 1

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

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

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

77. 0.129 0.386 ↑ 1.0 539 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
78. 0.257 0.257 ↑ 1.0 539 1

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

79. 0.518 0.907 ↑ 1.0 1,509 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 126kB
80. 0.389 0.389 ↑ 1.0 1,509 1

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

81. 0.675 1.355 ↓ 1.0 2,686 1

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

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

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

83.          

SubPlan (for Nested Loop)

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

85. 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)
86.          

CTE mt

87. 0.671 620.040 ↓ 2.1 428 1

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

  • Group Key: r_3.artcode, r_3.alt_code
88. 0.616 619.369 ↑ 1.1 792 1

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

  • Sort Key: r_3.artcode, r_3.alt_code
89. 0.001 1.533 ↓ 0.0 0 1

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

  • Group Key: r_3.artcode, r_3.alt_code
90. 0.005 1.532 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (r_3.sernr = h_2.sernr)
92. 0.006 1.311 ↓ 0.0 0 1

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

  • Hash Cond: (rows_1.id = r_3.id)
93. 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
94. 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
95. 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)

96. 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
97. 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
98. 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 19) = 0) THEN true ELSE (cor_1.id IS NOT NULL) END ELSE (r_4.rowno > 0) END
99. 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)
100. 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)
101. 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))
102. 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)
103. 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)
104. 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)
105. 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)
106. 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)

107. 0.000 0.000 ↓ 0.0 0

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

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

109. 0.000 0.000 ↓ 0.0 0

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

110. 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)
111. 0.000 0.000 ↓ 0.0 0

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

112. 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)
113. 0.000 0.000 ↓ 0.0 0

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

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

115. 0.000 0.000 ↓ 0.0 0

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

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

117. 0.000 0.000 ↓ 0.0 0

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

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

119.          

SubPlan (for Nested Loop)

120. 0.000 0.000 ↓ 0.0 0

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

121. 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)
122. 0.001 1.305 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
123. 1.304 1.304 ↓ 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=1.304..1.304 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
124. 0.050 0.208 ↑ 1.0 350 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
125. 0.158 0.158 ↑ 1.0 350 1

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

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
126. 0.033 60.409 ↑ 1.5 184 1

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

127. 0.347 60.376 ↑ 1.5 184 1

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

  • Group Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
128. 0.405 60.029 ↑ 1.5 185 1

Sort (cost=526.02..526.73 rows=283 width=107) (actual time=60.003..60.029 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
129. 0.617 59.624 ↑ 1.5 185 1

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

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

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

131. 0.069 43.425 ↑ 1.5 185 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
132. 0.152 43.356 ↑ 1.5 185 1

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

133. 0.051 42.834 ↑ 1.7 185 1

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

  • Hash Cond: (r_5.sernr = h_4.sernr)
134. 0.375 42.521 ↑ 1.7 185 1

Hash Right Join (cost=84.64..93.69 rows=320 width=57) (actual time=42.317..42.521 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
135. 40.439 40.439 ↓ 2.9 1,157 1

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

136. 0.399 1.707 ↓ 2.0 1,295 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
137. 1.308 1.308 ↓ 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.051..1.308 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
138. 0.073 0.262 ↑ 1.0 350 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
139. 0.189 0.189 ↑ 1.0 350 1

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

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
140. 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))
141. 0.090 520.526 ↓ 6.7 497 1

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

142. 1.217 520.436 ↓ 6.7 497 1

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

  • Group Key: sr.comp_part, sr.alt_code, st_1.qty
143. 2.399 519.219 ↓ 9.4 696 1

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

  • Sort Key: sr.comp_part, sr.alt_code, st_1.qty
  • Sort Method: quicksort Memory: 76kB
144. 0.887 516.820 ↓ 9.4 696 1

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

145. 7.811 514.541 ↓ 9.4 696 1

Hash Right Join (cost=562.36..826.16 rows=74 width=132) (actual time=509.067..514.541 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))
146. 453.668 453.668 ↓ 3.8 23,872 1

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

147. 0.205 53.062 ↓ 9.4 696 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
148. 2.847 52.857 ↓ 9.4 696 1

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

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

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

150. 0.012 3.097 ↑ 1.7 43 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
151. 0.035 3.085 ↑ 1.7 43 1

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

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

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

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
153. 0.188 1.131 ↓ 2.0 1,295 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
154. 0.943 0.943 ↓ 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.040..0.943 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
155. 1.392 1.392 ↑ 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.002..0.002 rows=1 loops=696)

  • Index Cond: (sernr = r_6.sernr)
  • Filter: (confirmed IS NOT NULL)
156. 0.184 8.819 ↑ 2.0 71 1

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

  • Sort Key: ""*SELECT* 4"".part_no, ""*SELECT* 4"".alt_code
  • Sort Method: quicksort Memory: 30kB
157. 0.014 8.635 ↑ 2.0 71 1

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

158. 0.058 8.621 ↑ 2.0 71 1

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

  • Group Key: st_2.received, sh_2.sernr
159. 0.072 8.563 ↑ 2.0 71 1

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

  • Sort Key: st_2.received, sh_2.sernr
  • Sort Method: quicksort Memory: 30kB
160. 0.042 8.491 ↑ 2.0 71 1

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

161. 1.087 8.236 ↑ 2.0 71 1

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

  • Hash Cond: (st_2.sernr = sh_2.sernr)
162. 1.014 1.014 ↑ 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..1.014 rows=6,211 loops=1)

163. 0.032 6.135 ↑ 2.0 71 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
164. 0.231 6.103 ↑ 2.0 71 1

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

  • Hash Cond: (t_1.id = r_7.id)
165. 0.194 0.194 ↓ 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.194 rows=1,157 loops=1)

166. 0.038 5.678 ↑ 2.0 71 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
167. 0.070 5.640 ↑ 2.0 71 1

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

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

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

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
169. 0.403 2.137 ↓ 2.0 1,295 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
170. 1.734 1.734 ↓ 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.059..1.734 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
171. 0.213 0.213 ↑ 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.003..0.003 rows=1 loops=71)

  • Index Cond: (sernr = r_7.sernr)
  • Filter: (confirmed IS NOT NULL)
172. 0.008 27.466 ↑ 3.6 40 1

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

173. 0.119 27.458 ↑ 3.6 40 1

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

  • Group Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
174. 0.205 27.339 ↑ 1.5 94 1

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

  • Sort Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
  • Sort Method: quicksort Memory: 30kB
175. 0.039 27.134 ↑ 1.5 94 1

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

176. 6.442 26.813 ↑ 1.5 94 1

Hash Right Join (cost=565.13..937.64 rows=144 width=132) (actual time=26.450..26.813 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))
177. 4.427 4.427 ↓ 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..4.427 rows=23,872 loops=1)

178. 0.054 15.944 ↑ 1.5 94 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
179. 0.158 15.890 ↑ 1.5 94 1

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

  • Hash Cond: (sh_3.master_sernr = r_8.so_ref)
180. 5.451 13.742 ↓ 1.3 1,061 1

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

  • Hash Cond: (sr_1.sernr = sh_3.sernr)
181. 5.181 5.181 ↓ 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.002..5.181 rows=29,564 loops=1)

182. 0.046 3.110 ↑ 5.5 148 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
183. 3.064 3.064 ↑ 5.5 148 1

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

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6960
184. 0.383 1.990 ↓ 2.0 1,295 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
185. 1.607 1.607 ↓ 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.059..1.607 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
186. 0.282 0.282 ↑ 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.003..0.003 rows=1 loops=94)

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

CTE mt2

188. 0.107 6.432 ↓ 57.0 57 1

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

  • Group Key: r_9.artcode, r_9.alt_code
189. 0.214 6.325 ↓ 83.0 83 1

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

  • Sort Key: r_9.artcode, r_9.alt_code
  • Sort Method: quicksort Memory: 31kB
190. 0.038 6.111 ↓ 83.0 83 1

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

191. 0.222 5.800 ↓ 91.0 91 1

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

  • Hash Cond: (t_2.id = r_9.id)
192. 0.190 0.190 ↓ 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.190 rows=1,157 loops=1)

193. 0.041 5.388 ↓ 91.0 91 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
194. 1.557 5.347 ↓ 91.0 91 1

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

  • Merge Cond: (r_9.id = r2.parent)
  • Filter: (r2.id IS NULL)
  • Rows Removed by Filter: 1333
195. 2.704 2.704 ↓ 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.064..2.704 rows=1,390 loops=1)

  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NULL))
  • Rows Removed by Filter: 1296
196. 1.086 1.086 ↑ 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.050..1.086 rows=1,334 loops=1)

197. 0.273 0.273 ↑ 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.003..0.003 rows=1 loops=91)

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

CTE tt

199. 0.037 508.298 ↑ 1.9 301 1

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

200. 0.008 345.752 ↑ 10.7 34 1

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

201. 0.072 345.744 ↑ 10.7 34 1

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

  • Group Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
202. 0.092 345.672 ↑ 10.7 34 1

Sort (cost=4,735.24..4,736.15 rows=365 width=86) (actual time=345.669..345.672 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
203. 0.885 345.580 ↑ 10.7 34 1

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

  • Hash Cond: (h_9.sernr = sh_5.sernr)
204. 9.780 23.199 ↑ 1.1 6,211 1

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

  • Group Key: h_9.sernr
205. 4.971 13.419 ↓ 1.0 8,192 1

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

  • Hash Cond: (rec_1.sernr = h_9.sernr)
206. 1.477 1.477 ↓ 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.477 rows=8,192 loops=1)

207. 2.176 6.971 ↓ 1.0 7,108 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 441kB
208. 4.795 4.795 ↓ 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.795 rows=7,108 loops=1)

209. 0.043 321.496 ↑ 10.7 34 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
210. 321.453 321.453 ↑ 10.7 34 1

Seq Scan on so_header sh_5 (cost=0.00..3,841.80 rows=365 width=26) (actual time=2.690..321.453 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
211. 0.040 96.516 ↓ 1.8 239 1

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

212. 0.394 96.476 ↓ 1.8 239 1

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

  • Group Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
213. 0.732 96.082 ↓ 1.8 239 1

Sort (cost=2,613.56..2,613.90 rows=136 width=132) (actual time=96.059..96.082 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
214. 6.561 95.350 ↓ 1.8 239 1

Hash Right Join (cost=2,236.23..2,608.74 rows=136 width=132) (actual time=94.785..95.350 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))
215. 4.252 4.252 ↓ 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..4.252 rows=23,872 loops=1)

216. 0.125 84.537 ↓ 1.8 239 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
217. 5.180 84.412 ↓ 1.8 239 1

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

  • Hash Cond: (r_11.sernr = sh_6.sernr)
218. 5.221 5.221 ↓ 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..5.221 rows=29,564 loops=1)

219. 0.017 74.011 ↑ 9.1 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
220. 73.994 73.994 ↑ 9.1 15 1

Seq Scan on so_header sh_6 (cost=0.00..2,071.80 rows=136 width=4) (actual time=1.478..73.994 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
221. 0.067 65.993 ↑ 2.4 28 1

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

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

CTE nodes_cte

223. 0.028 65.872 ↓ 1.7 109 1

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

224. 0.015 65.394 ↓ 16.0 96 1

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

225. 0.063 65.379 ↓ 16.0 96 1

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

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

CTE base

227. 0.082 64.093 ↑ 1.9 72 1

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

  • Group Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
228. 0.133 64.011 ↑ 1.9 72 1

Sort (cost=2,613.46..2,613.80 rows=136 width=132) (actual time=64.004..64.011 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
229. 3.023 63.878 ↑ 1.9 72 1

Hash Right Join (cost=2,236.14..2,608.64 rows=136 width=132) (actual time=63.632..63.878 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))
230. 2.048 2.048 ↓ 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.000..2.048 rows=23,872 loops=1)

231. 0.030 58.807 ↑ 1.9 72 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
232. 0.713 58.777 ↑ 1.9 72 1

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

  • Hash Cond: (r_1_1.sernr = sh_4.sernr)
233. 2.923 2.923 ↓ 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.003..2.923 rows=9,891 loops=1)

  • Filter: (alt_code IS NOT NULL)
  • Rows Removed by Filter: 19673
234. 0.016 55.141 ↑ 9.1 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
235. 55.125 55.125 ↑ 9.1 15 1

Seq Scan on so_header sh_4 (cost=0.00..2,071.80 rows=136 width=4) (actual time=1.494..55.125 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
236. 0.087 65.316 ↓ 16.0 96 1

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

237. 0.056 65.133 ↓ 16.0 96 1

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

238. 0.035 64.597 ↓ 16.0 96 1

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

239. 64.130 64.130 ↑ 1.9 72 1

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

240. 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.005..0.006 rows=1 loops=72)

  • Index Cond: (part_no = base.part_no)
  • Filter: (base.alt_code = alternate)
241. 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))
242. 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))
243. 0.004 0.450 ↑ 1.0 6 2

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

244. 0.018 0.446 ↑ 1.0 6 2

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

  • Sort Key: c_2.rowno
  • Sort Method: quicksort Memory: 25kB
245. 0.020 0.428 ↑ 1.0 6 2

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

246. 0.071 0.408 ↑ 1.0 6 2

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

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

248. 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)
249. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=13)

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

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

251.          

CTE il

252. 9.126 10.002 ↓ 2.5 716 1

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

  • Group Key: t_3.artcode, t_3.alt_code
253. 0.639 0.876 ↓ 3.0 883 1

Hash Left Join (cost=1.29..50.90 rows=290 width=20) (actual time=0.041..0.876 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
254. 0.220 0.220 ↑ 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.006..0.220 rows=1,158 loops=1)

255. 0.009 0.017 ↑ 1.0 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
256. 0.008 0.008 ↑ 1.0 13 1

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

257.          

CTE l

258. 1.710 1,169.310 ↓ 1.1 2,346 1

Merge Left Join (cost=341.17..624.74 rows=2,183 width=228) (actual time=1,161.613..1,169.310 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))
259. 0.892 1,155.318 ↓ 1.1 2,346 1

Merge Left Join (cost=323.51..540.77 rows=2,183 width=228) (actual time=1,149.386..1,155.318 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))
260. 0.951 1,150.531 ↓ 1.1 2,346 1

Merge Left Join (cost=213.68..382.21 rows=2,183 width=164) (actual time=1,147.327..1,150.531 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))
261. 0.473 640.562 ↑ 1.0 2,182 1

Merge Left Join (cost=176.40..231.18 rows=2,183 width=132) (actual time=638.326..640.562 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))
262. 1.488 633.537 ↑ 1.0 2,182 1

Merge Left Join (cost=176.37..225.49 rows=2,183 width=132) (actual time=631.787..633.537 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))
263. 8.139 11.243 ↑ 1.0 2,182 1

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

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

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

265. 0.459 620.806 ↓ 2.1 428 1

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

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

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

267. 0.018 6.552 ↓ 57.0 57 1

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

268. 0.064 6.534 ↓ 57.0 57 1

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

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

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

270. 0.520 509.018 ↑ 1.9 301 1

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

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

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

272. 0.061 3.895 ↓ 2.9 169 1

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

273. 1.907 3.834 ↓ 1.4 83 1

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

  • Group Key: p_1.artcode, p_1.alt_code
274. 0.267 1.927 ↓ 1.6 94 1

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

  • Sort Key: p_1.artcode, p_1.alt_code
  • Sort Method: quicksort Memory: 32kB
275. 0.065 1.660 ↓ 1.6 94 1

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

  • Hash Cond: (p_1.sernr = h_10.sernr)
276. 1.386 1.386 ↑ 4.1 181 1

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

  • Filter: ((closed IS NULL) AND (qty > qty_delivered))
  • Rows Removed by Filter: 2467
277. 0.013 0.209 ↑ 1.0 36 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
278. 0.196 0.196 ↑ 1.0 36 1

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

  • Filter: ((confirmed IS NOT NULL) AND (delivered IS NULL))
  • Rows Removed by Filter: 435
279. 1.842 12.282 ↓ 3.0 874 1

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

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

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

281.          

CTE e

282. 3.410 1,173.701 ↓ 10.0 2,182 1

HashAggregate (cost=92.78..97.68 rows=218 width=260) (actual time=1,172.258..1,173.701 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
283. 1,170.291 1,170.291 ↓ 1.1 2,346 1

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

284.          

CTE st_alt

285. 0.701 0.701 ↑ 1.0 1,509 1

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

286.          

CTE p2

287. 0.000 34.736 ↓ 0.0 0 1

Group (cost=6,735.81..6,739.42 rows=482 width=13) (actual time=34.736..34.736 rows=0 loops=1)

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

Initplan (for Group)

289. 0.000 0.000 ↓ 0.0 0

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

290. 0.022 0.022 ↑ 1.0 1 1

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

291. 0.012 34.735 ↓ 0.0 0 1

Sort (cost=6,735.77..6,736.97 rows=482 width=13) (actual time=34.735..34.735 rows=0 loops=1)

  • Sort Key: r_12.component_part, r_12.alt_code
  • Sort Method: quicksort Memory: 25kB
292. 0.002 34.723 ↓ 0.0 0 1

Nested Loop (cost=0.29..6,714.29 rows=482 width=13) (actual time=34.723..34.723 rows=0 loops=1)

293. 34.721 34.721 ↓ 0.0 0 1

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

  • Filter: ((update_code = $46) AND (session_id = ($45)::text))
  • Rows Removed by Filter: 138183
294. 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))
295.          

CTE cr_data

296. 13.388 1,298.107 ↓ 1.2 12,480 1

Merge Left Join (cost=6,688.42..7,176.81 rows=10,629 width=338) (actual time=1,278.239..1,298.107 rows=12,480 loops=1)

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

Initplan (for Merge Left Join)

298. 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.003..0.004 rows=1 loops=1)

299. 0.003 0.003 ↑ 1.0 1 1

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

300. 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.000..0.001 rows=1 loops=1)

301. 0.005 0.005 ↑ 1.0 1 1

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

302. 0.003 0.003 ↑ 1.0 1 1

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

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

304. 0.000 0.000 ↓ 0.0 0

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

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

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

307. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

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

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

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

311. 0.002 0.002 ↑ 1.0 1 1

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

312. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

313. 0.002 0.002 ↑ 1.0 1 1

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

314. 0.001 0.001 ↑ 1.0 1 1

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

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

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

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

318. 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.001..0.002 rows=1 loops=1)

319. 4.587 99.047 ↓ 1.2 12,480 1

Merge Left Join (cost=6,675.18..6,771.86 rows=10,629 width=162) (actual time=93.372..99.047 rows=12,480 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))
320. 24.307 59.708 ↓ 1.2 12,480 1

Sort (cost=6,644.06..6,670.63 rows=10,629 width=130) (actual time=58.615..59.708 rows=12,480 loops=1)

  • Sort Key: c_4.comp_part
  • Sort Method: quicksort Memory: 3636kB
321. 35.401 35.401 ↓ 1.2 12,480 1

Seq Scan on co_tool_data c_4 (cost=0.00..5,933.20 rows=10,629 width=130) (actual time=0.031..35.401 rows=12,480 loops=1)

  • Filter: (session_id = ($70)::text)
  • Rows Removed by Filter: 125703
322. 0.014 34.752 ↓ 0.0 0 1

Sort (cost=31.12..32.33 rows=482 width=64) (actual time=34.752..34.752 rows=0 loops=1)

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

CTE Scan on p2 p_2 (cost=0.00..9.64 rows=482 width=64) (actual time=34.738..34.738 rows=0 loops=1)

324. 8.686 1,185.644 ↓ 57.6 12,549 1

Sort (cost=12.83..13.37 rows=218 width=160) (actual time=1,184.818..1,185.644 rows=12,549 loops=1)

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

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

326.          

CTE base

327. 1,317.799 1,317.808 ↓ 2,116.0 2,116 1

CTE Scan on cr_data (cost=0.16..425.32 rows=1 width=648) (actual time=1,278.258..1,317.808 rows=2,116 loops=1)

  • Filter: ((depth >= $72) AND CASE WHEN ((depth > 1) AND (NOT edit_row) AND (NOT $79)) THEN (parent_update IS NOT NULL) ELSE true END AND (depth = $73) AND CASE WHEN (($75 = $76) AND (NOT $77)) THEN (comp_part = $78) ELSE true END AND (session_id = ($74)::text))
  • Rows Removed by Filter: 10364
328.          

Initplan (for CTE Scan)

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

330. 0.002 0.002 ↑ 1.0 1 1

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

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

332. 0.001 0.001 ↑ 1.0 1 1

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

333. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

334. 0.002 0.002 ↑ 1.0 1 1

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

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

336. 0.001 0.001 ↑ 1.0 1 1

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

337.          

CTE childs

338. 0.320 12.895 ↑ 1,538.5 26 1

HashAggregate (cost=44,031.13..44,431.13 rows=40,000 width=64) (actual time=12.613..12.895 rows=26 loops=1)

  • Group Key: c_6.part_no, c_6.alternate
339.          

CTE c

340. 0.014 12.554 ↑ 21,670.1 29 1

Recursive Union (cost=292.30..28,320.33 rows=628,432 width=64) (actual time=5.066..12.554 rows=29 loops=1)

341. 0.014 5.070 ↑ 1,062.0 1 1

HashAggregate (cost=292.30..302.92 rows=1,062 width=64) (actual time=5.064..5.070 rows=1 loops=1)

  • Group Key: cr_data_1.part_no, cr_data_1.alternate
342. 5.056 5.056 ↑ 5,314.0 1 1

CTE Scan on cr_data cr_data_1 (cost=0.00..265.73 rows=5,314 width=64) (actual time=0.110..5.056 rows=1 loops=1)

  • Filter: CASE WHEN up9 THEN false ELSE (comp_equal_up_part AND ((co_rows_id = '-9999'::integer) OR (co_rows_id = 0))) END
  • Rows Removed by Filter: 12479
343. 0.114 7.470 ↑ 6,970.8 9 3

Hash Join (cost=386.51..1,544.88 rows=62,737 width=13) (actual time=2.485..2.490 rows=9 loops=3)

  • Hash Cond: (c_5.part_no = sa_3.part_no)
344. 0.006 0.006 ↑ 1,062.0 10 3

WorkTable Scan on c c_5 (cost=0.00..212.40 rows=10,620 width=32) (actual time=0.001..0.002 rows=10 loops=3)

345. 3.502 7.350 ↑ 1.0 8,867 1

Hash (cost=275.67..275.67 rows=8,867 width=32) (actual time=7.350..7.350 rows=8,867 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 683kB
346. 3.848 3.848 ↑ 1.0 8,867 1

Seq Scan on structure_rows sa_3 (cost=0.00..275.67 rows=8,867 width=32) (actual time=0.010..3.848 rows=8,867 loops=1)

347. 12.575 12.575 ↑ 21,670.1 29 1

CTE Scan on c c_6 (cost=0.00..12,568.64 rows=628,432 width=64) (actual time=5.071..12.575 rows=29 loops=1)

348.          

CTE base_extra

349. 31.125 1,382.432 ↓ 6,240.0 12,480 1

Unique (cost=253.47..253.66 rows=2 width=648) (actual time=1,350.137..1,382.432 rows=12,480 loops=1)

350. 14.282 1,351.307 ↓ 6,240.0 12,480 1

Sort (cost=253.47..253.47 rows=2 width=648) (actual time=1,350.136..1,351.307 rows=12,480 loops=1)

  • Sort Key: b_1.id, b_1.session_id, b_1.part_no, b_1.alternate, b_1.comp_part, b_1.alt_code, b_1.mt, b_1.qty_pa, b_1.source, b_1.needed_qty, b_1.to_prod, b_1.to_purch, b_1.to_buy, b_1.co_rows_id, b_1.qty_from_parent, b_1.depth, b_1.man, b_1.changed_by_user, b_1.is_related, b_1.qty_to_child, b_1.edit_row, b_1.level, b_1.up9, b_1.up_null, b_1.comp_equal_up_part, b_1.comp_equal_up_comp_part, b_1.depth_smaller, b_1.depth_bigger, b_1.up_qty, b_1._main, b_1.up_comp, b_1.up_id, b_1.up_part, b_1._need, b_1.minlevel, b_1.update_code, b_1.parent_update
  • Sort Method: quicksort Memory: 3648kB
351. 1.531 1,337.025 ↓ 6,240.0 12,480 1

Append (cost=0.00..253.46 rows=2 width=648) (actual time=1,278.260..1,337.025 rows=12,480 loops=1)

352. 1,318.637 1,318.637 ↓ 2,116.0 2,116 1

CTE Scan on base b_1 (cost=0.00..0.02 rows=1 width=648) (actual time=1,278.259..1,318.637 rows=2,116 loops=1)

353. 12.976 16.857 ↓ 10,364.0 10,364 1

Hash Join (cost=0.05..253.42 rows=1 width=648) (actual time=2.178..16.857 rows=10,364 loops=1)

  • Hash Cond: (c_7.comp_part = b_2.comp_part)
  • Join Filter: ((NOT (b_2.alt_code IS DISTINCT FROM c_7.alt_code)) AND CASE WHEN (NOT $84) THEN (b_2.depth <> c_7.depth) ELSE (b_2.id <> c_7.id) END)
  • Rows Removed by Join Filter: 2122
354.          

Initplan (for Hash Join)

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

356. 2.233 2.233 ↓ 1.2 12,480 1

CTE Scan on cr_data c_7 (cost=0.00..212.58 rows=10,629 width=648) (actual time=0.001..2.233 rows=12,480 loops=1)

357. 0.753 1.647 ↓ 2,116.0 2,116 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=1.647..1.647 rows=2,116 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 168kB
358. 0.894 0.894 ↓ 2,116.0 2,116 1

CTE Scan on base b_2 (cost=0.00..0.02 rows=1 width=76) (actual time=0.002..0.894 rows=2,116 loops=1)

359.          

CTE calc

360. 28.044 1,729.326 ↓ 12,481.0 12,481 1

WindowAgg (cost=1,338.22..1,338.64 rows=1 width=312) (actual time=1,684.696..1,729.326 rows=12,481 loops=1)

361.          

CTE x

362. 27.588 1,548.777 ↓ 12,481.0 12,481 1

Sort (cost=1,338.08..1,338.08 rows=1 width=522) (actual time=1,547.821..1,548.777 rows=12,481 loops=1)

  • Sort Key: ((b_3.co_rows_id = '-9999'::integer)) DESC, b_3.id
  • Sort Method: quicksort Memory: 3648kB
363.          

Initplan (for Sort)

364. 0.002 0.002 ↑ 1.0 1 1

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

365. 0.001 0.001 ↑ 1.0 1 1

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

366. 0.000 0.000 ↓ 0.0 0

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

367. 0.001 0.001 ↑ 1.0 1 1

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

368. 0.001 0.001 ↑ 1.0 1 1

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

369. 0.000 0.000 ↓ 0.0 0

CTE Scan on m_depth m_depth_5 (cost=0.00..0.02 rows=1 width=4) (never executed)

370. 0.000 0.000 ↓ 0.0 0

CTE Scan on m_depth m_depth_6 (cost=0.00..0.02 rows=1 width=4) (never executed)

371. 0.000 0.000 ↓ 0.0 0

CTE Scan on counter counter_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

372. 0.001 0.001 ↑ 1.0 1 1

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

373. 0.000 0.000 ↓ 0.0 0

CTE Scan on counter counter_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

374. 0.002 0.002 ↑ 1.0 1 1

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

375. 24.481 1,521.181 ↓ 12,481.0 12,481 1

Hash Right Join (cost=385.26..1,337.85 rows=1 width=522) (actual time=1,496.422..1,521.181 rows=12,481 loops=1)

  • Hash Cond: (c_8.part_no = b_3.comp_part)
  • Join Filter: (NOT (c_8.alternate IS DISTINCT FROM b_3.alt_code))
376. 12.914 12.914 ↑ 1,538.5 26 1

CTE Scan on childs c_8 (cost=0.00..800.00 rows=40,000 width=64) (actual time=12.615..12.914 rows=26 loops=1)

377. 13.024 1,483.786 ↓ 12,481.0 12,481 1

Hash (cost=385.25..385.25 rows=1 width=562) (actual time=1,483.786..1,483.786 rows=12,481 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2654kB
378. 13.253 1,470.762 ↓ 12,481.0 12,481 1

Hash Right Join (cost=53.08..385.25 rows=1 width=562) (actual time=1,452.364..1,470.762 rows=12,481 loops=1)

  • Hash Cond: ((cr_2.comp_part = b_3.part_no) AND (cr_2.co_rows_id = b_3.co_rows_id))
  • Join Filter: ((NOT (cr_2.alt_code IS DISTINCT FROM b_3.alternate)) AND (cr_2.id <> b_3.id))
  • Rows Removed by Join Filter: 2116
  • Filter: CASE WHEN (cr_2.id IS NOT NULL) THEN (b_3.comp_equal_up_comp_part OR CASE WHEN ((NOT $94) AND ($95 > 1)) THEN (b_3.parent_update IS NOT NULL) ELSE true END) ELSE true END
379. 5.163 5.163 ↑ 2.4 2,206 1

CTE Scan on cr_data cr_2 (cost=0.00..292.30 rows=5,314 width=173) (actual time=0.003..5.163 rows=2,206 loops=1)

  • Filter: CASE WHEN ((co_rows_id = 0) OR (co_rows_id = '-9999'::integer)) THEN (part_no = comp_part) ELSE true END
  • Rows Removed by Filter: 10274
380. 12.514 1,452.346 ↓ 12,480.0 12,480 1

Hash (cost=53.06..53.06 rows=1 width=489) (actual time=1,452.346..1,452.346 rows=12,480 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2464kB
381. 10.053 1,439.832 ↓ 12,480.0 12,480 1

Hash Right Join (cost=11.56..53.06 rows=1 width=489) (actual time=1,428.296..1,439.832 rows=12,480 loops=1)

  • Hash Cond: ((s.part_no = b_3.part_no) AND (s.code = b_3.alternate))
382. 1.506 1.506 ↑ 1.0 1,509 1

CTE Scan on st_alt s (cost=0.00..30.18 rows=1,509 width=68) (actual time=0.014..1.506 rows=1,509 loops=1)

383. 12.191 1,428.273 ↓ 12,480.0 12,480 1

Hash (cost=11.54..11.54 rows=1 width=485) (actual time=1,428.273..1,428.273 rows=12,480 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2395kB
384. 7.135 1,416.082 ↓ 12,480.0 12,480 1

Hash Right Join (cost=0.07..11.54 rows=1 width=485) (actual time=1,408.958..1,416.082 rows=12,480 loops=1)

  • Hash Cond: (p_3.component_part = b_3.comp_part)
  • Join Filter: (NOT (p_3.alt_code IS DISTINCT FROM b_3.alt_code))
385. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on p2 p_3 (cost=0.00..9.64 rows=482 width=64) (actual time=0.001..0.001 rows=0 loops=1)

386. 11.861 1,408.946 ↓ 12,480.0 12,480 1

Hash (cost=0.06..0.06 rows=1 width=485) (actual time=1,408.946..1,408.946 rows=12,480 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2395kB
387. 1,397.085 1,397.085 ↓ 12,480.0 12,480 1

CTE Scan on base_extra b_3 (cost=0.00..0.06 rows=1 width=485) (actual time=1,350.148..1,397.085 rows=12,480 loops=1)

  • Filter: (CASE WHEN (NOT _main) THEN ($96 OR (NOT comp_equal_up_comp_part) OR edit_row) ELSE true END AND (depth >= $89) AND CASE WHEN ((NOT _main) AND (NOT $90) AND (depth <= $91) AND ($92 = $93)) THEN edit_row ELSE true END)
388. 15.730 1,701.282 ↓ 12,481.0 12,481 1

WindowAgg (cost=0.14..0.17 rows=1 width=558) (actual time=1,684.665..1,701.282 rows=12,481 loops=1)

389. 39.009 1,685.552 ↓ 12,481.0 12,481 1

Sort (cost=0.14..0.15 rows=1 width=526) (actual time=1,684.643..1,685.552 rows=12,481 loops=1)

  • Sort Key: x.comp_part, x.alt_code, x.co_rows_id, x.edit_row, x.id
  • Sort Method: quicksort Memory: 3648kB
390. 24.031 1,646.543 ↓ 12,481.0 12,481 1

WindowAgg (cost=0.10..0.13 rows=1 width=526) (actual time=1,621.082..1,646.543 rows=12,481 loops=1)

391. 12.948 1,622.512 ↓ 12,481.0 12,481 1

Sort (cost=0.10..0.11 rows=1 width=494) (actual time=1,621.058..1,622.512 rows=12,481 loops=1)

  • Sort Key: x.comp_part, x.alt_code, x.id
  • Sort Method: quicksort Memory: 3648kB
392. 20.313 1,609.564 ↓ 12,481.0 12,481 1

WindowAgg (cost=0.03..0.09 rows=1 width=494) (actual time=1,567.138..1,609.564 rows=12,481 loops=1)

393. 21.033 1,589.251 ↓ 12,481.0 12,481 1

WindowAgg (cost=0.03..0.07 rows=1 width=454) (actual time=1,567.119..1,589.251 rows=12,481 loops=1)

394. 10.954 1,568.218 ↓ 12,481.0 12,481 1

Sort (cost=0.03..0.04 rows=1 width=390) (actual time=1,567.095..1,568.218 rows=12,481 loops=1)

  • Sort Key: x.comp_part, x.alt_code, ((x.co_rows_id = '-9999'::integer)) DESC, x.id
  • Sort Method: quicksort Memory: 3597kB
395. 1,557.264 1,557.264 ↓ 12,481.0 12,481 1

CTE Scan on x (cost=0.00..0.02 rows=1 width=390) (actual time=1,547.826..1,557.264 rows=12,481 loops=1)

396. 1,739.735 1,739.735 ↓ 12,481.0 12,481 1

CTE Scan on calc b (cost=0.00..0.02 rows=1 width=307) (actual time=1,684.702..1,739.735 rows=12,481 loops=1)

Planning time : 27.751 ms
Execution time : 1,766.263 ms