explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nj2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 34,719.297 ↓ 33.1 556,000 1

Group (cost=96,963.31..99,483.02 rows=16,816 width=312) (actual time=31,090.007..34,719.297 rows=556,000 loops=1)

  • Group Key: whouse.m_warehouse_id, io.m_inout_id, iol.m_inoutline_id, o.documentno, bp.name, bpl.name, um.name, gf.name, (CASE WHEN (p.cof_productclass_id = ANY ('{5000012,5000010,5000011,5000019,5000013}'::numeric[])) THEN 'MDF'::text ELSE 'FERRAGEM'::text END), cmsr.cof_m_shipregion_id, p.name, p.value, ol.line, loc.value
2. 0.000 35,012.337 ↓ 39.7 556,000 1

Gather Merge (cost=96,963.31..98,887.43 rows=14,014 width=312) (actual time=31,090.005..35,012.337 rows=556,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 4,687.440 97,162.239 ↓ 26.4 185,333 3

Group (cost=95,963.29..96,269.85 rows=7,007 width=312) (actual time=29,490.804..32,387.413 rows=185,333 loops=3)

  • Group Key: whouse.m_warehouse_id, io.m_inout_id, iol.m_inoutline_id, o.documentno, bp.name, bpl.name, um.name, gf.name, (CASE WHEN (p.cof_productclass_id = ANY ('{5000012,5000010,5000011,5000019,5000013}'::numeric[])) THEN 'MDF'::text ELSE 'FERRAGEM'::text END), cmsr.cof_m_shipregion_id, p.name, p.value, ol.line, loc.value
4. 38,912.457 92,474.799 ↓ 533.2 3,736,101 3

Sort (cost=95,963.29..95,980.81 rows=7,007 width=312) (actual time=29,490.801..30,824.933 rows=3,736,101 loops=3)

  • Sort Key: whouse.m_warehouse_id, io.m_inout_id, iol.m_inoutline_id, o.documentno, bp.name, bpl.name, um.name, gf.name, (CASE WHEN (p.cof_productclass_id = ANY ('{5000012,5000010,5000011,5000019,5000013}'::numeric[])) THEN 'MDF'::text ELSE 'FERRAGEM'::text END), cmsr.cof_m_shipregion_id, p.name, p.value, ol.line, loc.value
  • Sort Method: external merge Disk: 1019376kB
  • Worker 0: Sort Method: external merge Disk: 918472kB
  • Worker 1: Sort Method: external merge Disk: 917184kB
5. 4,199.325 53,562.342 ↓ 533.2 3,736,101 3

Hash Left Join (cost=84,376.71..95,515.73 rows=7,007 width=312) (actual time=524.430..17,854.114 rows=3,736,101 loops=3)

  • Hash Cond: (iol.c_uom_id = um.c_uom_id)
6. 2,905.857 49,362.912 ↓ 533.2 3,736,101 3

Hash Left Join (cost=84,372.60..95,448.60 rows=7,007 width=281) (actual time=524.385..16,454.304 rows=3,736,101 loops=3)

  • Hash Cond: (p.cof_grupofornecedor_id = gf.cof_grupofornecedor_id)
7. 0.000 46,456.836 ↓ 533.2 3,736,101 3

Nested Loop Left Join (cost=84,362.09..95,419.56 rows=7,007 width=273) (actual time=524.307..15,485.612 rows=3,736,101 loops=3)

8. 8,959.732 25,510.527 ↓ 533.2 3,736,101 3

Nested Loop Left Join (cost=84,361.66..91,756.94 rows=7,007 width=274) (actual time=524.283..8,503.509 rows=3,736,101 loops=3)

9. 3,159.666 5,342.493 ↓ 533.2 3,736,101 3

Merge Join (cost=84,361.24..87,490.06 rows=7,007 width=275) (actual time=524.253..1,780.831 rows=3,736,101 loops=3)

  • Merge Cond: ((p.m_product_id = st.m_product_id) AND (loc.m_locator_id = st.m_locator_id))
10. 678.168 1,403.421 ↑ 1.6 187,523 3

Sort (cost=65,938.60..66,707.00 rows=307,360 width=287) (actual time=432.995..467.807 rows=187,523 loops=3)

  • Sort Key: iol.m_product_id, loc.m_locator_id
  • Sort Method: quicksort Memory: 103917kB
  • Worker 0: Sort Method: quicksort Memory: 93524kB
  • Worker 1: Sort Method: quicksort Memory: 93527kB
11. 185.178 725.253 ↑ 1.6 187,523 3

Parallel Hash Join (cost=14,826.33..37,923.40 rows=307,360 width=287) (actual time=92.465..241.751 rows=187,523 loops=3)

  • Hash Cond: (iol.m_inout_id = io.m_inout_id)
12. 214.257 277.278 ↑ 1.2 199,440 3

Parallel Hash Join (cost=1,489.67..22,374.86 rows=248,322 width=135) (actual time=4.547..92.426 rows=199,440 loops=3)

  • Hash Cond: (iol.m_product_id = p.m_product_id)
13. 49.539 49.539 ↑ 1.2 199,440 3

Parallel Seq Scan on m_inoutline iol (cost=0.00..20,233.22 rows=248,322 width=75) (actual time=0.005..16.513 rows=199,440 loops=3)

14. 4.491 13.482 ↑ 1.8 7,347 3

Parallel Hash (cost=1,327.63..1,327.63 rows=12,963 width=60) (actual time=4.494..4.494 rows=7,347 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2336kB
15. 8.991 8.991 ↑ 1.8 7,347 3

Parallel Seq Scan on m_product p (cost=0.00..1,327.63 rows=12,963 width=60) (actual time=0.013..2.997 rows=7,347 loops=3)

16. 60.558 262.797 ↑ 1.6 45,052 3

Parallel Hash (cost=12,423.48..12,423.48 rows=73,055 width=158) (actual time=87.599..87.599 rows=45,052 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 28480kB
17. 30.408 202.239 ↑ 1.6 45,052 3

Hash Join (cost=3,712.34..12,423.48 rows=73,055 width=158) (actual time=8.712..67.413 rows=45,052 loops=3)

  • Hash Cond: (io.m_warehouse_id = whouse.m_warehouse_id)
18. 31.470 171.717 ↑ 1.2 45,052 3

Parallel Hash Left Join (cost=3,709.78..11,479.63 rows=56,196 width=125) (actual time=8.666..57.239 rows=45,052 loops=3)

  • Hash Cond: (io.c_bpartner_id = bp.c_bpartner_id)
19. 24.699 132.285 ↑ 1.2 45,052 3

Hash Left Join (cost=1,201.88..8,824.18 rows=56,196 width=97) (actual time=5.907..44.095 rows=45,052 loops=3)

  • Hash Cond: (io.cof_m_shipregion_id = cmsr.cof_m_shipregion_id)
20. 57.120 107.523 ↑ 1.2 45,052 3

Hash Left Join (cost=1,199.97..8,661.29 rows=56,196 width=82) (actual time=5.877..35.841 rows=45,052 loops=3)

  • Hash Cond: (io.c_bpartner_location_id = bpl.c_bpartner_location_id)
21. 33.018 33.018 ↑ 1.2 45,052 3

Parallel Seq Scan on m_inout io (cost=0.00..7,313.78 rows=56,196 width=76) (actual time=0.010..11.006 rows=45,052 loops=3)

  • Filter: (docstatus <> ALL ('{RE,VO}'::bpchar[]))
  • Rows Removed by Filter: 2338
22. 9.006 17.385 ↓ 1.0 23,520 3

Hash (cost=906.10..906.10 rows=23,510 width=18) (actual time=5.795..5.795 rows=23,520 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1429kB
23. 8.379 8.379 ↓ 1.0 23,520 3

Seq Scan on c_bpartner_location bpl (cost=0.00..906.10 rows=23,510 width=18) (actual time=0.015..2.793 rows=23,520 loops=3)

24. 0.024 0.063 ↑ 1.0 40 3

Hash (cost=1.40..1.40 rows=40 width=22) (actual time=0.020..0.021 rows=40 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.039 0.039 ↑ 1.0 40 3

Seq Scan on cof_m_shipregion cmsr (cost=0.00..1.40 rows=40 width=22) (actual time=0.009..0.013 rows=40 loops=3)

26. 3.564 7.962 ↑ 1.8 7,052 3

Parallel Hash (cost=2,352.40..2,352.40 rows=12,440 width=34) (actual time=2.653..2.654 rows=7,052 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1792kB
27. 4.398 4.398 ↑ 1.8 7,052 3

Parallel Seq Scan on c_bpartner bp (cost=0.00..2,352.40 rows=12,440 width=34) (actual time=0.007..1.466 rows=7,052 loops=3)

28. 0.012 0.114 ↑ 1.0 13 3

Hash (cost=2.40..2.40 rows=13 width=47) (actual time=0.038..0.038 rows=13 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
29. 0.033 0.102 ↑ 1.0 13 3

Hash Join (cost=1.23..2.40 rows=13 width=47) (actual time=0.030..0.034 rows=13 loops=3)

  • Hash Cond: (loc.m_warehouse_id = whouse.m_warehouse_id)
30. 0.027 0.027 ↑ 1.0 13 3

Seq Scan on m_locator loc (cost=0.00..1.13 rows=13 width=26) (actual time=0.008..0.009 rows=13 loops=3)

31. 0.012 0.042 ↑ 1.0 10 3

Hash (cost=1.10..1.10 rows=10 width=21) (actual time=0.014..0.014 rows=10 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.030 0.030 ↑ 1.0 10 3

Seq Scan on m_warehouse whouse (cost=0.00..1.10 rows=10 width=21) (actual time=0.009..0.010 rows=10 loops=3)

33. 611.538 779.406 ↓ 37.3 3,749,150 3

Sort (cost=18,422.64..18,673.83 rows=100,473 width=32) (actual time=91.253..259.802 rows=3,749,150 loops=3)

  • Sort Key: st.m_product_id, st.m_locator_id
  • Sort Method: quicksort Memory: 7795kB
  • Worker 0: Sort Method: quicksort Memory: 7795kB
  • Worker 1: Sort Method: quicksort Memory: 7795kB
34. 17.859 167.868 ↓ 1.0 100,744 3

Subquery Scan on st (cost=0.00..10,075.12 rows=100,473 width=32) (actual time=0.026..55.956 rows=100,744 loops=3)

35. 12.216 150.009 ↓ 1.0 100,744 3

Append (cost=0.00..9,070.39 rows=100,473 width=338) (actual time=0.025..50.003 rows=100,744 loops=3)

36. 34.395 134.295 ↑ 1.0 99,525 3

Result (cost=0.00..8,183.96 rows=99,606 width=338) (actual time=0.024..44.765 rows=99,525 loops=3)

37. 12.066 99.900 ↑ 1.0 99,525 3

Append (cost=0.00..6,938.88 rows=99,606 width=310) (actual time=0.023..33.300 rows=99,525 loops=3)

38. 35.175 83.133 ↓ 1.0 98,525 3

Subquery Scan on *SELECT* 1 (cost=0.00..6,049.44 rows=98,153 width=148) (actual time=0.023..27.711 rows=98,525 loops=3)

39. 47.958 47.958 ↓ 1.0 98,525 3

Seq Scan on m_storageonhand s (cost=0.00..4,822.53 rows=98,153 width=120) (actual time=0.022..15.986 rows=98,525 loops=3)

40. 0.375 4.701 ↑ 1.5 1,000 3

Subquery Scan on *SELECT* 2 (cost=1.23..391.41 rows=1,453 width=151) (actual time=0.032..1.567 rows=1,000 loops=3)

41. 0.669 4.326 ↑ 1.5 1,000 3

Hash Join (cost=1.23..373.25 rows=1,453 width=123) (actual time=0.032..1.442 rows=1,000 loops=3)

  • Hash Cond: (sr.m_warehouse_id = w.m_warehouse_id)
42. 3.633 3.633 ↑ 1.5 1,000 3

Seq Scan on m_storagereservation sr (cost=0.00..366.60 rows=1,453 width=108) (actual time=0.017..1.211 rows=1,000 loops=3)

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 4677
43. 0.009 0.024 ↑ 1.0 10 3

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.008..0.008 rows=10 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.015 0.015 ↑ 1.0 10 3

Seq Scan on m_warehouse w (cost=0.00..1.10 rows=10 width=13) (actual time=0.004..0.005 rows=10 loops=3)

45. 0.495 3.498 ↓ 1.4 1,219 3

Subquery Scan on *SELECT* 3 (cost=1.23..384.07 rows=867 width=179) (actual time=0.023..1.166 rows=1,219 loops=3)

46. 0.786 3.003 ↓ 1.4 1,219 3

Hash Join (cost=1.23..371.06 rows=867 width=123) (actual time=0.022..1.001 rows=1,219 loops=3)

  • Hash Cond: (so.m_warehouse_id = w_1.m_warehouse_id)
47. 2.202 2.202 ↓ 1.4 1,219 3

Seq Scan on m_storagereservation so (cost=0.00..366.60 rows=867 width=108) (actual time=0.013..0.734 rows=1,219 loops=3)

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 4458
48. 0.006 0.015 ↑ 1.0 10 3

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.005..0.005 rows=10 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.009 0.009 ↑ 1.0 10 3

Seq Scan on m_warehouse w_1 (cost=0.00..1.10 rows=10 width=13) (actual time=0.002..0.003 rows=10 loops=3)

50. 11,208.302 11,208.302 ↑ 1.0 1 11,208,302

Index Scan using c_order_pkey on c_order o (cost=0.42..0.61 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=11,208,302)

  • Index Cond: (c_order_id = io.c_order_id)
51. 22,416.604 22,416.604 ↑ 1.0 1 11,208,302

Index Scan using c_orderline_pkey on c_orderline ol (cost=0.42..0.52 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=11,208,302)

  • Index Cond: (iol.c_orderline_id = c_orderline_id)
52. 0.105 0.219 ↑ 1.0 245 3

Hash (cost=7.45..7.45 rows=245 width=22) (actual time=0.073..0.073 rows=245 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
53. 0.114 0.114 ↑ 1.0 245 3

Seq Scan on cof_grupofornecedor gf (cost=0.00..7.45 rows=245 width=22) (actual time=0.011..0.038 rows=245 loops=3)

54. 0.042 0.105 ↑ 1.0 94 3

Hash (cost=2.94..2.94 rows=94 width=18) (actual time=0.035..0.035 rows=94 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
55. 0.063 0.063 ↑ 1.0 94 3

Seq Scan on c_uom um (cost=0.00..2.94 rows=94 width=18) (actual time=0.008..0.021 rows=94 loops=3)

Planning time : 5.209 ms
Execution time : 35,334.438 ms