explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8GHt

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 2,898.779 ↑ 35,464.8 8 1

Sort (cost=208,427.54..209,136.83 rows=283,718 width=278) (actual time=2,898.779..2,898.779 rows=8 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: 27kB
2. 38.938 2,898.759 ↑ 35,464.8 8 1

Hash Join (cost=161,771.42..182,731.06 rows=283,718 width=278) (actual time=2,705.716..2,898.759 rows=8 loops=1)

  • Hash Cond: (iol.m_inoutline_id = t_selection2.t_selection_id)
3. 633.920 2,859.799 ↑ 1.0 563,214 1

HashAggregate (cost=161,767.57..170,988.39 rows=567,435 width=331) (actual time=2,673.551..2,859.799 rows=563,214 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. 209.733 2,225.879 ↑ 1.0 563,214 1

Hash Left Join (cost=96,869.35..141,907.34 rows=567,435 width=299) (actual time=348.275..2,225.879 rows=563,214 loops=1)

  • Hash Cond: (iol.m_locator_id = loc.m_locator_id)
5. 131.820 2,016.139 ↑ 1.0 563,214 1

Hash Left Join (cost=96,868.06..136,394.41 rows=567,435 width=267) (actual time=348.264..2,016.139 rows=563,214 loops=1)

  • Hash Cond: (io.m_warehouse_id = whouse.m_warehouse_id)
6. 128.480 1,884.313 ↑ 1.0 563,214 1

Hash Left Join (cost=96,866.83..134,272.40 rows=567,435 width=253) (actual time=348.256..1,884.313 rows=563,214 loops=1)

  • Hash Cond: (iol.c_uom_id = um.c_uom_id)
7. 139.418 1,755.807 ↑ 1.0 563,214 1

Hash Left Join (cost=96,862.72..132,711.60 rows=567,435 width=247) (actual time=348.227..1,755.807 rows=563,214 loops=1)

  • Hash Cond: (p.cof_grupofornecedor_id = gf.cof_grupofornecedor_id)
8. 211.695 1,616.329 ↑ 1.0 563,214 1

Hash Left Join (cost=96,852.21..131,200.50 rows=567,435 width=239) (actual time=348.161..1,616.329 rows=563,214 loops=1)

  • Hash Cond: (iol.c_orderline_id = ol.c_orderline_id)
9. 154.513 1,195.717 ↑ 1.0 563,214 1

Hash Left Join (cost=39,424.61..72,283.38 rows=567,435 width=240) (actual time=138.997..1,195.717 rows=563,214 loops=1)

  • Hash Cond: (io.c_order_id = o.c_order_id)
10. 145.149 996.793 ↑ 1.0 563,214 1

Hash Left Join (cost=15,632.35..47,001.56 rows=567,435 width=241) (actual time=94.521..996.793 rows=563,214 loops=1)

  • Hash Cond: (io.c_bpartner_id = bp.c_bpartner_id)
11. 130.893 845.076 ↑ 1.0 563,214 1

Hash Left Join (cost=12,928.27..42,807.66 rows=567,435 width=219) (actual time=87.942..845.076 rows=563,214 loops=1)

  • Hash Cond: (io.cof_m_shipregion_id = cmsr.cof_m_shipregion_id)
12. 147.782 714.170 ↑ 1.0 563,214 1

Hash Left Join (cost=12,926.37..41,180.16 rows=567,435 width=204) (actual time=87.918..714.170 rows=563,214 loops=1)

  • Hash Cond: (io.c_bpartner_location_id = bpl.c_bpartner_location_id)
13. 177.301 560.870 ↑ 1.0 563,214 1

Hash Left Join (cost=11,726.39..38,490.40 rows=567,435 width=198) (actual time=82.387..560.870 rows=563,214 loops=1)

  • Hash Cond: (iol.m_product_id = p.m_product_id)
14. 255.441 373.234 ↑ 1.0 563,214 1

Hash Right Join (cost=10,032.56..35,306.77 rows=567,435 width=151) (actual time=72.039..373.234 rows=563,214 loops=1)

  • Hash Cond: (iol.m_inout_id = io.m_inout_id)
15. 45.829 45.829 ↓ 1.0 598,975 1

Seq Scan on m_inoutline iol (cost=0.00..23,709.73 rows=595,973 width=81) (actual time=0.004..45.829 rows=598,975 loops=1)

16. 23.300 71.964 ↓ 1.0 135,312 1

Hash (cost=8,346.67..8,346.67 rows=134,871 width=76) (actual time=71.964..71.964 rows=135,312 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 17018kB
17. 48.664 48.664 ↓ 1.0 135,312 1

Seq Scan on m_inout io (cost=0.00..8,346.67 rows=134,871 width=76) (actual time=0.009..48.664 rows=135,312 loops=1)

  • Filter: (docstatus <> ALL ('{RE,VO}'::bpchar[]))
  • Rows Removed by Filter: 7025
18. 3.052 10.335 ↓ 1.0 22,049 1

Hash (cost=1,418.37..1,418.37 rows=22,037 width=60) (actual time=10.335..10.335 rows=22,049 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2178kB
19. 7.283 7.283 ↓ 1.0 22,049 1

Seq Scan on m_product p (cost=0.00..1,418.37 rows=22,037 width=60) (actual time=0.004..7.283 rows=22,049 loops=1)

20. 2.776 5.518 ↓ 1.0 23,523 1

Hash (cost=906.10..906.10 rows=23,510 width=18) (actual time=5.518..5.518 rows=23,523 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1429kB
21. 2.742 2.742 ↓ 1.0 23,523 1

Seq Scan on c_bpartner_location bpl (cost=0.00..906.10 rows=23,510 width=18) (actual time=0.005..2.742 rows=23,523 loops=1)

22. 0.005 0.013 ↑ 1.0 40 1

Hash (cost=1.40..1.40 rows=40 width=22) (actual time=0.013..0.013 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.008 0.008 ↑ 1.0 40 1

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

24. 2.658 6.568 ↑ 1.0 21,159 1

Hash (cost=2,439.59..2,439.59 rows=21,159 width=34) (actual time=6.568..6.568 rows=21,159 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1649kB
25. 3.910 3.910 ↑ 1.0 21,159 1

Seq Scan on c_bpartner bp (cost=0.00..2,439.59 rows=21,159 width=34) (actual time=0.003..3.910 rows=21,159 loops=1)

26. 15.295 44.411 ↓ 1.0 141,314 1

Hash (cost=22,026.56..22,026.56 rows=141,256 width=12) (actual time=44.411..44.411 rows=141,314 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8345kB
27. 29.116 29.116 ↓ 1.0 141,314 1

Seq Scan on c_order o (cost=0.00..22,026.56 rows=141,256 width=12) (actual time=0.003..29.116 rows=141,314 loops=1)

28. 97.280 208.917 ↓ 1.0 714,387 1

Hash (cost=48,546.71..48,546.71 rows=710,471 width=11) (actual time=208.917..208.917 rows=714,387 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 38889kB
29. 111.637 111.637 ↓ 1.0 714,387 1

Seq Scan on c_orderline ol (cost=0.00..48,546.71 rows=710,471 width=11) (actual time=0.004..111.637 rows=714,387 loops=1)

30. 0.027 0.060 ↑ 1.0 245 1

Hash (cost=7.45..7.45 rows=245 width=22) (actual time=0.060..0.060 rows=245 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
31. 0.033 0.033 ↑ 1.0 245 1

Seq Scan on cof_grupofornecedor gf (cost=0.00..7.45 rows=245 width=22) (actual time=0.005..0.033 rows=245 loops=1)

32. 0.011 0.026 ↑ 1.0 94 1

Hash (cost=2.94..2.94 rows=94 width=18) (actual time=0.025..0.026 rows=94 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
33. 0.015 0.015 ↑ 1.0 94 1

Seq Scan on c_uom um (cost=0.00..2.94 rows=94 width=18) (actual time=0.003..0.015 rows=94 loops=1)

34. 0.002 0.006 ↓ 1.1 11 1

Hash (cost=1.10..1.10 rows=10 width=21) (actual time=0.006..0.006 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.004 0.004 ↓ 1.1 11 1

Seq Scan on m_warehouse whouse (cost=0.00..1.10 rows=10 width=21) (actual time=0.003..0.004 rows=11 loops=1)

36. 0.002 0.007 ↓ 1.3 17 1

Hash (cost=1.13..1.13 rows=13 width=19) (actual time=0.007..0.007 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.005 0.005 ↓ 1.3 17 1

Seq Scan on m_locator loc (cost=0.00..1.13 rows=13 width=19) (actual time=0.003..0.005 rows=17 loops=1)

38. 0.002 0.022 ↑ 3.6 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.005 0.020 ↑ 3.6 8 1

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

  • Group Key: t_selection2.t_selection_id
40. 0.015 0.015 ↑ 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.014..0.015 rows=8 loops=1)

  • Index Cond: (ad_pinstance_id = '6956601'::numeric)
  • Heap Fetches: 8
Planning time : 3.511 ms
Execution time : 2,899.874 ms