explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dHPe

Settings
# exclusive inclusive rows x rows loops node
1. 1.816 44,348.287 ↑ 91.1 1,088 1

Sort (cost=4,389,870.95..4,390,118.61 rows=99,063 width=617) (actual time=44,348.207..44,348.287 rows=1,088 loops=1)

  • Sort Key: return_inout.m_inout_id, return_inout.created
  • Sort Method: quicksort Memory: 610kB
2. 0.602 44,346.471 ↑ 91.1 1,088 1

Hash Left Join (cost=3,683,028.95..4,326,791.67 rows=99,063 width=617) (actual time=43,084.113..44,346.471 rows=1,088 loops=1)

  • Hash Cond: ((return_order.c_order_id)::text = (sales_order.order_return_id)::text)
3. 131.199 22,861.680 ↑ 91.1 1,088 1

Hash Left Join (cost=1,322,440.11..1,964,593.04 rows=99,063 width=423) (actual time=21,599.910..22,861.680 rows=1,088 loops=1)

  • Hash Cond: ((m_product_po.c_bpartner_id)::text = (supplier.c_bpartner_id)::text)
4. 2,444.550 22,380.680 ↑ 91.1 1,088 1

Hash Right Join (cost=1,244,554.94..1,867,958.84 rows=99,063 width=423) (actual time=21,250.071..22,380.680 rows=1,088 loops=1)

  • Hash Cond: (((return_orderline.c_order_id)::text = (return_order.c_order_id)::text) AND ((return_orderline.m_product_id)::text = (m_product.m_product_id)::text))
5. 1,792.139 1,792.139 ↑ 1.0 5,644,690 1

Seq Scan on c_orderline return_orderline (cost=0.00..443,331.65 rows=5,645,365 width=70) (actual time=0.023..1,792.139 rows=5,644,690 loops=1)

6. 1.563 18,143.991 ↑ 91.1 1,088 1

Hash (cost=1,237,650.99..1,237,650.99 rows=99,063 width=419) (actual time=18,143.991..18,143.991 rows=1,088 loops=1)

  • Buckets: 16,384 Batches: 16 Memory Usage: 157kB
7. 191.517 18,142.428 ↑ 91.1 1,088 1

Hash Left Join (cost=1,046,713.61..1,237,650.99 rows=99,063 width=419) (actual time=16,499.533..18,142.428 rows=1,088 loops=1)

  • Hash Cond: ((return_inout.c_order_id)::text = (return_order.c_order_id)::text)
8. 237.999 16,128.018 ↑ 91.1 1,088 1

Hash Left Join (cost=751,408.28..912,940.62 rows=99,063 width=409) (actual time=11,094.586..16,128.018 rows=1,088 loops=1)

  • Hash Cond: ((return_inout.c_bpartner_id)::text = (customer.c_bpartner_id)::text)
9. 2,077.747 15,523.017 ↑ 91.1 1,088 1

Hash Right Join (cost=673,523.11..816,500.41 rows=99,063 width=409) (actual time=10,717.538..15,523.017 rows=1,088 loops=1)

  • Hash Cond: ((m_product_po.m_product_id)::text = (m_product.m_product_id)::text)
10. 2,732.311 2,732.311 ↑ 1.0 1,508,180 1

Seq Scan on m_product_po (cost=0.00..96,198.94 rows=1,508,185 width=66) (actual time=0.022..2,732.311 rows=1,508,180 loops=1)

  • Filter: (iscurrentvendor = 'Y'::bpchar)
  • Rows Removed by Filter: 4,695
11. 0.994 10,712.959 ↑ 91.1 1,088 1

Hash (cost=667,446.82..667,446.82 rows=99,063 width=376) (actual time=10,712.959..10,712.959 rows=1,088 loops=1)

  • Buckets: 16,384 Batches: 16 Memory Usage: 160kB
12. 128.017 10,711.965 ↑ 91.1 1,088 1

Hash Left Join (cost=336,092.43..667,446.82 rows=99,063 width=376) (actual time=8,371.569..10,711.965 rows=1,088 loops=1)

  • Hash Cond: ((return_inoutline.m_product_id)::text = (m_product.m_product_id)::text)
13. 37.785 7,941.531 ↑ 91.1 1,088 1

Hash Join (cost=152,497.00..458,833.44 rows=99,063 width=368) (actual time=5,307.998..7,941.531 rows=1,088 loops=1)

  • Hash Cond: ((return_inoutline.m_inout_id)::text = (return_inout.m_inout_id)::text)
14. 447.935 2,825.745 ↑ 22.4 35,047 1

Hash Join (cost=1.16..252,304.19 rows=783,409 width=202) (actual time=0.403..2,825.745 rows=35,047 loops=1)

  • Hash Cond: ((return_inoutline.m_locator_id)::text = (m_locator.m_locator_id)::text)
15. 2,377.584 2,377.584 ↓ 1.0 4,330,125 1

Seq Scan on m_inoutline return_inoutline (cost=0.00..237,190.02 rows=4,308,750 width=137) (actual time=0.014..2,377.584 rows=4,330,125 loops=1)

  • Filter: (COALESCE(em_dp_returned_pen_to_register, 'N'::bpchar) <> 'Y'::bpchar)
  • Rows Removed by Filter: 244
16. 0.003 0.226 ↑ 1.0 2 1

Hash (cost=1.14..1.14 rows=2 width=180) (actual time=0.226..0.226 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.223 0.223 ↑ 1.0 2 1

Seq Scan on m_locator (cost=0.00..1.14 rows=2 width=180) (actual time=0.221..0.223 rows=2 loops=1)

  • Filter: ((m_locator_id)::text = ANY ('{19494A983B87407EA77E75301B1E14BB,12873F673ED548D8A7BBE8789245F98C}'::text[]))
  • Rows Removed by Filter: 9
18. 124.545 5,078.001 ↑ 2.0 104,991 1

Hash (cost=144,145.31..144,145.31 rows=209,562 width=199) (actual time=5,078.001..5,078.001 rows=104,991 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 1,769kB
19. 362.579 4,953.456 ↑ 2.0 104,991 1

Hash Join (cost=5.25..144,145.31 rows=209,562 width=199) (actual time=4.005..4,953.456 rows=104,991 loops=1)

  • Hash Cond: ((return_inout.c_doctype_id)::text = (return_doctype.c_doctype_id)::text)
20. 4,590.834 4,590.834 ↓ 1.0 1,542,781 1

Seq Scan on m_inout return_inout (cost=0.00..139,911.72 rows=1,536,786 width=232) (actual time=0.213..4,590.834 rows=1,542,781 loops=1)

  • Filter: ((issotrx = 'Y'::bpchar) AND ((docstatus)::text = 'CO'::text))
  • Rows Removed by Filter: 114,442
21. 0.003 0.043 ↑ 1.0 12 1

Hash (cost=5.10..5.10 rows=12 width=32) (actual time=0.043..0.043 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.040 0.040 ↑ 1.0 12 1

Seq Scan on c_doctype return_doctype (cost=0.00..5.10 rows=12 width=32) (actual time=0.009..0.040 rows=12 loops=1)

  • Filter: (isreturn = 'Y'::bpchar)
  • Rows Removed by Filter: 76
23. 585.700 2,642.417 ↑ 1.0 1,598,490 1

Hash (cost=149,564.31..149,564.31 rows=1,598,490 width=41) (actual time=2,642.417..2,642.417 rows=1,598,490 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,300kB
24. 1,074.653 2,056.717 ↑ 1.0 1,598,490 1

Hash Left Join (cost=62.30..149,564.31 rows=1,598,490 width=41) (actual time=1.697..2,056.717 rows=1,598,490 loops=1)

  • Hash Cond: ((m_product.m_brand_id)::text = (m_brand.m_brand_id)::text)
25. 980.383 980.383 ↑ 1.0 1,598,490 1

Seq Scan on m_product (cost=0.00..145,282.90 rows=1,598,490 width=66) (actual time=0.010..980.383 rows=1,598,490 loops=1)

26. 0.143 1.681 ↑ 1.0 769 1

Hash (cost=52.69..52.69 rows=769 width=41) (actual time=1.680..1.681 rows=769 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
27. 1.538 1.538 ↑ 1.0 769 1

Seq Scan on m_brand (cost=0.00..52.69 rows=769 width=41) (actual time=0.299..1.538 rows=769 loops=1)

28. 174.970 367.002 ↑ 1.0 979,545 1

Hash (cost=57,987.63..57,987.63 rows=979,563 width=33) (actual time=367.002..367.002 rows=979,545 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,460kB
29. 192.032 192.032 ↑ 1.0 979,545 1

Seq Scan on c_bpartner customer (cost=0.00..57,987.63 rows=979,563 width=33) (actual time=0.007..192.032 rows=979,545 loops=1)

30. 619.186 1,822.893 ↑ 1.0 2,104,362 1

Hash (cost=250,488.26..250,488.26 rows=2,105,126 width=43) (actual time=1,822.893..1,822.893 rows=2,104,362 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,935kB
31. 1,203.707 1,203.707 ↑ 1.0 2,104,362 1

Seq Scan on c_order return_order (cost=0.00..250,488.26 rows=2,105,126 width=43) (actual time=0.008..1,203.707 rows=2,104,362 loops=1)

32. 161.727 349.801 ↑ 1.0 979,545 1

Hash (cost=57,987.63..57,987.63 rows=979,563 width=33) (actual time=349.801..349.801 rows=979,545 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,453kB
33. 188.074 188.074 ↑ 1.0 979,545 1

Seq Scan on c_bpartner supplier (cost=0.00..57,987.63 rows=979,563 width=33) (actual time=0.242..188.074 rows=979,545 loops=1)

34. 0.004 21,484.189 ↑ 1.0 1 1

Hash (cost=2,360,588.83..2,360,588.83 rows=1 width=80) (actual time=21,484.189..21,484.189 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.003 21,484.185 ↑ 1.0 1 1

Subquery Scan on sales_order (cost=2,360,588.81..2,360,588.83 rows=1 width=80) (actual time=21,484.178..21,484.185 rows=1 loops=1)

36. 0.007 21,484.182 ↑ 1.0 1 1

Limit (cost=2,360,588.81..2,360,588.82 rows=1 width=80) (actual time=21,484.177..21,484.182 rows=1 loops=1)

37. 0.001 21,484.175 ↑ 743,212.0 1 1

Unique (cost=2,360,588.81..2,369,878.96 rows=743,212 width=80) (actual time=21,484.175..21,484.175 rows=1 loops=1)

38. 448.104 21,484.174 ↑ 743,212.0 1 1

Sort (cost=2,360,588.81..2,362,446.84 rows=743,212 width=80) (actual time=21,484.174..21,484.174 rows=1 loops=1)

  • Sort Key: o.documentno, o.c_order_id, ol.qtyordered, o2.c_order_id
  • Sort Method: external merge Disk: 17,192kB
39. 537.608 21,036.070 ↑ 3.9 192,755 1

Hash Join (cost=1,739,941.60..2,222,060.95 rows=743,212 width=80) (actual time=16,241.869..21,036.070 rows=192,755 loops=1)

  • Hash Cond: ((ol.c_order_id)::text = (o.c_order_id)::text)
40. 3,905.987 19,263.291 ↑ 3.9 192,755 1

Hash Join (cost=1,444,636.26..1,888,881.68 rows=743,212 width=70) (actual time=5,145.258..19,263.291 rows=192,755 loops=1)

  • Hash Cond: ((il.c_orderline_id)::text = (ol.c_orderline_id)::text)
41. 1,718.533 12,249.602 ↑ 3.9 192,755 1

Hash Join (cost=864,580.55..1,223,298.04 rows=743,212 width=66) (actual time=1,945.579..12,249.602 rows=192,755 loops=1)

  • Hash Cond: ((il.m_inoutline_id)::text = (ol2.m_inoutline_id)::text)
42. 8,586.986 8,586.986 ↑ 1.0 4,330,369 1

Seq Scan on m_inoutline il (cost=0.00..226,364.02 rows=4,330,402 width=66) (actual time=0.771..8,586.986 rows=4,330,369 loops=1)

43. 39.873 1,944.083 ↑ 3.9 192,755 1

Hash (cost=846,580.40..846,580.40 rows=743,212 width=66) (actual time=1,944.083..1,944.083 rows=192,755 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 1,078kB
44. 142.101 1,904.210 ↑ 3.9 192,755 1

Hash Join (cost=281,187.53..846,580.40 rows=743,212 width=66) (actual time=1,030.704..1,904.210 rows=192,755 loops=1)

  • Hash Cond: ((ol2.c_order_id)::text = (o2.c_order_id)::text)
45. 741.981 786.902 ↑ 14.9 379,154 1

Bitmap Heap Scan on c_orderline ol2 (cost=14,209.08..416,519.70 rows=5,645,365 width=66) (actual time=55.282..786.902 rows=379,154 loops=1)

  • Recheck Cond: (m_inoutline_id IS NOT NULL)
  • Rows Removed by Index Recheck: 697,920
  • Heap Blocks: exact=53,509 lossy=67,267
46. 44.921 44.921 ↓ 1.0 379,837 1

Bitmap Index Scan on c_orderline_inoutline (cost=0.00..12,797.74 rows=377,863 width=0) (actual time=44.921..44.921 rows=379,837 loops=1)

47. 26.347 975.207 ↑ 2.0 138,378 1

Hash (cost=261,348.20..261,348.20 rows=277,140 width=33) (actual time=975.207..975.207 rows=138,378 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 1,614kB
48. 231.561 948.860 ↑ 2.0 138,378 1

Hash Join (cost=5.25..261,348.20 rows=277,140 width=33) (actual time=0.050..948.860 rows=138,378 loops=1)

  • Hash Cond: ((o2.c_doctypetarget_id)::text = (dt.c_doctype_id)::text)
49. 717.265 717.265 ↓ 1.0 2,035,996 1

Seq Scan on c_order o2 (cost=0.00..255,751.08 rows=2,032,359 width=66) (actual time=0.009..717.265 rows=2,035,996 loops=1)

  • Filter: (issotrx = 'Y'::bpchar)
  • Rows Removed by Filter: 68,366
50. 0.003 0.034 ↑ 1.0 12 1

Hash (cost=5.10..5.10 rows=12 width=32) (actual time=0.034..0.034 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.031 0.031 ↑ 1.0 12 1

Seq Scan on c_doctype dt (cost=0.00..5.10 rows=12 width=32) (actual time=0.008..0.031 rows=12 loops=1)

  • Filter: (isreturn = 'Y'::bpchar)
  • Rows Removed by Filter: 76
52. 1,306.006 3,107.702 ↑ 1.0 5,644,690 1

Hash (cost=443,331.65..443,331.65 rows=5,645,365 width=70) (actual time=3,107.702..3,107.702 rows=5,644,690 loops=1)

  • Buckets: 65,536 Batches: 256 Memory Usage: 2,725kB
53. 1,801.696 1,801.696 ↑ 1.0 5,644,690 1

Seq Scan on c_orderline ol (cost=0.00..443,331.65 rows=5,645,365 width=70) (actual time=0.009..1,801.696 rows=5,644,690 loops=1)

54. 459.178 1,235.171 ↑ 1.0 2,104,362 1

Hash (cost=250,488.26..250,488.26 rows=2,105,126 width=43) (actual time=1,235.171..1,235.171 rows=2,104,362 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,948kB
55. 775.993 775.993 ↑ 1.0 2,104,362 1

Seq Scan on c_order o (cost=0.00..250,488.26 rows=2,105,126 width=43) (actual time=0.008..775.993 rows=2,104,362 loops=1)

Planning time : 32.491 ms
Execution time : 44,351.082 ms