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. 0.000 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. 4,668.534 57,547.539 ↓ 26.4 185,333 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. 38,212.263 52,879.005 ↓ 533.2 3,736,101 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: 903544kB
  • Worker 0: Sort Method: external merge Disk: 899920kB
  • Worker 1: Sort Method: external merge Disk: 897872kB
7. 3,520.248 14,666.742 ↓ 533.2 3,736,101 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. 2,307.087 11,146.377 ↓ 533.2 3,736,101 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. 2,480.589 8,839.062 ↓ 533.2 3,736,101 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. 2,710.626 6,358.410 ↓ 693.0 3,736,101 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. 1,370.484 2,720.046 ↑ 1.3 187,523 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: 66294kB
  • Worker 0: Sort Method: quicksort Memory: 66047kB
  • Worker 1: Sort Method: quicksort Memory: 66130kB
12. 204.291 1,349.562 ↑ 1.3 187,523 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. 156.840 836.115 ↑ 1.3 187,523 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. 253.335 662.970 ↑ 1.3 187,523 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. 55.539 55.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.004..18.513 rows=199,440 loops=3)

16. 54.873 354.096 ↑ 1.2 45,052 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: 262144 Batches: 1 Memory Usage: 23552kB
17. 39.675 299.223 ↑ 1.2 45,052 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. 32.457 178.884 ↑ 1.2 45,052 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. 25.899 136.047 ↑ 1.2 45,052 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. 57.168 110.088 ↑ 1.2 45,052 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. 34.863 34.863 ↑ 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.020..11.621 rows=45,052 loops=3)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1429kB
23. 8.535 8.535 ↓ 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.010..2.845 rows=23,520 loops=3)

24. 0.021 0.060 ↑ 1.0 40 3

Hash (cost=1.40..1.40 rows=40 width=22) (actual time=0.020..0.020 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.008..0.013 rows=40 loops=3)

26. 4.029 10.380 ↑ 1.8 7,052 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: 32768 Batches: 1 Memory Usage: 1760kB
27. 6.351 6.351 ↑ 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.011..2.117 rows=7,052 loops=3)

28. 24.807 80.664 ↑ 1.2 47,037 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: 262144 Batches: 1 Memory Usage: 8704kB
29. 55.857 55.857 ↑ 1.2 47,037 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. 6.711 16.305 ↑ 1.8 7,347 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: 32768 Batches: 1 Memory Usage: 2304kB
31. 9.594 9.594 ↑ 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.012..3.198 rows=7,347 loops=3)

32. 137.493 309.156 ↑ 1.2 237,830 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: 1048576 Batches: 1 Memory Usage: 41728kB
33. 171.663 171.663 ↑ 1.2 237,830 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. 719.034 927.738 ↓ 574.1 3,749,123 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: 10943kB
  • Worker 0: Sort Method: quicksort Memory: 10943kB
  • Worker 1: Sort Method: quicksort Memory: 10943kB
35. 54.210 208.704 ↓ 15.4 100,744 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. 12.777 154.467 ↓ 1.0 100,744 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. 36.549 138.063 ↑ 1.0 99,525 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. 12.039 101.514 ↑ 1.0 99,525 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. 36.069 84.450 ↓ 1.0 98,525 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. 48.381 48.381 ↓ 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.023..16.127 rows=98,525 loops=3)

41. 0.381 5.025 ↑ 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.675 rows=1,000 loops=3)

42. 0.690 4.644 ↑ 1.5 1,000 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. 3.933 3.933 ↑ 1.5 1,000 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: 4677
44. 0.009 0.021 ↑ 1.0 10 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.012 0.012 ↑ 1.0 10 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.471 3.627 ↓ 1.4 1,219 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.834 3.156 ↓ 1.4 1,219 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. 2.307 2.307 ↓ 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.769 rows=1,219 loops=3)

  • Filter: ((qty <> '0'::numeric) AND (issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 4458
49. 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
50. 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)

51. 0.009 0.027 ↑ 1.0 13 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.018 0.018 ↑ 1.0 13 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.009 0.063 ↑ 1.0 10 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 0.054 0.054 ↑ 1.0 10 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.099 0.228 ↑ 1.0 245 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
56. 0.129 0.129 ↑ 1.0 245 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.039 0.117 ↑ 1.0 94 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
58. 0.078 0.078 ↑ 1.0 94 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: 1024 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