explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5LIH : replenishment

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 19.854 1,330.414 ↑ 7,364.0 1 1

Hash Join (cost=92,362.19..111,675.64 rows=7,364 width=452) (actual time=35.792..1,330.414 rows=1 loops=1)

  • Hash Cond: (t1.ref_orderline_id = c_orderline.c_orderline_id)
2. 1,310.543 1,310.543 ↑ 1.0 14,379 1

CTE Scan on t1 (cost=92,351.27..111,462.14 rows=14,729 width=378) (actual time=3.368..1,310.543 rows=14,379 loops=1)

3.          

CTE t1

4. 27.226 1,124.626 ↑ 1.0 14,379 1

Append (cost=46.94..92,351.27 rows=14,729 width=96) (actual time=3.311..1,124.626 rows=14,379 loops=1)

5. 1.788 29.223 ↓ 1.0 446 1

Hash Join (cost=46.94..966.80 rows=445 width=91) (actual time=3.309..29.223 rows=446 loops=1)

  • Hash Cond: (line.m_requisition_id = req.m_requisition_id)
6. 13.364 26.143 ↓ 1.0 446 1

Hash Right Join (cost=24.01..706.36 rows=445 width=82) (actual time=2.002..26.143 rows=446 loops=1)

  • Hash Cond: (poline.c_orderline_id = line.c_orderline_id)
7. 11.451 11.451 ↑ 1.0 9,132 1

Seq Scan on c_orderline poline (cost=0.00..631.65 rows=9,365 width=12) (actual time=0.003..11.451 rows=9,132 loops=1)

8. 0.665 1.328 ↓ 1.0 446 1

Hash (cost=18.45..18.45 rows=445 width=77) (actual time=1.328..1.328 rows=446 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
9. 0.663 0.663 ↓ 1.0 446 1

Seq Scan on m_requisitionline line (cost=0.00..18.45 rows=445 width=77) (actual time=0.005..0.663 rows=446 loops=1)

10. 0.643 1.292 ↓ 1.0 442 1

Hash (cost=17.41..17.41 rows=441 width=15) (actual time=1.292..1.292 rows=442 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
11. 0.649 0.649 ↓ 1.0 442 1

Seq Scan on m_requisition req (cost=0.00..17.41 rows=441 width=15) (actual time=0.005..0.649 rows=442 loops=1)

12. 30.394 1,056.696 ↑ 1.0 12,603 1

Subquery Scan on *SELECT* 2 (cost=2,482.06..90,539.30 rows=12,959 width=97) (actual time=127.656..1,056.696 rows=12,603 loops=1)

13. 319.317 1,026.302 ↑ 1.0 12,603 1

Hash Join (cost=2,482.06..90,409.71 rows=12,959 width=97) (actual time=127.651..1,026.302 rows=12,603 loops=1)

  • Hash Cond: (line_1.m_product_id = product.m_product_id)
14. 41.504 117.331 ↑ 1.0 12,603 1

Hash Join (cost=718.84..3,165.69 rows=12,959 width=99) (actual time=38.635..117.331 rows=12,603 loops=1)

  • Hash Cond: (line_1.m_production_id = prod.m_production_id)
15. 37.228 37.228 ↑ 1.0 12,603 1

Seq Scan on m_productionline line_1 (cost=0.00..2,268.66 rows=12,959 width=63) (actual time=0.007..37.228 rows=12,603 loops=1)

  • Filter: ((isendproduct = 'Y'::bpchar) AND (ismanual = 'N'::bpchar))
  • Rows Removed by Filter: 40,013
16. 18.721 38.599 ↓ 1.0 12,794 1

Hash (cost=558.93..558.93 rows=12,793 width=42) (actual time=38.599..38.599 rows=12,794 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 996kB
17. 19.878 19.878 ↓ 1.0 12,794 1

Seq Scan on m_production prod (cost=0.00..558.93 rows=12,793 width=42) (actual time=0.005..19.878 rows=12,794 loops=1)

18. 39.484 88.850 ↑ 1.0 26,831 1

Hash (cost=1,427.54..1,427.54 rows=26,854 width=11) (actual time=88.850..88.850 rows=26,831 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,314kB
19. 49.366 49.366 ↑ 1.0 26,831 1

Seq Scan on m_product product (cost=0.00..1,427.54 rows=26,854 width=11) (actual time=0.006..49.366 rows=26,831 loops=1)

20.          

SubPlan (for Hash Join)

21. 37.809 176.442 ↑ 1.0 1 12,603

Limit (cost=1.05..1.06 rows=1 width=64) (actual time=0.014..0.014 rows=1 loops=12,603)

22. 75.618 138.633 ↑ 1.0 1 12,603

Sort (cost=1.05..1.06 rows=1 width=64) (actual time=0.011..0.011 rows=1 loops=12,603)

  • Sort Key: brk.breakvalue DESC
  • Sort Method: quicksort Memory: 25kB
23. 63.015 63.015 ↑ 1.0 1 12,603

Seq Scan on fil_processingtime_break brk (cost=0.00..1.04 rows=1 width=64) (actual time=0.003..0.005 rows=1 loops=12,603)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
  • Rows Removed by Filter: 2
24. 8.776 32.910 ↑ 1.0 1 2,194

Limit (cost=1.05..1.06 rows=1 width=64) (actual time=0.014..0.015 rows=1 loops=2,194)

25. 13.164 24.134 ↑ 1.0 1 2,194

Sort (cost=1.05..1.06 rows=1 width=64) (actual time=0.011..0.011 rows=1 loops=2,194)

  • Sort Key: brk_1.breakvalue DESC
  • Sort Method: quicksort Memory: 25kB
26. 10.970 10.970 ↑ 1.0 1 2,194

Seq Scan on fil_processingtime_break brk_1 (cost=0.00..1.04 rows=1 width=64) (actual time=0.003..0.005 rows=1 loops=2,194)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
  • Rows Removed by Filter: 2
27. 31.227 145.726 ↑ 1.0 1 10,409

Limit (cost=1.05..1.06 rows=1 width=64) (actual time=0.013..0.014 rows=1 loops=10,409)

28. 72.863 114.499 ↑ 1.0 1 10,409

Sort (cost=1.05..1.06 rows=1 width=64) (actual time=0.011..0.011 rows=1 loops=10,409)

  • Sort Key: brk_2.breakvalue DESC
  • Sort Method: quicksort Memory: 25kB
29. 41.636 41.636 ↑ 1.0 1 10,409

Seq Scan on fil_processingtime_break brk_2 (cost=0.00..1.04 rows=1 width=64) (actual time=0.003..0.004 rows=1 loops=10,409)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
  • Rows Removed by Filter: 2
30. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.05..1.06 rows=1 width=64) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.05..1.06 rows=1 width=64) (never executed)

  • Sort Key: brk_3.breakvalue DESC
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on fil_processingtime_break brk_3 (cost=0.00..1.04 rows=1 width=64) (never executed)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
33. 31.227 145.726 ↑ 1.0 1 10,409

Limit (cost=1.05..1.06 rows=1 width=64) (actual time=0.013..0.014 rows=1 loops=10,409)

34. 72.863 114.499 ↑ 1.0 1 10,409

Sort (cost=1.05..1.06 rows=1 width=64) (actual time=0.011..0.011 rows=1 loops=10,409)

  • Sort Key: brk_4.breakvalue DESC
  • Sort Method: quicksort Memory: 25kB
35. 41.636 41.636 ↑ 1.0 1 10,409

Seq Scan on fil_processingtime_break brk_4 (cost=0.00..1.04 rows=1 width=64) (actual time=0.003..0.004 rows=1 loops=10,409)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
  • Rows Removed by Filter: 2
36. 5.358 11.481 ↓ 1.0 1,330 1

Hash Join (cost=72.43..827.47 rows=1,325 width=90) (actual time=4.405..11.481 rows=1,330 loops=1)

  • Hash Cond: (line_2.m_movement_id = mov.m_movement_id)
37. 1.745 1.745 ↓ 1.0 1,374 1

Seq Scan on m_movementline line_2 (cost=0.00..47.67 rows=1,367 width=55) (actual time=0.005..1.745 rows=1,374 loops=1)

38. 2.012 4.378 ↑ 1.0 1,247 1

Hash (cost=56.46..56.46 rows=1,277 width=41) (actual time=4.378..4.378 rows=1,247 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 111kB
39. 2.366 2.366 ↑ 1.0 1,247 1

Seq Scan on m_movement mov (cost=0.00..56.46 rows=1,277 width=41) (actual time=0.009..2.366 rows=1,247 loops=1)

  • Filter: (docstatus <> 'VO'::bpchar)
  • Rows Removed by Filter: 44
40. 0.005 0.017 ↑ 3.0 1 1

Hash (cost=10.88..10.88 rows=3 width=6) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.012 0.012 ↑ 3.0 1 1

Index Scan using c_orderline_order on c_orderline (cost=0.29..10.88 rows=3 width=6) (actual time=0.010..0.012 rows=1 loops=1)

  • Index Cond: (c_order_id = '1002525'::numeric)
Planning time : 1.770 ms
Execution time : 1,331.066 ms