explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 76w2

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 43,105.657 ↓ 0.0 0 1

Sort (cost=1,491,719.98..1,491,720.00 rows=9 width=680) (actual time=43,105.657..43,105.657 rows=0 loops=1)

  • Sort Key: (((o.documentno)::text || ' 6'::text))
  • Sort Method: quicksort Memory: 25kB
2.          

CTE ipayment

3. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=41,927.18..620,155.80 rows=3,083,318 width=59) (never executed)

  • Hash Cond: (cb.c_currency_id = cr.c_currency_id_to)
  • Join Filter: ((cr.validfrom <= c.dateacct) AND (cr.validto >= c.dateacct))
4. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=41,311.87..82,973.26 rows=30,010 width=40) (never executed)

  • Hash Cond: (c.c_cashbook_id = cb.c_cashbook_id)
5. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=41,281.52..82,530.27 rows=30,010 width=41) (never executed)

  • Hash Cond: (cl.c_cash_id = c.c_cash_id)
6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=31,168.62..70,654.64 rows=30,034 width=26) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=31,168.19..41,765.31 rows=30,034 width=19) (never executed)

  • Hash Cond: (al.c_allocationhdr_id = ah.c_allocationhdr_id)
8. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=17,937.70..26,848.03 rows=30,788 width=25) (never executed)

  • Hash Cond: (al.c_invoice_id = i2.c_invoice_id)
9. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_allocationline al (cost=0.00..7,699.89 rows=240,689 width=25) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Hash (cost=17,531.01..17,531.01 rows=32,535 width=6) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on c_invoice i2 (cost=2,251.08..17,531.01 rows=32,535 width=6) (never executed)

  • Recheck Cond: (c_doctypetarget_id = ANY ('{1000005,1000156,1000158}'::numeric[]))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND ((dateinvoiced)::date <= ('now'::cstring)::date))
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on art_c_invoice_issotrx_doc_status (cost=0.00..2,242.95 rows=110,626 width=0) (never executed)

  • Index Cond: (c_doctypetarget_id = ANY ('{1000005,1000156,1000158}'::numeric[]))
13. 0.000 0.000 ↓ 0.0 0

Hash (cost=9,666.03..9,666.03 rows=217,237 width=6) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_allocationhdr ah (cost=0.00..9,666.03 rows=217,237 width=6) (never executed)

  • Filter: (docstatus <> ALL ('{VO,RE,--}'::bpchar[]))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using c_cashline_pkey on c_cashline cl (cost=0.42..0.95 rows=1 width=13) (never executed)

  • Index Cond: (c_cashline_id = al.c_cashline_id)
16. 0.000 0.000 ↓ 0.0 0

Hash (cost=7,461.14..7,461.14 rows=137,101 width=28) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_cash c (cost=0.00..7,461.14 rows=137,101 width=28) (never executed)

  • Filter: (docstatus = ANY ('{CO,CL}'::bpchar[]))
18. 0.000 0.000 ↓ 0.0 0

Hash (cost=23.49..23.49 rows=549 width=11) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_cashbook cb (cost=0.00..23.49 rows=549 width=11) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Hash (cost=445.75..445.75 rows=13,565 width=28) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_conversion_rate cr (cost=0.00..445.75 rows=13,565 width=28) (never executed)

  • Filter: (c_currency_id = '341'::numeric)
22.          

CTE w_invoice

23. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=781,088.47..858,971.42 rows=40,000 width=112) (never executed)

  • Group Key: ip.c_invoice_id, (to_char(ip.statementdate, 'YYYY.MM.DD'::text))
24. 0.000 0.000 ↓ 0.0 0

Sort (cost=781,088.47..788,796.77 rows=3,083,318 width=120) (never executed)

  • Sort Key: ip.c_invoice_id, (to_char(ip.statementdate, 'YYYY.MM.DD'::text))
25. 0.000 0.000 ↓ 0.0 0

CTE Scan on ipayment ip (cost=0.00..69,374.65 rows=3,083,318 width=120) (never executed)

26.          

CTE buy

27. 60.576 252.317 ↓ 103.3 25,617 1

Nested Loop Left Join (cost=0.42..9,479.51 rows=248 width=33) (actual time=0.076..252.317 rows=25,617 loops=1)

28. 99.187 99.187 ↓ 183.6 13,222 1

Seq Scan on c_order buy_1 (cost=0.00..7,828.87 rows=72 width=13) (actual time=0.047..99.187 rows=13,222 loops=1)

  • Filter: ((c_doctype_id = ANY ('{1000160,1000016}'::numeric[])) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (COALESCE(issotrx, 'N'::bpchar) = 'N'::bpchar))
  • Rows Removed by Filter: 70060
29. 92.554 92.554 ↑ 4.5 2 13,222

Index Scan using c_orderline_order on c_orderline buyl (cost=0.42..22.84 rows=9 width=26) (actual time=0.006..0.007 rows=2 loops=13,222)

  • Index Cond: (c_order_id = buy_1.c_order_id)
30. 0.001 43,105.636 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,983.40..3,113.11 rows=9 width=680) (actual time=43,105.636..43,105.636 rows=0 loops=1)

  • Join Filter: ((corb.validfrom <= o.dateordered) AND (corb.validto >= o.dateordered))
31. 0.001 43,105.635 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,983.11..2,937.14 rows=1 width=294) (actual time=43,105.635..43,105.635 rows=0 loops=1)

32. 0.000 43,105.634 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,982.97..2,936.94 rows=1 width=290) (actual time=43,105.634..43,105.634 rows=0 loops=1)

33. 0.022 43,105.634 ↓ 0.0 0 1

Hash Right Join (cost=1,981.70..2,931.71 rows=1 width=264) (actual time=43,105.634..43,105.634 rows=0 loops=1)

  • Hash Cond: (wi.c_invoice_id = i.c_invoice_id)
34. 0.000 0.000 ↓ 0.0 0

CTE Scan on w_invoice wi (cost=0.00..800.00 rows=40,000 width=80) (never executed)

35. 0.001 43,105.612 ↓ 0.0 0 1

Hash (cost=1,981.69..1,981.69 rows=1 width=206) (actual time=43,105.612..43,105.612 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
36. 0.001 43,105.611 ↓ 0.0 0 1

Nested Loop Left Join (cost=94.43..1,981.69 rows=1 width=206) (actual time=43,105.611..43,105.611 rows=0 loops=1)

37. 0.000 43,105.610 ↓ 0.0 0 1

Nested Loop Left Join (cost=94.14..1,977.90 rows=1 width=168) (actual time=43,105.610..43,105.610 rows=0 loops=1)

38. 0.001 43,105.610 ↓ 0.0 0 1

Nested Loop (cost=93.85..1,977.58 rows=1 width=116) (actual time=43,105.610..43,105.610 rows=0 loops=1)

39. 0.000 43,105.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=93.57..1,977.26 rows=1 width=126) (actual time=43,105.609..43,105.609 rows=0 loops=1)

40. 0.000 43,105.609 ↓ 0.0 0 1

Nested Loop Left Join (cost=93.29..1,971.29 rows=1 width=105) (actual time=43,105.609..43,105.609 rows=0 loops=1)

  • Join Filter: ((rl2.value)::bpchar = i.docstatus)
41. 0.000 43,105.609 ↓ 0.0 0 1

Nested Loop (cost=88.92..1,947.32 rows=1 width=90) (actual time=43,105.609..43,105.609 rows=0 loops=1)

42. 0.002 43,105.610 ↓ 0.0 0 1

Nested Loop (cost=88.50..1,946.78 rows=1 width=54) (actual time=43,105.609..43,105.610 rows=0 loops=1)

  • Join Filter: ((sr.aek_shiprequest_id = srl.aek_shiprequest_id) AND (rl.aek_shiprequestline_id = srl.aek_shiprequestline_id) AND ((srl.c_orderline_id = buy.c_orderline_id) OR (srl.c_orderline_id = buy.link_orderline_id) OR (srl.c_orderline_id = ol.c_orderline_id)))
43. 0.001 43,105.608 ↓ 0.0 0 1

Nested Loop (cost=88.21..1,946.44 rows=1 width=117) (actual time=43,105.608..43,105.608 rows=0 loops=1)

  • Join Filter: CASE WHEN (buy.link_order_id > '0'::numeric) THEN (ol.c_orderline_id = buy.link_orderline_id) ELSE true END
44. 19,792.904 43,105.607 ↓ 0.0 0 1

Nested Loop (cost=87.78..1,923.55 rows=1 width=133) (actual time=43,105.607..43,105.607 rows=0 loops=1)

  • Join Filter: ((sr.c_order_id = buy.c_order_id) OR (sr.c_order_id = o.c_order_id))
45. 1,988.658 3,741.315 ↓ 9,785,694.0 9,785,694 1

Nested Loop (cost=81.96..1,909.76 rows=1 width=149) (actual time=34.242..3,741.315 rows=9,785,694 loops=1)

46. 0.490 42.443 ↓ 382.0 382 1

Nested Loop (cost=81.96..1,902.32 rows=1 width=69) (actual time=34.158..42.443 rows=382 loops=1)

47. 22.186 38.889 ↓ 383.0 383 1

Merge Join (cost=81.67..1,901.88 rows=1 width=56) (actual time=25.263..38.889 rows=383 loops=1)

  • Merge Cond: (rl.aek_route_id = il.aek_route_id)
  • Join Filter: (rl.aek_shiprequestline_id = il.aek_shiprequestline_id)
  • Rows Removed by Join Filter: 5359
48. 8.811 8.811 ↑ 1.0 18,938 1

Index Scan using aek_routeline_route_id on aek_routeline rl (cost=0.29..1,025.14 rows=18,938 width=19) (actual time=0.018..8.811 rows=18,938 loops=1)

49. 7.892 7.892 ↑ 63.7 9,869 1

Index Scan using art_c_invoiceline_route_id on c_invoiceline il (cost=0.42..76,432.74 rows=628,560 width=37) (actual time=0.012..7.892 rows=9,869 loops=1)

50. 3.064 3.064 ↑ 1.0 1 383

Index Scan using aek_shiprequest_pkey on aek_shiprequest sr (cost=0.29..0.43 rows=1 width=13) (actual time=0.007..0.008 rows=1 loops=383)

  • Index Cond: (aek_shiprequest_id = rl.aek_shiprequest_id)
  • Filter: (shiprequesttype = 'C'::bpchar)
  • Rows Removed by Filter: 0
51. 1,710.214 1,710.214 ↓ 103.3 25,617 382

CTE Scan on buy (cost=0.00..4.96 rows=248 width=80) (actual time=0.000..4.477 rows=25,617 loops=382)

52. 0.000 19,571.388 ↓ 0.0 0 9,785,694

Bitmap Heap Scan on c_order o (cost=5.83..13.78 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=9,785,694)

  • Recheck Cond: ((c_order_id = buy.c_order2_id) OR (c_order_id = buy.link_order_id))
  • Filter: ((dateordered >= '2010-01-01'::date) AND (issotrx = 'Y'::bpchar) AND (c_doctypetarget_id = '1000154'::numeric) AND (c_division_id = '1000025'::numeric) AND (docstatus <> ALL ('{VO,RE,--}'::bpchar[])))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4266940
53. 9,785.694 19,571.388 ↓ 0.0 0 9,785,694

BitmapOr (cost=5.83..5.83 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=9,785,694)

54. 0.000 0.000 ↓ 0.0 0 9,785,694

Bitmap Index Scan on c_order_pkey (cost=0.00..2.91 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=9,785,694)

  • Index Cond: (c_order_id = buy.c_order2_id)
55. 9,785.694 9,785.694 ↓ 0.0 0 9,785,694

Bitmap Index Scan on c_order_pkey (cost=0.00..2.91 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9,785,694)

  • Index Cond: (c_order_id = buy.link_order_id)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using c_orderline_order on c_orderline ol (cost=0.42..22.76 rows=9 width=12) (never executed)

  • Index Cond: (c_order_id = o.c_order_id)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using aek_shiprequestline_pkey on aek_shiprequestline srl (cost=0.29..0.32 rows=1 width=18) (never executed)

  • Index Cond: (aek_shiprequestline_id = il.aek_shiprequestline_id)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoice_key on c_invoice i (cost=0.42..0.53 rows=1 width=42) (never executed)

  • Index Cond: (c_invoice_id = il.c_invoice_id)
  • Filter: ((c_doctypetarget_id = ANY ('{1000005,1000158}'::numeric[])) AND (issotrx = 'N'::bpchar) AND (iscargo = 'Y'::bpchar) AND (docstatus <> ALL ('{VO,RE,--}'::bpchar[])))
59. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ad_ref_list rl2 (cost=4.37..23.82 rows=12 width=20) (never executed)

  • Recheck Cond: (ad_reference_id = '131'::numeric)
60. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.37 rows=12 width=0) (never executed)

  • Index Cond: (ad_reference_id = '131'::numeric)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using ad_ref_list_trl_pkey on ad_ref_list_trl rlt2 (cost=0.28..5.96 rows=1 width=31) (never executed)

  • Index Cond: (ad_ref_list_id = rl2.ad_ref_list_id)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using aek_route_pkey on aek_route r (cost=0.29..0.31 rows=1 width=11) (never executed)

  • Index Cond: (aek_route_id = il.aek_route_id)
  • Filter: ((docstatus)::text <> ALL ('{VO,RE,--}'::text[]))
63. 0.000 0.000 ↓ 0.0 0

Index Scan using m_product_pkey on m_product pol (cost=0.29..0.31 rows=1 width=65) (never executed)

  • Index Cond: (m_product_id = il.m_product_id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_pkey on c_bpartner bc (cost=0.29..3.78 rows=1 width=50) (never executed)

  • Index Cond: (c_bpartner_id = i.c_bpartner_id)
65. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.26..5.22 rows=1 width=38) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.99..4.81 rows=1 width=47) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.70..3.98 rows=1 width=32) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.43..2.75 rows=1 width=28) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl (cost=0.29..2.58 rows=1 width=19) (never executed)

  • Index Cond: (c_bpartner_location_id = i.c_bpartner_location_id)
  • Filter: (ad_client_id = '1000000'::numeric)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using c_salesregion_pkey on c_salesregion sar (cost=0.14..0.16 rows=1 width=32) (never executed)

  • Index Cond: (c_salesregion_id = bpl.c_salesregion_id)
71. 0.000 0.000 ↓ 0.0 0

Index Scan using c_region_pkey on c_region sreg (cost=0.27..1.22 rows=1 width=20) (never executed)

  • Index Cond: (c_region_id = sar.c_region_id)
72. 0.000 0.000 ↓ 0.0 0

Index Scan using c_location_pkey on c_location loc (cost=0.29..0.82 rows=1 width=32) (never executed)

  • Index Cond: (c_location_id = bpl.c_location_id)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using c_region_pkey on c_region reg (cost=0.27..0.29 rows=1 width=20) (never executed)

  • Index Cond: (c_region_id = loc.c_region_id)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using c_currency_pkey on c_currency cur (cost=0.14..0.19 rows=1 width=9) (never executed)

  • Index Cond: (c_currency_id = i.c_currency_id)
75. 0.000 0.000 ↓ 0.0 0

Index Scan using c_conversionrate_once on c_conversion_rate corb (cost=0.29..153.20 rows=1,507 width=28) (never executed)

  • Index Cond: ((c_currency_id = '341'::numeric) AND (c_currency_id_to = i.c_currency_id))