explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RN7b

Settings
# exclusive inclusive rows x rows loops node
1. 1,828.450 25,513.173 ↑ 91.1 1,088 1

Hash Right Join (cost=3,381,521.41..4,006,743.59 rows=99,063 width=617) (actual time=23,442.342..25,513.173 rows=1,088 loops=1)

  • Hash Cond: (((sales_orderline.c_order_id)::text = (sales_order.c_order_id)::text) AND ((sales_orderline.m_product_id)::text = (m_product.m_product_id)::text))
2. 1,844.362 1,844.362 ↑ 1.0 5,644,690 1

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

3. 1.220 21,840.361 ↑ 91.1 1,088 1

Hash (cost=3,374,037.46..3,374,037.46 rows=99,063 width=466) (actual time=21,840.361..21,840.361 rows=1,088 loops=1)

  • Buckets: 8192 Batches: 16 Memory Usage: 89kB
4. 147.142 21,839.141 ↑ 91.1 1,088 1

Hash Left Join (cost=2,594,995.36..3,374,037.46 rows=99,063 width=466) (actual time=16,840.681..21,839.141 rows=1,088 loops=1)

  • Hash Cond: ((sales_inout.c_order_id)::text = (sales_order.c_order_id)::text)
5. 137.038 20,290.660 ↑ 91.1 1,088 1

Hash Left Join (cost=2,299,690.03..3,048,359.09 rows=99,063 width=456) (actual time=15,439.135..20,290.660 rows=1,088 loops=1)

  • Hash Cond: ((sales_inoutline.m_inout_id)::text = (sales_inout.m_inout_id)::text)
6. 364.125 19,312.228 ↑ 91.1 1,088 1

Hash Left Join (cost=2,127,927.95..2,845,305.97 rows=99,063 width=456) (actual time=14,597.522..19,312.228 rows=1,088 loops=1)

  • Hash Cond: (sub_sales_inoutline.m_inoutline_id = (sales_inoutline.m_inoutline_id)::text)
7. 10.888 16,859.515 ↑ 91.1 1,088 1

Hash Left Join (cost=1,796,686.90..2,451,447.88 rows=99,063 width=455) (actual time=12,508.903..16,859.515 rows=1,088 loops=1)

  • Hash Cond: ((return_order.c_order_id)::text = (sub_sales_inoutline.c_order_id)::text)
8. 47.170 15,084.161 ↑ 91.1 1,088 1

Hash Left Join (cost=1,322,440.11..1,964,593.04 rows=99,063 width=423) (actual time=10,744.237..15,084.161 rows=1,088 loops=1)

  • Hash Cond: ((m_product_po.c_bpartner_id)::text = (supplier.c_bpartner_id)::text)
9. 5,808.171 14,662.571 ↑ 91.1 1,088 1

Hash Right Join (cost=1,244,554.94..1,867,958.84 rows=99,063 width=423) (actual time=10,369.778..14,662.571 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))
10. 2,128.867 2,128.867 ↑ 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.020..2,128.867 rows=5,644,690 loops=1)

11. 1.016 6,725.533 ↑ 91.1 1,088 1

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

  • Buckets: 16384 Batches: 16 Memory Usage: 157kB
12. 148.082 6,724.517 ↑ 91.1 1,088 1

Hash Left Join (cost=1,046,713.61..1,237,650.99 rows=99,063 width=419) (actual time=6,399.314..6,724.517 rows=1,088 loops=1)

  • Hash Cond: ((return_inout.c_order_id)::text = (return_order.c_order_id)::text)
13. 71.302 5,317.809 ↑ 91.1 1,088 1

Hash Left Join (cost=751,408.28..912,940.62 rows=99,063 width=409) (actual time=4,428.947..5,317.809 rows=1,088 loops=1)

  • Hash Cond: ((return_inout.c_bpartner_id)::text = (customer.c_bpartner_id)::text)
14. 412.375 4,893.272 ↑ 91.1 1,088 1

Hash Right Join (cost=673,523.11..816,500.41 rows=99,063 width=409) (actual time=4,065.736..4,893.272 rows=1,088 loops=1)

  • Hash Cond: ((m_product_po.m_product_id)::text = (m_product.m_product_id)::text)
15. 421.980 421.980 ↑ 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.038..421.980 rows=1,508,180 loops=1)

  • Filter: (iscurrentvendor = 'Y'::bpchar)
  • Rows Removed by Filter: 4695
16. 0.893 4,058.917 ↑ 91.1 1,088 1

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

  • Buckets: 16384 Batches: 16 Memory Usage: 160kB
17. 115.417 4,058.024 ↑ 91.1 1,088 1

Hash Left Join (cost=336,092.43..667,446.82 rows=99,063 width=376) (actual time=2,584.845..4,058.024 rows=1,088 loops=1)

  • Hash Cond: ((return_inoutline.m_product_id)::text = (m_product.m_product_id)::text)
18. 28.376 2,462.426 ↑ 91.1 1,088 1

Hash Join (cost=152,497.00..458,833.44 rows=99,063 width=368) (actual time=773.500..2,462.426 rows=1,088 loops=1)

  • Hash Cond: ((return_inoutline.m_inout_id)::text = (return_inout.m_inout_id)::text)
19. 299.279 1,672.998 ↑ 22.4 35,047 1

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

  • Hash Cond: ((return_inoutline.m_locator_id)::text = (m_locator.m_locator_id)::text)
20. 1,373.666 1,373.666 ↓ 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.335..1,373.666 rows=4,330,125 loops=1)

  • Filter: (COALESCE(em_dp_returned_pen_to_register, 'N'::bpchar) <> 'Y'::bpchar)
  • Rows Removed by Filter: 244
21. 0.015 0.053 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.038 0.038 ↑ 1.0 2 1

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

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

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

  • Buckets: 32768 Batches: 16 Memory Usage: 1769kB
24. 182.245 710.435 ↑ 2.0 104,991 1

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

  • Hash Cond: ((return_inout.c_doctype_id)::text = (return_doctype.c_doctype_id)::text)
25. 527.994 527.994 ↓ 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.036..527.994 rows=1,542,781 loops=1)

  • Filter: ((issotrx = 'Y'::bpchar) AND ((docstatus)::text = 'CO'::text))
  • Rows Removed by Filter: 114442
26. 0.022 0.196 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.174 0.174 ↑ 1.0 12 1

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

  • Filter: (isreturn = 'Y'::bpchar)
  • Rows Removed by Filter: 76
28. 346.376 1,480.181 ↑ 1.0 1,598,490 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2300kB
29. 735.314 1,133.805 ↑ 1.0 1,598,490 1

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

  • Hash Cond: ((m_product.m_brand_id)::text = (m_brand.m_brand_id)::text)
30. 398.085 398.085 ↑ 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..398.085 rows=1,598,490 loops=1)

31. 0.133 0.406 ↑ 1.0 769 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
32. 0.273 0.273 ↑ 1.0 769 1

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

33. 167.154 353.235 ↑ 1.0 979,545 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 2460kB
34. 186.081 186.081 ↑ 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.014..186.081 rows=979,545 loops=1)

35. 466.567 1,258.626 ↑ 1.0 2,104,362 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2935kB
36. 792.059 792.059 ↑ 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.014..792.059 rows=2,104,362 loops=1)

37. 173.624 374.420 ↑ 1.0 979,545 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 2453kB
38. 200.796 200.796 ↑ 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.396..200.796 rows=979,545 loops=1)

39. 33.410 1,764.466 ↓ 1.1 146,675 1

Hash (cost=471,122.41..471,122.41 rows=128,991 width=65) (actual time=1,764.466..1,764.466 rows=146,675 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 4028kB
40. 10.747 1,731.056 ↓ 1.1 146,675 1

Subquery Scan on sub_sales_inoutline (cost=465,708.62..471,122.41 rows=128,991 width=65) (actual time=1,483.927..1,731.056 rows=146,675 loops=1)

41. 81.654 1,720.309 ↓ 1.1 146,675 1

GroupAggregate (cost=465,708.62..469,832.50 rows=128,991 width=65) (actual time=1,483.926..1,720.309 rows=146,675 loops=1)

  • Group Key: c_orderline.c_order_id
42. 690.683 1,638.655 ↓ 1.0 379,154 1

Sort (cost=465,708.62..466,653.27 rows=377,863 width=66) (actual time=1,483.916..1,638.655 rows=379,154 loops=1)

  • Sort Key: c_orderline.c_order_id
  • Sort Method: external merge Disk: 28240kB
43. 900.722 947.972 ↓ 1.0 379,154 1

Bitmap Heap Scan on c_orderline (cost=12,892.20..415,202.83 rows=377,863 width=66) (actual time=60.348..947.972 rows=379,154 loops=1)

  • Recheck Cond: (m_inoutline_id IS NOT NULL)
  • Rows Removed by Index Recheck: 697920
  • Heap Blocks: exact=53509 lossy=67267
44. 47.250 47.250 ↓ 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=47.250..47.250 rows=379,837 loops=1)

45. 1,047.104 2,088.588 ↑ 1.0 4,330,369 1

Hash (cost=226,364.02..226,364.02 rows=4,330,402 width=66) (actual time=2,088.588..2,088.588 rows=4,330,369 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 3772kB
46. 1,041.484 1,041.484 ↑ 1.0 4,330,369 1

Seq Scan on m_inoutline sales_inoutline (cost=0.00..226,364.02 rows=4,330,402 width=66) (actual time=0.014..1,041.484 rows=4,330,369 loops=1)

47. 341.702 841.394 ↑ 1.0 1,657,223 1

Hash (cost=131,625.48..131,625.48 rows=1,657,248 width=66) (actual time=841.394..841.394 rows=1,657,223 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2976kB
48. 499.692 499.692 ↑ 1.0 1,657,223 1

Seq Scan on m_inout sales_inout (cost=0.00..131,625.48 rows=1,657,248 width=66) (actual time=0.010..499.692 rows=1,657,223 loops=1)

49. 500.943 1,401.339 ↑ 1.0 2,104,362 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2935kB
50. 900.396 900.396 ↑ 1.0 2,104,362 1

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

Planning time : 147.878 ms
Execution time : 25,526.734 ms