Attached Files | query [^] (11,749 bytes) 2017-09-25 18:14 [Show Content] [Hide Content]SELECT nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10,
Sum(amount) AS AMOUNT,
Sum(qty) AS QTY,
Sum(weight) AS WEIGHT,
Sum(cost) AS COST,
Sum(amountref) AS AMOUNTREF,
Sum(qtyref) AS QTYREF,
Sum(weightref) AS WEIGHTREF,
Sum(convamount) AS CONVAMOUNT,
CASE
WHEN Sum(costcalculated) = 0 THEN Sum(convcost)
WHEN Sum(costedamt) = 0 THEN 0
WHEN Sum(CASE
WHEN groupcount = costcalculated THEN 0
ELSE convamount
END) = 0 THEN 0
ELSE Sum(convcost) * Sum(convamount) / Sum(
CASE
WHEN groupcount = costcalculated THEN 0
ELSE convamount
END)
END AS CONVCOST,
Sum(convamountref) AS CONVAMOUNTREF,
Sum(convcostref) AS CONVCOSTREF,
C_currency_symbol('116', 0, 'Y') AS CONVSYM,
C_currency_isosym('116') AS CONVISOSYM,
Sum(ZZ.costcalculated) AS COSTCALCULATED
FROM (SELECT nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10,
Sum(linenetamt) AS AMOUNT,
Sum(qtyinvoiced) AS QTY,
Sum(weight) AS WEIGHT,
Sum(cost) AS COST,
Sum(linenetref) AS AMOUNTREF,
Sum(qtyinvoicedref) AS QTYREF,
Sum(weight_ref) AS WEIGHTREF,
C_currency_convert(Sum(linenetamt), trcurrencyid, '116', To_date(
trdate),
NULL,
trclientid, trorgid) AS CONVAMOUNT,
CASE
WHEN Sum(costcalculated) = 0 THEN Sum(cost)
WHEN Sum(costedamt) = 0 THEN 0
ELSE Sum(cost) * Sum(linenetamt) / Sum(costedamt)
END AS CONVCOST,
0 AS CONVAMOUNTREF,
0 AS CONVCOSTREF,
trcurrencyid,
trdate,
trclientid,
trorgid,
Sum(AA.costcalculated) AS COSTCALCULATED,
C_currency_convert(Sum(costedamt), trcurrencyid, '116', To_date(
trdate),
NULL,
trclientid, trorgid) AS costedamt,
Count(*) AS GROUPCOUNT
FROM (SELECT To_char(Ad_column_identifier(To_char('M_Product'),
To_char(
m_product.m_product_id),
To_char('en_US'))
|| CASE
WHEN uomsymbol IS NULL THEN ''
ELSE To_char(' ('
|| uomsymbol
|| ')')
END) AS
NIVEL1,
To_char('') AS
NIVEL2,
To_char('') AS
NIVEL3,
To_char('') AS
NIVEL4,
To_char('') AS
NIVEL5,
To_char('') AS
NIVEL6,
To_char('') AS
NIVEL7,
To_char('') AS
NIVEL8,
To_char('') AS
NIVEL9,
To_char('') AS
NIVEL10,
CASE
WHEN c_doctype.docbasetype = 'ARC' THEN
c_invoiceline.linenetamt *- 1
ELSE c_invoiceline.linenetamt
END AS
LINENETAMT,
CASE
WHEN c_doctype.docbasetype = 'ARC' THEN
c_invoiceline.qtyinvoiced *- 1
ELSE c_invoiceline.qtyinvoiced
END AS
QTYINVOICED,
c_invoiceline.qtyinvoiced * m_product.weight AS
WEIGHT,
0 AS
LINENETREF,
0 AS
QTYINVOICEDREF,
0 AS
WEIGHT_REF,
c_uom.uomsymbol,
c_invoice.c_currency_id AS
TRCURRENCYID
,
To_date(COALESCE(c_invoice.dateinvoiced, Now())
) AS TRDATE,
c_invoiceline.ad_client_id AS
TRCLIENTID,
c_invoiceline.ad_org_id AS
TRORGID,
CASE
WHEN trxcost.cost IS NULL
AND m_product.isstocked = 'Y'
AND m_product.producttype = 'I' THEN 1
ELSE 0
END AS
COSTCALCULATED,
CASE
WHEN ( m_product.isstocked = 'Y'
AND m_product.producttype = 'I' ) THEN (
CASE
WHEN trx.movementqty = 0 THEN 0
ELSE COALESCE(
C_currency_convert_precision (Round(
trxcost.cost /
Abs(trx.movementqty) * (
CASE
WHEN
c_doctype.docbasetype = 'ARC'
THEN
c_invoiceline.qtyinvoiced *-
1
ELSE
c_invoiceline.qtyinvoiced
END ),
C_get_currency_precision(trxcost.c_currency_id, 'C')),
trxcost.c_currency_id, '116', trx.movementdate, NULL, trx.ad_client_id, trx.ad_org_id, 'C'), 0)
END )
ELSE ( COALESCE(M_get_no_trx_product_cost(m_product.m_product_id,
c_invoice.dateinvoiced, 'STA',
ad_org.ad_org_id, NULL,
c_invoice.c_currency_id), 0
) * ( CASE
WHEN c_doctype.docbasetype = 'ARC' THEN
c_invoiceline.qtyinvoiced *- 1
ELSE c_invoiceline.qtyinvoiced
END ) )
END AS COST,
CASE
WHEN trxcost.cost IS NULL
AND m_product.isstocked = 'Y'
AND m_product.producttype = 'I' THEN 0
ELSE
CASE
WHEN c_doctype.docbasetype = 'ARC' THEN c_invoiceline.linenetamt *- 1
ELSE c_invoiceline.linenetamt
END
END AS COSTEDAMT
FROM c_invoiceline
JOIN c_invoice
ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
JOIN c_bpartner
ON c_invoice.c_bpartner_id = c_bpartner.c_bpartner_id
JOIN c_bp_group
ON c_bpartner.c_bp_group_id = c_bp_group.c_bp_group_id
JOIN ad_org
ON c_invoice.ad_org_id = ad_org.ad_org_id
LEFT JOIN c_bpartner CB
ON c_bpartner.salesrep_id = CB.c_bpartner_id
LEFT JOIN ad_user
ON c_invoice.salesrep_id = ad_user.ad_user_id
LEFT JOIN c_project
ON c_invoice.c_project_id = c_project.c_project_id
LEFT JOIN c_doctype
ON c_invoice.c_doctype_id = c_doctype.c_doctype_id
LEFT JOIN m_product
ON c_invoiceline.m_product_id = m_product.m_product_id
LEFT JOIN c_uom
ON c_invoiceline.c_uom_id = c_uom.c_uom_id
LEFT JOIN m_product_category
ON m_product.m_product_category_id =
m_product_category.m_product_category_id
LEFT JOIN m_inoutline
ON c_invoiceline.m_inoutline_id = m_inoutline.m_inoutline_id
LEFT JOIN m_inout
ON m_inoutline.m_inout_id = m_inout.m_inout_id
LEFT JOIN m_transaction trx
ON trx.m_inoutline_id = m_inoutline.m_inoutline_id
LEFT JOIN (SELECT Sum(cost) AS cost,
m_transaction_id,
c_currency_id
FROM m_transaction_cost
GROUP BY m_transaction_id,
c_currency_id) trxcost
ON trx.m_transaction_id = trxcost.m_transaction_id
WHERE c_invoice.issotrx = 'Y'
AND c_invoice.processed = 'Y'
AND c_invoice.ad_org_id IN (
'C002170F73544C6E9AAEC8DD7FBB5750',
'0A733B3F5292414BA2310836A3EE9B73',
'664FFE8B17974ABCBE4215D77996BB16',
'AA6E6D54ACC94E3DA142B1CA42C332E6',
'E168567EAFF94B4793996B0AB415930B',
'24D0809B7ACB4BEEBFD742E6D3BF1004',
'61D784C8E7B24C61864CAAC67AC3554F',
'9F488865B7FF4341938EBA9D94204795',
'2A85B2F756EA492897FA0C5061D35C04',
'33DD7E752F9E4370AAE84B7B6B1EA5AD' )
AND c_invoice.ad_client_id IN ( '0', '101C8C9C4A8D4C84857E3F5FBD722757'
)
AND 1 = 1
AND c_invoice.dateinvoiced >= To_date('04-01-2017')
AND c_invoice.dateinvoiced < To_date('06-01-2017')
AND c_bp_group.c_bp_group_id = '21FE15587FE048D4B898272F4D44176A'
ORDER BY nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10) AA
GROUP BY nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10,
trcurrencyid,
trdate,
trclientid,
trorgid) ZZ
GROUP BY nivel1,
nivel2,
nivel3,
nivel4,
nivel5,
nivel6,
nivel7,
nivel8,
nivel9,
nivel10
ORDER BY nivel1
query plan [^] (6,933 bytes) 2017-09-25 18:14 [Show Content] [Hide Content]'Sort (cost=520093.06..520093.56 rows=200 width=592)'
' Sort Key: (to_char(((ad_column_identifier('M_Product'::character varying, to_char(m_product.m_product_id), 'en_US'::character varying))::text || (CASE WHEN (c_uom.uomsymbol IS NULL) THEN ''::character varying ELSE to_char(((' ('::text || (c_uom.uomsymbol)::text) || ')'::text)) END)::text)))'
' -> HashAggregate (cost=519980.92..520085.42 rows=200 width=592)'
' -> HashAggregate (cost=518910.88..519898.98 rows=964 width=574)'
' -> Sort (cost=518043.28..518067.38 rows=9640 width=296)'
' Sort Key: (to_char(((ad_column_identifier('M_Product'::character varying, to_char(m_product.m_product_id), 'en_US'::character varying))::text || (CASE WHEN (c_uom.uomsymbol IS NULL) THEN ''::character varying ELSE to_char(((' ('::text || (c_uom.uomsymbol)::text) || ')'::text)) END)::text)))'
' -> Merge Right Join (cost=453544.61..517405.36 rows=9640 width=296)'
' Merge Cond: ((m_transaction_cost.m_transaction_id)::text = (trx.m_transaction_id)::text)'
' -> GroupAggregate (cost=258011.68..284450.72 rows=1194097 width=43)'
' -> Sort (cost=258011.68..261636.20 rows=1449807 width=43)'
' Sort Key: m_transaction_cost.m_transaction_id, m_transaction_cost.c_currency_id'
' -> Seq Scan on m_transaction_cost (cost=0.00..65042.07 rows=1449807 width=43)'
' -> Sort (cost=195532.93..195557.03 rows=9640 width=293)'
' Sort Key: trx.m_transaction_id'
' -> Nested Loop Left Join (cost=353.47..194895.01 rows=9640 width=293)'
' -> Nested Loop Left Join (cost=353.04..188707.39 rows=9640 width=214)'
' -> Hash Left Join (cost=352.49..180908.33 rows=9640 width=214)'
' Hash Cond: ((c_invoiceline.c_uom_id)::text = (c_uom.c_uom_id)::text)'
' -> Nested Loop Left Join (cost=350.99..180774.28 rows=9640 width=219)'
' -> Hash Left Join (cost=350.57..176305.83 rows=9640 width=212)'
' Hash Cond: ((c_invoice.c_doctype_id)::text = (c_doctype.c_doctype_id)::text)'
' -> Nested Loop (cost=35.19..175809.70 rows=9640 width=239)'
' -> Hash Join (cost=34.64..27818.10 rows=2499 width=109)'
' Hash Cond: ((c_invoice.ad_org_id)::text = (ad_org.ad_org_id)::text)'
' -> Nested Loop (cost=0.98..27750.08 rows=2499 width=110)'
' -> Seq Scan on c_bp_group (cost=0.00..1.19 rows=1 width=82)'
' Filter: ((c_bp_group_id)::text = '21FE15587FE048D4B898272F4D44176A'::text)'
' -> Nested Loop (cost=0.98..27723.90 rows=2499 width=143)'
' -> Index Scan using c_invoice_client_org_date_doc on c_invoice (cost=0.56..7096.37 rows=3155 width=257)'
' Index Cond: (((ad_client_id)::text = ANY ('{0,101C8C9C4A8D4C84857E3F5FBD722757}'::text[])) AND ((ad_org_id)::text = ANY ('{C002170F73544C6E9AAEC8DD7FBB5750,0A733B3F5292414BA2310836A3EE9B73,664FFE8B17974ABCBE4215D77996BB16,AA6E6D54ACC94E3DA142B1CA42C332E6,E168567EAFF94B4793996B0AB415930B,24D0809B7ACB4BEEBFD742E6D3BF1004,61D784C8E7B24C61864CAAC67AC3554F,9F488865B7FF4341938EBA9D94204795,2A85B2F756EA492897FA0C5061D35C04,33DD7E752F9E4370AAE84B7B6B1EA5AD}'::text[])) AND (dateinvoiced >= '2017-04-01 00:00:00'::timestamp without time zone) AND (dateinvoiced < '2017-06-01 00:00:00'::timestamp without time zone))'
' Filter: ((issotrx = 'Y'::bpchar) AND (processed = 'Y'::bpchar))'
' -> Index Scan using c_bpartner_idsalesrep on c_bpartner (cost=0.42..6.53 rows=1 width=99)'
' Index Cond: ((c_bpartner_id)::text = (c_invoice.c_bpartner_id)::text)'
' Filter: ((c_bp_group_id)::text = '21FE15587FE048D4B898272F4D44176A'::text)'
' -> Hash (cost=31.21..31.21 rows=196 width=32)'
' -> Index Only Scan using ad_org_key on ad_org (cost=0.27..31.21 rows=196 width=32)'
' -> Index Scan using c_invoiceline_invoice on c_invoiceline (cost=0.55..59.06 rows=16 width=196)'
' Index Cond: ((c_invoice_id)::text = (c_invoice.c_invoice_id)::text)'
' -> Hash (cost=256.28..256.28 rows=4728 width=37)'
' -> Seq Scan on c_doctype (cost=0.00..256.28 rows=4728 width=37)'
' -> Index Scan using m_product_key on m_product (cost=0.42..0.45 rows=1 width=73)'
' Index Cond: ((c_invoiceline.m_product_id)::text = (m_product_id)::text)'
' -> Hash (cost=1.22..1.22 rows=22 width=98)'
' -> Seq Scan on c_uom (cost=0.00..1.22 rows=22 width=98)'
' -> Index Scan using m_inoutline_key on m_inoutline (cost=0.55..0.80 rows=1 width=66)'
' Index Cond: ((c_invoiceline.m_inoutline_id)::text = (m_inoutline_id)::text)'
' -> Index Scan using m_transaction_inoutline on m_transaction trx (cost=0.43..0.63 rows=1 width=145)'
' Index Cond: ((m_inoutline_id)::text = (m_inoutline.m_inoutline_id)::text)'
QueryPlanAfterChange.txt [^] (11,690 bytes) 2017-10-05 18:03 [Show Content] [Hide Content]Sort (cost=250897.45..250897.95 rows=200 width=592) (actual time=32758.798..32759.039 rows=1604 loops=1)
Sort Key: (to_char(((ad_column_identifier('M_Product'::character varying, to_char(m_product.m_product_id), 'en_US'::character varying))::tex
t || (CASE WHEN (c_uom.uomsymbol IS NULL) THEN ''::character varying ELSE to_char(((' ('::text || (c_uom.uomsymbol)::text) || ')'::text)) END):
:text)))
Sort Method: quicksort Memory: 467kB
-> HashAggregate (cost=250785.31..250889.81 rows=200 width=592) (actual time=32650.848..32746.581 rows=1604 loops=1)
-> HashAggregate (cost=249673.09..250700.14 rows=1002 width=574) (actual time=32430.756..32621.931 rows=7803 loops=1)
-> Sort (cost=248771.65..248796.69 rows=10016 width=784) (actual time=32352.921..32375.758 rows=25253 loops=1)
Sort Key: (to_char(((ad_column_identifier('M_Product'::character varying, to_char(m_product.m_product_id), 'en_US'::charac
ter varying))::text || (CASE WHEN (c_uom.uomsymbol IS NULL) THEN ''::character varying ELSE to_char(((' ('::text || (c_uom.uomsymbol)::text) ||
')'::text)) END)::text)))
Sort Method: external merge Disk: 13912kB
-> HashAggregate (cost=224768.80..248106.08 rows=10016 width=784) (actual time=2522.099..31877.304 rows=25253 loops=1)
-> Nested Loop Left Join (cost=364.63..223942.48 rows=10016 width=784) (actual time=7.230..2417.082 rows=25271 loo
ps=1)
-> Nested Loop Left Join (cost=364.20..217870.66 rows=9640 width=774) (actual time=7.227..2127.428 rows=2525
3 loops=1)
-> Nested Loop Left Join (cost=363.77..211683.23 rows=9640 width=695) (actual time=7.225..1762.164 row
s=25253 loops=1)
-> Nested Loop Left Join (cost=363.35..206656.93 rows=9640 width=695) (actual time=7.223..1435.7
20 rows=25253 loops=1)
-> Nested Loop Left Join (cost=362.80..198857.78 rows=9640 width=662) (actual time=7.220..
999.009 rows=25253 loops=1)
-> Hash Left Join (cost=362.66..197240.86 rows=9640 width=682) (actual time=7.218..8
22.167 rows=25253 loops=1)
Hash Cond: ((c_invoiceline.c_uom_id)::text = (c_uom.c_uom_id)::text)
-> Nested Loop Left Join (cost=361.16..197106.82 rows=9640 width=687) (actual time=7.169..796.233 rows=25253 loops=1)
-> Hash Left Join (cost=360.75..192638.39 rows=9640 width=647) (actual time=7.165..480.107 rows=25253 loops=1)
Hash Cond: ((c_invoice.c_doctype_id)::text = (c_doctype.c_doctype_id)::text)
-> Nested Loop (cost=45.37..192142.26 rows=9640 width=674) (actual time=0.846..447.939 rows=25253 loops=1)
-> Nested Loop Left Join (cost=44.81..43995.78 rows=2502 width=511) (actual time=0.814..251.351 rows=6778 loops=1)
Join Filter: ((c_invoice.c_project_id)::text = (c_project.c_project_id)::text)
-> Nested Loop Left Join (cost=44.81..43610.35 rows=2502 width=455) (actual time=0.804..244.061 rows=6778 loops=1)
-> Hash Join (cost=44.39..27857.37 rows=2502 width=443) (actual time=0.730..148.207 rows=6778 loops=1)
Hash Cond: ((c_invoice.ad_org_id)::text = (ad_org.ad_org_id)::text)
-> Nested Loop (cost=0.98..27779.56 rows=2502 width=411) (actual time=0.336..141.139 rows=6778 loops=1)
-> Seq Scan on c_bp_group (cost=0.00..1.19 rows=1 width=220) (actual time=0.011..0.018 rows=1 loops=1)
Filter: ((c_bp_group_id)::text ='21FE15587FE048D4B898272F4D44176A'::text)
Rows Removed by Filter: 14
-> Nested Loop (cost=0.98..27753.35 rows=2502 width=306) (actual time=0.308..137.753 rows=6778 loops=1)
-> Index Scan using c_invoice_client_org_date_doc on c_invoice (cost=0.56..7104.01 rows=3159 width=273) (actual time=0.275..24.418 rows=6779 loops=1)
Index Cond: (((ad_client_id)::text = ANY ('{0,101C8C9C4A8D4C84857E3F5FBD722757}'::text[])) AND ((ad_org_id)::text = ANY ('{C002170F73544C6E9AAEC8DD7FBB5750,0A733B3F5292414BA2310836A3EE9B73,664FFE8B17974ABCBE4215D77996BB16,AA6E6D54ACC94E3DA142B1CA42C332E6,E168567EAFF94B4793996B0AB415930B,24D0809B7ACB4BEEBFD742E6D3BF1004,61D784C8E7B24C61864CAAC67AC3554F,9F488865B7FF4341938EBA9D94204795,2A85B2F756EA492897FA0C5061D35C04,33DD7E752F9E4370AAE84B7B6B1EA5AD}'::text[])) AND (dateinvoiced >= '2017-04-01 00:00:00'::timestamp without time zone) AND (dateinvoiced < '2017-06-01 00:00:00'::timestamp without time zone))
Filter: ((issotrx = 'Y'::bpchar) AND (processed = 'Y'::bpchar))
Rows Removed by Filter: 311
-> Index Scan using c_bpartner_idsalesrep on c_bpartner (cost=0.42..6.53 rows=1 width=99) (actual time=0.014..0.015 rows=1 loops=6779)
Index Cond: ((c_bpartner_id)::text = (c_invoice.c_bpartner_id)::text)
Filter: ((c_bp_group_id)::text = '21FE15587FE048D4B898272F4D44176A'::text)
Rows Removed by Filter: 0
-> Hash (cost=40.96..40.96 rows=196 width=65) (actual time=0.378..0.378 rows=196 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 19kB
-> Seq Scan on ad_org (cost=0.00..40.96 rows=196 width=65) (actual time=0.032..0.255 rows=196 loops=1)
-> Index Scan using ad_user_key on ad_user (cost=0.42..6.29 rows=1 width=44) (actual time=0.012..0.013 rows=1 loops=6778)
Index Cond: ((c_invoice.salesrep_id)::text = (ad_user_id)::text)
-> Materialize (cost=0.00..10.15 rows=10 width=220) (actual time=0.000..0.000 rows=0 loops=6778)
-> Seq Scan on c_project (cost=0.00..10.10 rows=10 width=220) (actual time=0.001..0.001 rows=0 loops=1)
-> Index Scan using c_invoiceline_invoice on c_invoiceline (cost=0.55..59.05 rows=16 width=229) (actual time=0.021..0.025 rows=4 loops=6778)
Index Cond: ((c_invoice_id)::text = (c_invoice.c_invoice_id)::text)
-> Hash (cost=256.28..256.28 rows=4728 width=37) (actual time=6.305..6.305 rows=4728 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 324kB
-> Seq Scan on c_doctype (cost=0.00..256.28 rows=4728 width=37) (actual time=0.005..3.623 rows=4728 loops=1)
-> Index Scan using m_product_key on m_product (cost=0.42..0.45 rows=1 width=73) (actual time=0.010..0.011 rows=1 loops=25253)
Index Cond: ((c_invoiceline.m_product_id)::text = (m_product_id)::text)
-> Hash (cost=1.22..1.22 rows=22 width=98) (actual time=0.032..0.032 rows=23 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on c_uom (cost=0.00..1.22 rows=22 width=98) (actual time=0.007..0.021 rows=23 loops=1)
-> Index Scan using m_product_category_key on m_product_category (cost=0.14..0.16 rows=1 width=46) (actual time=0.005..0.006 rows=1 loops=25253)
Index Cond: ((m_product.m_product_category_id)::text = (m_product_category_id)::text)
-> Index Scan using m_inoutline_key on m_inoutline (cost=0.55..0.80 rows=1 width=66) (actual time=0.015..0.016 rows=1 loops=25253)
Index Cond: ((c_invoiceline.m_inoutline_id)::text = (m_inoutline_id)::text)
-> Index Scan using m_inout_key on m_inout (cost=0.42..0.51 rows=1 width=66) (actual time=0.011..0.011 rows=1 loops=25253)
Index Cond: ((m_inoutline.m_inout_id)::text = (m_inout_id)::text)
-> Index Scan using m_transaction_inoutline on m_transaction trx (cost=0.43..0.63 rows=1 width=145) (actual time=0.013..0.013 rows=1 loops=25253)
Index Cond: ((m_inoutline_id)::text = (m_inoutline.m_inoutline_id)::text)
-> Index Scan using m_transaction_cost_trx on m_transaction_cost trxcost (cost=0.43..0.62 rows=1 width=43) (actual time=0.010..0.010 rows=1 loops=25253)
Index Cond: ((trx.m_transaction_id)::text = (m_transaction_id)::text)
Total runtime: 32764.511 ms
(66 rows)
|