explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i8b8

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

CTE Scan on base_extra (cost=56,617.61..56,617.65 rows=2 width=648) (actual time=1,358.047..1,386.982 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.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 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.000..0.001 rows=1 loops=1)

8.          

CTE _qty

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

10.          

CTE up_id

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

12.          

CTE m_depth

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

14.          

CTE counter

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 _main

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

18.          

CTE _first

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

20.          

CTE _update_code

21. 0.019 0.019 ↑ 1.0 1 1

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

22.          

CTE b1

23. 1.212 787.164 ↓ 10.0 2,182 1

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

24. 785.952 785.952 ↓ 10.0 2,182 1

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

25.          

CTE a

26. 0.534 1.896 ↑ 1.0 2,182 1

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

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

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

28. 0.427 1.008 ↑ 1.0 2,182 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 135kB
29. 0.581 0.581 ↑ 1.0 2,182 1

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

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

CTE so_rep

31. 2.125 329.234 ↓ 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=292.897..329.234 rows=23,872 loops=1)

32. 20.397 327.109 ↓ 3.8 23,872 1

GroupAggregate (cost=9,133.31..9,855.02 rows=6,204 width=150) (actual time=292.897..327.109 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)
33. 90.781 306.712 ↑ 1.0 35,534 1

Sort (cost=9,133.31..9,222.58 rows=35,706 width=525) (actual time=292.882..306.712 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: 28,608kB
34. 211.528 215.931 ↑ 1.0 35,534 1

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

35. 4.403 4.403 ↑ 1.0 35,534 1

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

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

37. 0.005 0.005 ↑ 1.0 1 1

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

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

CTE gr_so_rows

39. 32.480 35.468 ↓ 4.2 29,564 1

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

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

41.          

CTE st_total

42. 0.550 13.415 ↑ 1.1 6,211 1

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

43. 5.574 12.865 ↑ 1.1 6,211 1

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

  • Group Key: h.sernr
44. 2.652 7.291 ↓ 1.0 8,192 1

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

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

46. 1.258 3.821 ↓ 1.0 7,108 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 441kB
47. 2.563 2.563 ↓ 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.563 rows=7,108 loops=1)

48.          

CTE to_deliver

49. 0.211 32.737 ↓ 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=31.041..32.737 rows=1,157 loops=1)

50. 1.424 32.526 ↓ 2.9 1,157 1

GroupAggregate (cost=6,209.15..6,276.05 rows=400 width=361) (actual time=31.037..32.526 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
51. 2.139 31.102 ↓ 1.3 1,485 1

Sort (cost=6,209.15..6,212.00 rows=1,141 width=297) (actual time=31.022..31.102 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
52. 0.397 28.963 ↓ 1.3 1,485 1

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

53. 0.946 28.566 ↓ 1.3 1,485 1

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

  • Sort Key: r_2.sernr, r_2.rowno
  • Sort Method: quicksort Memory: 426kB
54. 2.959 27.620 ↓ 1.3 1,485 1

HashAggregate (cost=6,061.89..6,079.01 rows=1,141 width=382) (actual time=26.661..27.620 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
55. 9.767 24.661 ↓ 1.3 1,485 1

Nested Loop (cost=624.98..6,024.81 rows=1,141 width=290) (actual time=5.502..24.661 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: 1,149
56. 0.007 0.007 ↑ 1.0 1 1

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

  • Filter: (register = 'Invoice'::text)
  • Rows Removed by Filter: 4
57. 0.774 11.431 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.parent = cor.id)
58. 1.224 9.897 ↓ 1.2 2,634 1

Hash Left Join (cost=490.88..566.60 rows=2,282 width=290) (actual time=4.694..9.897 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))
59. 0.772 8.089 ↓ 1.2 2,634 1

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

  • Hash Cond: (d.sernr = dh.sernr)
60. 1.091 7.086 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.artcode = a_1.code)
61. 0.812 5.124 ↓ 1.2 2,634 1

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

  • Hash Cond: (r_2.sernr = h_1.sernr)
62. 1.016 4.156 ↓ 1.1 2,634 1

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

  • Hash Cond: (d.co_rows_id = r_2.id)
63. 0.382 1.496 ↓ 1.0 2,567 1

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

  • Hash Cond: (c.del_row_id = d.id)
64. 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)

65. 0.435 1.113 ↓ 1.0 2,567 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 159kB
66. 0.678 0.678 ↓ 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..0.678 rows=2,567 loops=1)

67. 0.751 1.644 ↓ 1.0 2,306 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 286kB
68. 0.893 0.893 ↓ 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.893 rows=2,306 loops=1)

  • Filter: (closed IS NULL)
  • Rows Removed by Filter: 380
69. 0.053 0.156 ↑ 1.0 350 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
70. 0.103 0.103 ↑ 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.103 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
71. 0.449 0.871 ↑ 1.0 2,212 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 145kB
72. 0.422 0.422 ↑ 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.422 rows=2,212 loops=1)

73. 0.082 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: 1,024 Batches: 1 Memory Usage: 32kB
74. 0.149 0.149 ↑ 1.0 539 1

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

75. 0.363 0.584 ↑ 1.0 1,509 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 126kB
76. 0.221 0.221 ↑ 1.0 1,509 1

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

77. 0.382 0.760 ↓ 1.0 2,686 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 137kB
78. 0.378 0.378 ↓ 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.378 rows=2,686 loops=1)

79.          

SubPlan (for Nested Loop)

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

81. 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)
82.          

CTE mt

83. 0.341 466.463 ↓ 2.1 428 1

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

  • Group Key: r_3.artcode, r_3.alt_code
84. 0.335 466.122 ↑ 1.1 792 1

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

  • Sort Key: r_3.artcode, r_3.alt_code
85. 0.001 1.060 ↓ 0.0 0 1

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

  • Group Key: r_3.artcode, r_3.alt_code
86. 0.005 1.059 ↓ 0.0 0 1

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

  • Sort Key: r_3.artcode, r_3.alt_code
  • Sort Method: quicksort Memory: 25kB
87. 0.013 1.054 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (rows_1.id = r_3.id)
89. 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
90. 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
91. 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)

92. 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
93. 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
94. 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
95. 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)
96. 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)
97. 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))
98. 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)
99. 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)
100. 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)
101. 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)
102. 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)

103. 0.000 0.000 ↓ 0.0 0

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

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

105. 0.000 0.000 ↓ 0.0 0

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

106. 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)
107. 0.000 0.000 ↓ 0.0 0

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

108. 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)
109. 0.000 0.000 ↓ 0.0 0

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

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

111. 0.000 0.000 ↓ 0.0 0

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

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

113. 0.000 0.000 ↓ 0.0 0

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

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

115.          

SubPlan (for Nested Loop)

116. 0.000 0.000 ↓ 0.0 0

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

117. 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)
118. 0.000 0.854 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
119. 0.854 0.854 ↓ 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.854..0.854 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: 1,333
120. 0.045 0.181 ↑ 1.0 350 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
121. 0.136 0.136 ↑ 1.0 350 1

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

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
122. 0.018 51.310 ↑ 1.5 184 1

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

123. 0.187 51.292 ↑ 1.5 184 1

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

  • Group Key: sh.part_no, sh.alt_code, sh.lot_size, st.received, sh.finished, r_5.closed
124. 0.387 51.105 ↑ 1.5 185 1

Sort (cost=526.02..526.73 rows=283 width=107) (actual time=51.087..51.105 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
125. 0.626 50.718 ↑ 1.5 185 1

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

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

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

127. 0.062 35.244 ↑ 1.5 185 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
128. 0.143 35.182 ↑ 1.5 185 1

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

129. 0.051 34.669 ↑ 1.7 185 1

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

  • Hash Cond: (r_5.sernr = h_4.sernr)
130. 0.370 34.452 ↑ 1.7 185 1

Hash Right Join (cost=84.64..93.69 rows=320 width=57) (actual time=34.249..34.452 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: 1,110
131. 33.015 33.015 ↓ 2.9 1,157 1

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

132. 0.250 1.067 ↓ 2.0 1,295 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 88kB
133. 0.817 0.817 ↓ 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.037..0.817 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
134. 0.054 0.166 ↑ 1.0 350 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
135. 0.112 0.112 ↑ 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.112 rows=350 loops=1)

  • Filter: (confirmed IS NOT NULL)
  • Rows Removed by Filter: 3
136. 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))
137. 0.044 395.302 ↓ 6.7 497 1

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

138. 0.617 395.258 ↓ 6.7 497 1

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

  • Group Key: sr.comp_part, sr.alt_code, st_1.qty
139. 1.296 394.641 ↓ 9.4 696 1

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

  • Sort Key: sr.comp_part, sr.alt_code, st_1.qty
  • Sort Method: quicksort Memory: 76kB
140. 0.565 393.345 ↓ 9.4 696 1

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

141. 4.174 392.084 ↓ 9.4 696 1

Hash Right Join (cost=562.36..826.16 rows=74 width=132) (actual time=388.954..392.084 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))
142. 337.064 337.064 ↓ 3.8 23,872 1

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

143. 0.186 50.846 ↓ 9.4 696 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
144. 2.623 50.660 ↓ 9.4 696 1

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

  • Hash Cond: (sr.sernr = sh_1.sernr)
145. 44.836 44.836 ↓ 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.529..44.836 rows=29,564 loops=1)

146. 0.014 3.201 ↑ 1.7 43 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
147. 0.042 3.187 ↑ 1.7 43 1

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

  • Hash Cond: (sh_1.sernr = r_6.so_ref)
148. 1.975 1.975 ↑ 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.975 rows=148 loops=1)

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6,960
149. 0.212 1.170 ↓ 2.0 1,295 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
150. 0.958 0.958 ↓ 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.048..0.958 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
151. 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)
152. 0.102 4.859 ↑ 2.0 71 1

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

  • Sort Key: ""*SELECT* 4"".part_no, ""*SELECT* 4"".alt_code
  • Sort Method: quicksort Memory: 30kB
153. 0.008 4.757 ↑ 2.0 71 1

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

154. 0.035 4.749 ↑ 2.0 71 1

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

  • Group Key: st_2.received, sh_2.sernr
155. 0.043 4.714 ↑ 2.0 71 1

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

  • Sort Key: st_2.received, sh_2.sernr
  • Sort Method: quicksort Memory: 30kB
156. 0.061 4.671 ↑ 2.0 71 1

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

157. 0.511 4.539 ↑ 2.0 71 1

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

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

159. 0.016 3.574 ↑ 2.0 71 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
160. 0.109 3.558 ↑ 2.0 71 1

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

  • Hash Cond: (t_1.id = r_7.id)
161. 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)

162. 0.019 3.355 ↑ 2.0 71 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
163. 0.038 3.336 ↑ 2.0 71 1

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

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

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

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6,960
165. 0.203 1.179 ↓ 2.0 1,295 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
166. 0.976 0.976 ↓ 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.040..0.976 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
167. 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)
168. 0.005 13.256 ↑ 3.6 40 1

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

169. 0.062 13.251 ↑ 3.6 40 1

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

  • Group Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
170. 0.098 13.189 ↑ 1.5 94 1

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

  • Sort Key: sr_1.comp_part, sr_1.alt_code, st_3.qty
  • Sort Method: quicksort Memory: 30kB
171. 0.081 13.091 ↑ 1.5 94 1

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

172. 3.006 12.916 ↑ 1.5 94 1

Hash Right Join (cost=565.13..937.64 rows=144 width=132) (actual time=12.744..12.916 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))
173. 2.061 2.061 ↓ 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.000..2.061 rows=23,872 loops=1)

174. 0.027 7.849 ↑ 1.5 94 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
175. 0.080 7.822 ↑ 1.5 94 1

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

  • Hash Cond: (sh_3.master_sernr = r_8.so_ref)
176. 2.581 6.724 ↓ 1.3 1,061 1

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

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

178. 0.022 1.643 ↑ 5.5 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
179. 1.621 1.621 ↑ 5.5 148 1

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

  • Filter: ((planned IS NOT NULL) AND (finished IS NULL) AND (cancelled IS NULL))
  • Rows Removed by Filter: 6,960
180. 0.177 1.018 ↓ 2.0 1,295 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
181. 0.841 0.841 ↓ 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.034..0.841 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
182. 0.094 0.094 ↑ 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.001..0.001 rows=1 loops=94)

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

CTE mt2

184. 0.058 3.316 ↓ 57.0 57 1

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

  • Group Key: r_9.artcode, r_9.alt_code
185. 0.115 3.258 ↓ 83.0 83 1

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

  • Sort Key: r_9.artcode, r_9.alt_code
  • Sort Method: quicksort Memory: 31kB
186. 0.082 3.143 ↓ 83.0 83 1

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

187. 0.111 2.970 ↓ 91.0 91 1

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

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

189. 0.023 2.776 ↓ 91.0 91 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
190. 0.761 2.753 ↓ 91.0 91 1

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

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

  • Filter: ((alt_code IS NOT NULL) AND (so_ref IS NULL))
  • Rows Removed by Filter: 1,296
192. 0.576 0.576 ↑ 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.027..0.576 rows=1,334 loops=1)

193. 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
194.          

CTE tt

195. 0.030 279.966 ↑ 1.9 301 1

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

196. 0.003 177.301 ↑ 10.7 34 1

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

197. 0.048 177.298 ↑ 10.7 34 1

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

  • Group Key: sh_5.part_no, sh_5.alt_code, (COALESCE((sum(COALESCE(rec_1.qty, '0'::numeric))), '0'::numeric))
198. 0.055 177.250 ↑ 10.7 34 1

Sort (cost=4,735.24..4,736.15 rows=365 width=86) (actual time=177.248..177.250 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
199. 0.466 177.195 ↑ 10.7 34 1

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

  • Hash Cond: (h_9.sernr = sh_5.sernr)
200. 6.263 15.103 ↑ 1.1 6,211 1

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

  • Group Key: h_9.sernr
201. 3.435 8.840 ↓ 1.0 8,192 1

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

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

203. 1.401 4.382 ↓ 1.0 7,108 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 441kB
204. 2.981 2.981 ↓ 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.003..2.981 rows=7,108 loops=1)

205. 0.029 161.626 ↑ 10.7 34 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
206. 161.597 161.597 ↑ 10.7 34 1

Seq Scan on so_header sh_5 (cost=0.00..3,841.80 rows=365 width=26) (actual time=1.443..161.597 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: 7,074
207. 0.034 49.085 ↓ 1.8 239 1

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

208. 0.342 49.051 ↓ 1.8 239 1

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

  • Group Key: r_11.comp_part, r_11.alt_code, st_5.qty, r_11.sernr
209. 0.511 48.709 ↓ 1.8 239 1

Sort (cost=2,613.56..2,613.90 rows=136 width=132) (actual time=48.689..48.709 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
210. 3.124 48.198 ↓ 1.8 239 1

Hash Right Join (cost=2,236.23..2,608.74 rows=136 width=132) (actual time=47.914..48.198 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))
211. 2.113 2.113 ↓ 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.113 rows=23,872 loops=1)

212. 0.074 42.961 ↓ 1.8 239 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
213. 2.367 42.887 ↓ 1.8 239 1

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

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

215. 0.012 37.996 ↑ 9.1 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
216. 37.984 37.984 ↑ 9.1 15 1

Seq Scan on so_header sh_6 (cost=0.00..2,071.80 rows=136 width=4) (actual time=0.776..37.984 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: 7,093
217. 0.066 53.550 ↑ 2.4 28 1

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

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

CTE nodes_cte

219. 0.029 53.433 ↓ 1.7 109 1

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

220. 0.017 52.966 ↓ 16.0 96 1

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

221. 0.061 52.949 ↓ 16.0 96 1

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

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

CTE base

223. 0.085 51.673 ↑ 1.9 72 1

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

  • Group Key: r_1_1.comp_part, r_1_1.alt_code, st_4.qty, r_1_1.sernr
224. 0.138 51.588 ↑ 1.9 72 1

Sort (cost=2,613.46..2,613.80 rows=136 width=132) (actual time=51.582..51.588 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
225. 3.056 51.450 ↑ 1.9 72 1

Hash Right Join (cost=2,236.14..2,608.64 rows=136 width=132) (actual time=51.196..51.450 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))
226. 2.175 2.175 ↓ 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.175 rows=23,872 loops=1)

227. 0.036 46.219 ↑ 1.9 72 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
228. 0.709 46.183 ↑ 1.9 72 1

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

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

  • Filter: (alt_code IS NOT NULL)
  • Rows Removed by Filter: 19,673
230. 0.015 42.520 ↑ 9.1 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
231. 42.505 42.505 ↑ 9.1 15 1

Seq Scan on so_header sh_4 (cost=0.00..2,071.80 rows=136 width=4) (actual time=1.386..42.505 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: 7,093
232. 0.081 52.888 ↓ 16.0 96 1

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

233. 0.045 52.711 ↓ 16.0 96 1

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

234. 0.042 52.186 ↓ 16.0 96 1

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

235. 51.712 51.712 ↑ 1.9 72 1

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

236. 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)
237. 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))
238. 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))
239. 0.006 0.438 ↑ 1.0 6 2

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

240. 0.020 0.432 ↑ 1.0 6 2

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

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

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

242. 0.055 0.390 ↑ 1.0 6 2

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

243. 0.008 0.008 ↑ 1.1 54 2

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

244. 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)
245. 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))
246. 53.484 53.484 ↓ 1.7 109 1

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

247.          

CTE il

248. 9.079 9.596 ↓ 2.5 716 1

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

  • Group Key: t_3.artcode, t_3.alt_code
249. 0.367 0.517 ↓ 3.0 883 1

Hash Left Join (cost=1.29..50.90 rows=290 width=20) (actual time=0.024..0.517 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
250. 0.139 0.139 ↑ 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.004..0.139 rows=1,158 loops=1)

251. 0.005 0.011 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
252. 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)

253.          

CTE l

254. 1.700 780.478 ↓ 1.1 2,346 1

Merge Left Join (cost=341.17..624.74 rows=2,183 width=228) (actual time=772.811..780.478 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))
255. 0.916 766.534 ↓ 1.1 2,346 1

Merge Left Join (cost=323.51..540.77 rows=2,183 width=228) (actual time=760.624..766.534 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))
256. 0.942 762.655 ↓ 1.1 2,346 1

Merge Left Join (cost=213.68..382.21 rows=2,183 width=164) (actual time=759.483..762.655 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))
257. 0.473 481.106 ↑ 1.0 2,182 1

Merge Left Join (cost=176.40..231.18 rows=2,183 width=132) (actual time=478.893..481.106 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))
258. 1.462 477.248 ↑ 1.0 2,182 1

Merge Left Join (cost=176.37..225.49 rows=2,183 width=132) (actual time=475.520..477.248 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))
259. 6.331 8.881 ↑ 1.0 2,182 1

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

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

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

261. 0.263 466.905 ↓ 2.1 428 1

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

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

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

263. 0.014 3.385 ↓ 57.0 57 1

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

264. 0.034 3.371 ↓ 57.0 57 1

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

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

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

266. 0.466 280.607 ↑ 1.9 301 1

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

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

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

268. 0.066 2.963 ↓ 2.9 169 1

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

269. 1.835 2.897 ↓ 1.4 83 1

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

  • Group Key: p_1.artcode, p_1.alt_code
270. 0.156 1.062 ↓ 1.6 94 1

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

  • Sort Key: p_1.artcode, p_1.alt_code
  • Sort Method: quicksort Memory: 32kB
271. 0.038 0.906 ↓ 1.6 94 1

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

  • Hash Cond: (p_1.sernr = h_10.sernr)
272. 0.744 0.744 ↑ 4.1 181 1

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

  • Filter: ((closed IS NULL) AND (qty > qty_delivered))
  • Rows Removed by Filter: 2,467
273. 0.009 0.124 ↑ 1.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
274. 0.115 0.115 ↑ 1.0 36 1

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

  • Filter: ((confirmed IS NOT NULL) AND (delivered IS NULL))
  • Rows Removed by Filter: 435
275. 2.190 12.244 ↓ 3.0 874 1

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

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

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

277.          

CTE e

278. 3.413 784.863 ↓ 10.0 2,182 1

HashAggregate (cost=92.78..97.68 rows=218 width=260) (actual time=783.422..784.863 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
279. 781.450 781.450 ↓ 1.1 2,346 1

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

280.          

CTE p2

281. 0.000 30.153 ↓ 0.0 0 1

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

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

Initplan (for Group)

283. 0.000 0.000 ↓ 0.0 0

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

284. 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.021..0.022 rows=1 loops=1)

285. 0.005 30.152 ↓ 0.0 0 1

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

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

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

287. 30.146 30.146 ↓ 0.0 0 1

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

  • Filter: ((update_code = $43) AND (session_id = ($42)::text))
  • Rows Removed by Filter: 138,183
288. 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))
289.          

CTE cr_data

290. 10.472 898.204 ↓ 1.2 12,414 1

Merge Left Join (cost=6,837.99..7,325.54 rows=10,629 width=338) (actual time=882.496..898.204 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))
291.          

Initplan (for Merge Left Join)

292. 0.002 0.002 ↑ 1.0 1 1

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

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

294. 0.000 0.000 ↑ 1.0 1 1

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

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

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

297. 0.000 0.000 ↑ 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.000 rows=1 loops=1)

298. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

304. 0.001 0.001 ↑ 1.0 1 1

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

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

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

307. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on _qty (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 _main _main_2 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

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

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

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

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

313. 3.664 92.804 ↓ 1.2 12,414 1

Merge Left Join (cost=6,824.74..6,920.68 rows=10,629 width=162) (actual time=88.226..92.804 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))
314. 20.354 58.972 ↓ 1.2 12,414 1

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

  • Sort Key: c_4.comp_part
  • Sort Method: quicksort Memory: 3,618kB
315. 38.618 38.618 ↓ 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=3.006..38.618 rows=12,414 loops=1)

  • Filter: (session_id = ($67)::text)
  • Rows Removed by Filter: 125,769
316. 0.013 30.168 ↓ 0.0 0 1

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

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

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

318. 6.864 794.915 ↓ 57.3 12,482 1

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

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

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

320.          

CTE base

321. 914.987 914.991 ↓ 6,791.0 6,791 1

CTE Scan on cr_data (cost=0.16..425.32 rows=1 width=648) (actual time=884.382..914.991 rows=6,791 loops=1)

  • Filter: ((depth >= $69) AND CASE WHEN ((depth > 1) AND (NOT edit_row) AND (NOT $76)) THEN (parent_update IS NOT NULL) ELSE true END 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: 5,623
322.          

Initplan (for CTE Scan)

323. 0.000 0.000 ↑ 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.000 rows=1 loops=1)

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

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

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

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

328. 0.000 0.000 ↑ 1.0 1 1

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

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

330. 0.002 0.002 ↑ 1.0 1 1

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

331.          

CTE base_extra

332. 23.436 1,384.501 ↓ 4,438.5 8,877 1

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

333. 343.094 1,361.065 ↓ 8,731.5 17,463 1

Sort (cost=253.47..253.48 rows=2 width=648) (actual time=1,358.042..1,361.065 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: 3,544kB
334. 1.335 1,017.971 ↓ 8,731.5 17,463 1

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

335. 917.060 917.060 ↓ 6,791.0 6,791 1

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

336. 94.687 99.576 ↓ 10,672.0 10,672 1

Hash Join (cost=0.06..253.42 rows=1 width=648) (actual time=3.441..99.576 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 $78) THEN (b_1.depth <> c_5.depth) ELSE (b_1.id <> c_5.id) END)
  • Rows Removed by Join Filter: 760,157
337.          

Initplan (for Hash Join)

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

339. 1.605 1.605 ↓ 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.605 rows=12,414 loops=1)

340. 1.430 3.283 ↓ 6,791.0 6,791 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 469kB
341. 1.853 1.853 ↓ 6,791.0 6,791 1

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

  • Filter: (part_no <> comp_part)
Planning time : 23.877 ms
Execution time : 1,402.536 ms