explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AknR

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 19,796.523 ↑ 1,401.3 6 1

Sort (cost=145,972.37..145,993.39 rows=8,408 width=278) (actual time=19,796.523..19,796.523 rows=6 loops=1)

  • Sort Key: (CASE WHEN (p.cof_productclass_id = ANY ('{5000012,5000010,5000011,5000019,5000013}'::numeric[])) THEN 'MDF'::text ELSE 'FERRAGEM'::text END), bp.name, io.documentno, ol.line, whouse.name
  • Sort Method: quicksort Memory: 26kB
2. 42.979 19,796.501 ↑ 1,401.3 6 1

Hash Join (cost=142,556.68..145,424.27 rows=8,408 width=278) (actual time=17,823.017..19,796.501 rows=6 loops=1)

  • Hash Cond: (iol.m_inoutline_id = t_selection2.t_selection_id)
3. 89.729 19,753.485 ↓ 33.1 556,000 1

Group (cost=142,552.82..145,072.53 rows=16,816 width=331) (actual time=16,407.822..19,753.485 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
4. 481.243 19,663.756 ↓ 39.7 556,000 1

Gather Merge (cost=142,552.82..144,476.94 rows=14,014 width=299) (actual time=16,407.820..19,663.756 rows=556,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 1,556.178 19,182.513 ↓ 26.4 185,333 3 / 3

Group (cost=141,552.80..141,859.36 rows=7,007 width=299) (actual time=16,376.372..19,182.513 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
6. 12,737.421 17,626.335 ↓ 533.2 3,736,101 3 / 3

Sort (cost=141,552.80..141,570.32 rows=7,007 width=299) (actual time=16,376.367..17,626.335 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: 903,544kB
  • Worker 0: Sort Method: external merge Disk: 899,920kB
  • Worker 1: Sort Method: external merge Disk: 897,872kB
7. 1,173.416 4,888.914 ↓ 533.2 3,736,101 3 / 3

Hash Left Join (cost=139,142.96..141,105.24 rows=7,007 width=299) (actual time=1,054.037..4,888.914 rows=3,736,101 loops=3)

  • Hash Cond: (iol.c_uom_id = um.c_uom_id)
8. 769.029 3,715.459 ↓ 533.2 3,736,101 3 / 3

Hash Left Join (cost=139,138.85..141,038.11 rows=7,007 width=268) (actual time=1,053.989..3,715.459 rows=3,736,101 loops=3)

  • Hash Cond: (p.cof_grupofornecedor_id = gf.cof_grupofornecedor_id)
9. 826.863 2,946.354 ↓ 533.2 3,736,101 3 / 3

Hash Join (cost=139,128.33..141,009.07 rows=7,007 width=260) (actual time=1,053.901..2,946.354 rows=3,736,101 loops=3)

  • Hash Cond: (io.m_warehouse_id = whouse.m_warehouse_id)
10. 903.542 2,119.470 ↓ 693.0 3,736,101 3 / 3

Merge Join (cost=139,127.11..140,987.69 rows=5,391 width=253) (actual time=1,053.847..2,119.470 rows=3,736,101 loops=3)

  • Merge Cond: ((io.m_warehouse_id = loc.m_warehouse_id) AND (p.m_product_id = "*SELECT* 1".m_product_id))
11. 456.828 906.682 ↑ 1.3 187,523 3 / 3

Sort (cost=128,351.99..128,943.07 rows=236,431 width=246) (actual time=879.351..906.682 rows=187,523 loops=3)

  • Sort Key: io.m_warehouse_id, iol.m_product_id
  • Sort Method: quicksort Memory: 66,294kB
  • Worker 0: Sort Method: quicksort Memory: 66,047kB
  • Worker 1: Sort Method: quicksort Memory: 66,130kB
12. 68.097 449.854 ↑ 1.3 187,523 3 / 3

Parallel Hash Left Join (cost=83,858.33..107,249.27 rows=236,431 width=246) (actual time=228.233..449.854 rows=187,523 loops=3)

  • Hash Cond: (iol.c_orderline_id = ol.c_orderline_id)
13. 52.280 278.705 ↑ 1.3 187,523 3 / 3

Parallel Hash Join (cost=35,755.65..58,525.97 rows=236,431 width=247) (actual time=123.868..278.705 rows=187,523 loops=3)

  • Hash Cond: (iol.m_product_id = p.m_product_id)
14. 84.445 220.990 ↑ 1.3 187,523 3 / 3

Parallel Hash Join (cost=34,265.99..56,415.55 rows=236,431 width=187) (actual time=118.310..220.990 rows=187,523 loops=3)

  • Hash Cond: (iol.m_inout_id = io.m_inout_id)
15. 18.513 18.513 ↑ 1.2 199,440 3 / 3

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

16. 18.291 118.032 ↑ 1.2 45,052 3 / 3

Parallel Hash (cost=33,563.54..33,563.54 rows=56,196 width=118) (actual time=118.032..118.032 rows=45,052 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 23,552kB
17. 13.225 99.741 ↑ 1.2 45,052 3 / 3

Parallel Hash Left Join (cost=25,646.17..33,563.54 rows=56,196 width=118) (actual time=36.809..99.741 rows=45,052 loops=3)

  • Hash Cond: (io.c_order_id = o.c_order_id)
18. 10.819 59.628 ↑ 1.2 45,052 3 / 3

Parallel Hash Left Join (cost=3,709.78..11,479.63 rows=56,196 width=119) (actual time=9.700..59.628 rows=45,052 loops=3)

  • Hash Cond: (io.c_bpartner_id = bp.c_bpartner_id)
19. 8.633 45.349 ↑ 1.2 45,052 3 / 3

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

  • Hash Cond: (io.cof_m_shipregion_id = cmsr.cof_m_shipregion_id)
20. 19.056 36.696 ↑ 1.2 45,052 3 / 3

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

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

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

  • Filter: (docstatus <> ALL ('{RE,VO}'::bpchar[]))
  • Rows Removed by Filter: 2,338
22. 3.174 6.019 ↓ 1.0 23,520 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,429kB
23. 2.845 2.845 ↓ 1.0 23,520 3 / 3

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

24. 0.007 0.020 ↑ 1.0 40 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
25. 0.013 0.013 ↑ 1.0 40 3 / 3

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

26. 1.343 3.460 ↑ 1.8 7,052 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,760kB
27. 2.117 2.117 ↑ 1.8 7,052 3 / 3

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

28. 8.269 26.888 ↑ 1.2 47,037 3 / 3

Parallel Hash (cost=21,201.73..21,201.73 rows=58,773 width=12) (actual time=26.888..26.888 rows=47,037 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,704kB
29. 18.619 18.619 ↑ 1.2 47,037 3 / 3

Parallel Seq Scan on c_order o (cost=0.00..21,201.73 rows=58,773 width=12) (actual time=0.011..18.619 rows=47,037 loops=3)

30. 2.237 5.435 ↑ 1.8 7,347 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,304kB
31. 3.198 3.198 ↑ 1.8 7,347 3 / 3

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

32. 45.831 103.052 ↑ 1.2 237,830 3 / 3

Parallel Hash (cost=44,402.30..44,402.30 rows=296,030 width=11) (actual time=103.052..103.052 rows=237,830 loops=3)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 41,728kB
33. 57.221 57.221 ↑ 1.2 237,830 3 / 3

Parallel Seq Scan on c_orderline ol (cost=0.00..44,402.30 rows=296,030 width=11) (actual time=0.011..57.221 rows=237,830 loops=3)

34. 239.678 309.246 ↓ 574.1 3,749,123 3 / 3

Sort (cost=10,759.58..10,775.91 rows=6,531 width=36) (actual time=174.490..309.246 rows=3,749,123 loops=3)

  • Sort Key: loc.m_warehouse_id, "*SELECT* 1".m_product_id
  • Sort Method: quicksort Memory: 10,943kB
  • Worker 0: Sort Method: quicksort Memory: 10,943kB
  • Worker 1: Sort Method: quicksort Memory: 10,943kB
35. 18.070 69.568 ↓ 15.4 100,744 3 / 3

Hash Join (cost=1.29..10,345.74 rows=6,531 width=36) (actual time=0.041..69.568 rows=100,744 loops=3)

  • Hash Cond: ("*SELECT* 1".m_locator_id = loc.m_locator_id)
36. 4.259 51.489 ↓ 1.0 100,744 3 / 3

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

37. 12.183 46.021 ↑ 1.0 99,525 3 / 3

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

38. 4.013 33.838 ↑ 1.0 99,525 3 / 3

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

39. 12.023 28.150 ↓ 1.0 98,525 3 / 3

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

40. 16.127 16.127 ↓ 1.0 98,525 3 / 3

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

41. 0.127 1.675 ↑ 1.5 1,000 3 / 3

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

42. 0.230 1.548 ↑ 1.5 1,000 3 / 3

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

  • Hash Cond: (sr.m_warehouse_id = w.m_warehouse_id)
43. 1.311 1.311 ↑ 1.5 1,000 3 / 3

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

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 4,677
44. 0.003 0.007 ↑ 1.0 10 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 0.004 0.004 ↑ 1.0 10 3 / 3

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

46. 0.157 1.209 ↓ 1.4 1,219 3 / 3

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

47. 0.278 1.052 ↓ 1.4 1,219 3 / 3

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

  • Hash Cond: (so.m_warehouse_id = w_1.m_warehouse_id)
48. 0.769 0.769 ↓ 1.4 1,219 3 / 3

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

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 4,458
49. 0.002 0.005 ↑ 1.0 10 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.003 0.003 ↑ 1.0 10 3 / 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)

51. 0.003 0.009 ↑ 1.0 13 3 / 3

Hash (cost=1.13..1.13 rows=13 width=26) (actual time=0.009..0.009 rows=13 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.006 0.006 ↑ 1.0 13 3 / 3

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

53. 0.003 0.021 ↑ 1.0 10 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
54. 0.018 0.018 ↑ 1.0 10 3 / 3

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

55. 0.033 0.076 ↑ 1.0 245 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
56. 0.043 0.043 ↑ 1.0 245 3 / 3

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

57. 0.013 0.039 ↑ 1.0 94 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
58. 0.026 0.026 ↑ 1.0 94 3 / 3

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

59. 0.002 0.037 ↑ 3.6 8 1

Hash (cost=3.49..3.49 rows=29 width=6) (actual time=0.037..0.037 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.004 0.035 ↑ 3.6 8 1

HashAggregate (cost=3.20..3.49 rows=29 width=6) (actual time=0.034..0.035 rows=8 loops=1)

  • Group Key: t_selection2.t_selection_id
61. 0.031 0.031 ↑ 3.6 8 1

Index Only Scan using t_selection2_pkey on t_selection2 (cost=0.42..3.13 rows=29 width=6) (actual time=0.029..0.031 rows=8 loops=1)

  • Index Cond: (ad_pinstance_id = '6956601'::numeric)
  • Heap Fetches: 8
Planning time : 9.094 ms
Execution time : 19,991.761 ms