explain.depesz.com

PostgreSQL's explain analyze made readable

Result: runc : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: replenishment; plan #5LIH; plan #BNVh; plan #9C6; plan #CDvl; plan #o8ML; plan #cTxEO; plan #4jYJ; plan #Gzo1; plan #MV4a; plan #9OZe; plan #VELI; plan #SYTt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 52.407 1,230.080 ↑ 7,362.0 1 1

Nested Loop Semi Join (cost=47.24..93,131.28 rows=7,362 width=378) (actual time=32.246..1,230.080 rows=1 loops=1)

  • Join Filter: (line.ref_orderline_id = ol.c_orderline_id)
  • Rows Removed by Join Filter: 14,378
2. 27.746 1,148.915 ↑ 1.0 14,379 1

Append (cost=46.96..92,310.57 rows=14,724 width=96) (actual time=3.434..1,148.915 rows=14,379 loops=1)

3. 1.722 29.454 ↓ 1.0 446 1

Hash Join (cost=46.96..966.84 rows=445 width=91) (actual time=3.432..29.454 rows=446 loops=1)

  • Hash Cond: (line.m_requisition_id = req.m_requisition_id)
4. 13.541 26.378 ↑ 1.0 446 1

Hash Right Join (cost=24.04..706.39 rows=446 width=82) (actual time=2.060..26.378 rows=446 loops=1)

  • Hash Cond: (poline.c_orderline_id = line.c_orderline_id)
5. 11.452 11.452 ↑ 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.452 rows=9,132 loops=1)

6. 0.717 1.385 ↑ 1.0 446 1

Hash (cost=18.46..18.46 rows=446 width=77) (actual time=1.385..1.385 rows=446 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
7. 0.668 0.668 ↑ 1.0 446 1

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

8. 0.673 1.354 ↓ 1.0 442 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
9. 0.681 0.681 ↓ 1.0 442 1

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

10. 31.281 1,080.426 ↑ 1.0 12,603 1

Subquery Scan on *SELECT* 2 (cost=2,481.56..90,498.66 rows=12,953 width=97) (actual time=137.152..1,080.426 rows=12,603 loops=1)

11. 305.471 1,049.145 ↑ 1.0 12,603 1

Hash Join (cost=2,481.56..90,369.13 rows=12,953 width=97) (actual time=137.147..1,049.145 rows=12,603 loops=1)

  • Hash Cond: (line_1.m_product_id = product.m_product_id)
12. 41.149 121.234 ↑ 1.0 12,603 1

Hash Join (cost=718.87..3,165.21 rows=12,953 width=99) (actual time=42.529..121.234 rows=12,603 loops=1)

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

Seq Scan on m_productionline line_1 (cost=0.00..2,268.24 rows=12,953 width=63) (actual time=0.007..37.602 rows=12,603 loops=1)

  • Filter: ((isendproduct = 'Y'::bpchar) AND (ismanual = 'N'::bpchar))
  • Rows Removed by Filter: 40,013
14. 20.748 42.483 ↑ 1.0 12,794 1

Hash (cost=558.94..558.94 rows=12,794 width=42) (actual time=42.483..42.483 rows=12,794 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 996kB
15. 21.735 21.735 ↑ 1.0 12,794 1

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

16. 41.946 94.236 ↑ 1.0 26,831 1

Hash (cost=1,427.31..1,427.31 rows=26,831 width=11) (actual time=94.236..94.236 rows=26,831 loops=1)

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

Seq Scan on m_product product (cost=0.00..1,427.31 rows=26,831 width=11) (actual time=0.007..52.290 rows=26,831 loops=1)

18.          

SubPlan (for Hash Join)

19. 50.412 189.045 ↑ 1.0 1 12,603

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

20. 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
21. 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
22. 10.970 37.298 ↑ 1.0 1 2,194

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

23. 15.358 26.328 ↑ 1.0 1 2,194

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

  • Sort Key: brk_1.breakvalue DESC
  • Sort Method: quicksort Memory: 25kB
24. 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
25. 41.636 156.135 ↑ 1.0 1 10,409

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

26. 62.454 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
27. 52.045 52.045 ↑ 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.005 rows=1 loops=10,409)

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

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

29. 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
30. 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))
31. 31.227 145.726 ↑ 1.0 1 10,409

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

32. 62.454 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
33. 52.045 52.045 ↑ 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.005 rows=1 loops=10,409)

  • Filter: ((line_1.movementqty >= breakvalue) AND (fil_processingtime_id = product.fil_processingtime_id))
  • Rows Removed by Filter: 2
34. 5.289 11.289 ↓ 1.0 1,330 1

Hash Join (cost=71.79..827.36 rows=1,326 width=90) (actual time=4.364..11.289 rows=1,330 loops=1)

  • Hash Cond: (line_2.m_movement_id = mov.m_movement_id)
35. 1.681 1.681 ↓ 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.681 rows=1,374 loops=1)

36. 1.991 4.319 ↑ 1.0 1,247 1

Hash (cost=56.14..56.14 rows=1,252 width=41) (actual time=4.319..4.319 rows=1,247 loops=1)

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

Seq Scan on m_movement mov (cost=0.00..56.14 rows=1,252 width=41) (actual time=0.009..2.328 rows=1,247 loops=1)

  • Filter: (docstatus <> 'VO'::bpchar)
  • Rows Removed by Filter: 44
38. 28.747 28.758 ↑ 3.0 1 14,379

Materialize (cost=0.29..10.90 rows=3 width=6) (actual time=0.001..0.002 rows=1 loops=14,379)

39. 0.011 0.011 ↑ 3.0 1 1

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

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