0037834: Performance issue in Create Invoices From Orders window

'Sort (cost=25814771.93..25814772.18 rows=100 width=339)' ' Sort Key: o.name, bp.name, o_1.dateordered' ' -> Nested Loop (cost=13979308.56..25814768.61 rows=100 width=339)' ' -> Hash Join (cost=13979308.14..25814681.10 rows=100 width=342)' ' Hash Cond: ((o_1.c_doctype_id)::text = (dt.c_doctype_id)::text)' ' -> Nested Loop (cost=13978555.03..25813926.24 rows=100 width=323)' ' -> Hash Join (cost=13978554.47..25731217.38 rows=13285 width=325)' ' Hash Cond: ((o_1.ad_org_id)::text = (o.ad_org_id)::text)' ' -> Hash Join (cost=13978536.94..25731003.53 rows=16923 width=339)' ' Hash Cond: ((o_1.invoicerule)::text = (l.value)::text)' ' -> GroupAggregate (cost=13978524.92..25612480.02 rows=7282601 width=231)' ' -> Sort (cost=13978524.92..13996731.42 rows=7282601 width=231)' ' Sort Key: o_1.ad_client_id, o_1.ad_org_id, o_1.c_bpartner_id, o_1.c_order_id, o_1.documentno, o_1.dateordered, o_1.c_doctype_id, o_1.totallines, o_1.grandtotal, o_1.invoicerule, c.c_currency_id, c.stdprecision' ' -> Hash Join (cost=6765531.65..12681910.14 rows=7282601 width=231)' ' Hash Cond: ((l_1.c_tax_id)::text = (t.c_tax_id)::text)' ' -> Hash Join (cost=6765527.46..12581770.19 rows=7282601 width=260)' ' Hash Cond: ((l_1.c_order_id)::text = (o_1.c_order_id)::text)' ' -> Seq Scan on c_orderline l_1 (cost=0.00..4349675.70 rows=41035602 width=85)' ' Filter: ((abs((qtyordered - qtyinvoiced)) <> 0::numeric) OR (abs((qtydelivered - qtyinvoiced)) <> 0::numeric))' ' -> Hash (cost=6669682.97..6669682.97 rows=2347959 width=208)' ' -> Hash Join (cost=24910.40..6669682.97 rows=2347959 width=208)' ' Hash Cond: ((o_1.c_currency_id)::text = (c.c_currency_id)::text)' ' -> Hash Left Join (cost=24903.44..6637391.57 rows=2347959 width=204)' ' Hash Cond: ((bp_1.c_invoiceschedule_id)::text = (si.c_invoiceschedule_id)::text)' ' Filter: (((o_1.invoicerule)::text = 'I'::text) OR ((o_1.invoicerule)::text = 'O'::text) OR ((o_1.invoicerule)::text = 'D'::text) OR (((o_1.invoicerule)::text = 'S'::text) AND ((si.invoicefrequency IS NULL) OR ((si.invoicefrequency)::text = 'D'::text) OR ((si.invoicefrequency)::text = 'W'::text) OR (((si.invoicefrequency)::text = 'T'::text) AND (trunc(o_1.dateordered) <= ((trunc(now(), 'MM'::character varying) + si.invoicedaycutoff) - 1)) AND (trunc(now()) >= ((trunc((o_1.dateordered)::timestamp with time zone, 'MM'::character varying) + si.invoiceday) - 1))) OR ((trunc(o_1.dateordered) <= ((trunc(now(), 'MM'::character varying) + si.invoicedaycutoff) + 14)) AND (trunc(now()) >= ((trunc((o_1.dateordered)::timestamp with time zone, 'MM'::character varying) + si.invoiceday) + 14))) OR (((si.invoicefrequency)::text = 'M'::text) AND (trunc(o_1.dateordered) <= ((trunc(now(), 'MM'::character varying) + si.invoicedaycutoff) - 1)) AND (trunc(now()) >= ((trunc((o_1.dateordered)::timestamp with time zone, 'MM'::character varying) + si.invoiceday) - 1))))))' ' -> Hash Join (cost=24902.42..6628571.14 rows=2350620 width=286)' ' Hash Cond: ((o_1.c_bpartner_id)::text = (bp_1.c_bpartner_id)::text)' ' -> Hash Join (cost=265.28..6548106.77 rows=2350620 width=204)' ' Hash Cond: ((o_1.c_doctype_id)::text = (dt_1.c_doctype_id)::text)' ' -> Index Scan using c_order_client_org_date_docno on c_order o_1 (cost=1.07..6476521.27 rows=12750693 width=204)' ' Index Cond: (((ad_client_id)::text = ANY ('{0,9D11C7AED56349F186249FDAD0B69515}'::text[])) AND ((ad_org_id)::text = ANY ('{0,023DF36C03AB4BF681C03DC310F0FD14,02A339D6E06B4E43B9B8FBFF263CB337,044D5A64FA214E9B9275A2A7BA08BE6C,04FA68D5718F486EB0F6789A205D984E,0616ACD0F4CD4D59933DCCB2BAD12AAC,0B051DE44D7D4D069C032B0961A39E13,0BC83AB4049349F6A970C1E196FECB55,0DF2CA6752E34596ABD36BCB8886BCB0,0E122C9CDC404086A37E86558CE67F17,0E5A371867734AE5B9DB22C07EEFF4A6,0EED637FC6DD4FC6A9E4ACE66F349D60,103E4378B81245428BAD94E1DB9E9841,10B10B421D354B068E724911376D0EAB,1199229CE3F54357BF83995E23209820,1294C2248D524FB7AEE22073B3DF1F46,14DDB18729144E98B991D5FE2C00E2D2,1ABF28E04092430DB70427FA66C51900,1AEBCB33F47649A798CA7F361BBA66F9,1B48B7801D5A4B45A87EE7EA6BE70CB3,1E99A66B12BC41888E3626B1CA24701A,1FC710785A214BC7BF069205A0E506FA,20409C0E6CD042348E768EF9DD3553F4,22283C0E837744B6A95DE7798529D149,227DB929435740A8AC0BF8CC0B3FFDC8,2288A0958718423887FE0373CBC79163,239E9315970D489CB336D51F8A4C1D6D,243AEDF44E2D4CA2A993CADBCC07285F,25C9322D670C4308B484DAE04140AB5C,261D4C493B9B48948E4042003F914FB0,28B527E15A2341EBA3E9842BD18A2A07,29F668B642D3483D96710097AA715F44,2B5C645F90784ED88523C16874D05875,2BB23DF1D10743BD944C3E643F81A21D,2FAEE704A31645309E77399BE80EF00C,3030D85BD5854D9783B37D5B1984EBCD,31C121B2A0864F37AF23760879DCF376,340F5D97595F4DDF83EF0AFF133C11DE,387F54B230D84A458223910ECAA46585,388F51CA3B09465E8F863A8885364087,3D05638ABCD042BF8AE45048E3EAA993,3E8E55D9333546A2ADF0591D6BBB5202,4020122B392D4B8FAC4B71ABC007BE8E,4086CFCC0FC8449BBACCBFD45A79AB91,43AF06D02F7F4F26BC3EA1135AC279A9,43B4C56A6ABC45C2999858601EF452D6,45AB7A0BA23A47E5A5143024C33AD250,4A1E50C7661648BF969CCE6878D199DB,4DBE2BD989D14328A8996E4171AB62F7,4EEBA27705D74460B125AC3796BB5044,5277569712DE4809BA93EBC1BEC60A1D,5448E3E9D0DF4EE78082AD21C8F67693,57E3793F499148529DEA9DC0B48BA0B5,59323FEEA128423D8E6CFE52C9469840,59332BF8A9324107A30D9F93E048F6F4,5A95CE93A8BC424DB6994DFD21BAF960,5BA277A736C94948A434D340178D9BAC,602BF7D1EC0B41BEB412EA532D880F31,633D90BC32214B03BC67F88F9BB6CF47,638BFC40E0C64C17955C1D20A3A51679,6440820F77794731922C332C22EB0C5E,65C7845EF4E440A69415C8235EA85557,67880CE9DB3A4614B71E5F540E5FD84F,691321964EFB422C9F9A8431C13BD454,6A85E17259ED43508E6672BB86186246,6B8A39712CBD47E692DFD6909674498C,6ED25D3CA5CA4AE896D13804C2516FE8,6F5A6A0BA4A54D0497CCE82A9F75DF22,6F729785F8CA4B66B1CE8BBBE7257154,74A75D4B82774C8F84325B7C6C25E901,766EE695426C4BBAB718F3C204A7DF9C,76774EF90C894499AEDDE8AF585995A6,7801B61436E24E52B0078F14F7270B27,78CCE833C2D442E0B55FA75C0F46688B,793A6F70CF0647088CD84E539CDC3950,7A243C1DC47E4DDD8052DF306F01CE2A,7B1238161D2D45CB917804C763C8604E,7D693EA6788F44818C46AE9C592EDC18,7FA06B6FC46C4187BE1FB346A92D1928,81EF509AC26047C79BB88FB00F62848B,829FB78685104D3AB3C6F5CB4EC895FC,8626F42D0E284E68BC9BD3D9C9E8EDCA,873D1C0F51AF41AAAB56C5DB61F72163,87B1A4B698FD460D97FEFF071F32A6ED,8801836C2A304F1FAD53C2E9200CA2B7,88454885E1A34B16AFC51BC6190DF002,8A6645244D2B40DBBD8963035E38F6B5,8BB8060F43CE4316BCC9639A75E1301B,8D58D404736B432D901E687893E8E171,91FDEC433CB54A8A803019CF23ADDEB5,95165634C8CE44088AB3A0113084DF0A,95A2353A5223434982403F0700489AAE,99E8D23A83E54E0DB95B51BBFF434C63,9AE96E0DBF854535BB6C7C8E5CC83149,9BE0ED62B4EC41E0AA0258877627B882,9CA9D2765EEE40DE91A15245FB3AA872,9CEEB4ECBCD14C6EABD9C104C20166D3,A06BBA017F0E4DCA9100E2A9092C7DE6,A078C773065C45EB94AE75E1DD46F16C,A09702376A7745AEAC55E2ADA23D1FD3,A1A1D9FA06384AA9B5CC2F35D39B0199,A35AFDBFD4214132B977821A61AF168F,A3B2AF75C3144204AEA18DE4019FD5BC,AAFE16A9BF6E493CB87A8556A0BDC02B,ABD3C740184C4A8E892B801CBEC7503B,AC14BC4670014376B176B39116ADE55E,B0CA1DA22EE241F79859CC153F96067F,B2ACE3EF4EC747799EB37C70BE33AB24,B3C7EF2F11B4443EA3EF7EFD6EE158A2,B552116D5B754A2D9DB6FE49CB5EB2B3,B5DBCF8701EF485DA55F78E04A1CE627,B60F88C677E641879AA07E36F204505C,B6A5F340B17246B3B19C7A2F35F1931C,B7B8D2E0EBF74087B2802DD75B2C3AAF,B818621BBC5A43A699933449B8EA37B8,BC2845E04C604D72A12D795C3577A135,BCB92A47909F4D67A5910722C48D66CD,BD46249726054687BB877095378BE8F0,BE4DA00046EC4EA694B08A38B55C4046,C39A954461A649339543DD70FCD8DDD0,C72216DA7849460690616CCC0B76E7AF,C91D20D24575415CB81965907FA80AB9,CA9E6A181368432C8A3EA4B9C395776E,CBF4429631674F87BB3D29A463D475F7,CCA64F7F3C114C62B7218B16B7BECB3E,CED6CCEB673E4C069B8D1167CF54CD5E,D055341711154E14AA89BA39278AA072,D06F60CFD03941F78460BB4737687F06,D0B3BBFAE2A5470D88906F87EFB57B9F,D1405FE61EC84371B36E606D0F07D820,D1C7D0C73A744C0AA9B78C3715DAC366,D3337B4F074545E0A5D68D36F64DE0D0,D503E2FFF9204F6BBC1765C1CEE059E4,D701705CCB6A48199723A207ED1146B1,DA60E38381FE45248722805FE870DEF1,DD67968132604C67BBDB4A6579484F3D,DDF1147346EA4DAE8B96E51F08CD8E65,DE67F772A3DE49C8B78C3A2C7D3B9A8F,DEADD9ABDE554BF79774B80D25F7C5D5,E0B7A44F4C674D63BA728AED499FD18A,E3D4869CA0A34E3B8FB110418FBBF50E,E80ABB58775C4FAB9C24C13AB24436BB,E9948F4F822941F9A2BC5065921BA13E,EA092A6B78D343F5897FC4328C897633,ECA7B310948A478A914405F7A918BE9F,ED65A777F6824380856721266386913D,EF39BA0931904958AB8A91BC36D2B4EC,F41C2A14741F465A932EE07CD6FFEBBC,F421A0DF88FD44A988A293ECCB381180,F572A6855237472C8A7B422B430E5EF3,F95D56BB3B1E499ABCB3CB85B5520C0E,F974CB757D91497D9E0F58529D918E87,FB1B2625EBA94A0281AD95065A546AFD,FCD93C62A1774190BA2B50A6CA98D36E,FCF6AC5C03AE406DBD7F41A7AD5B1CD9}'::text[])) AND ((ad_org_id)::text = ANY ('{FCF6AC5C03AE406DBD7F41A7AD5B1CD9,FCD93C62A1774190BA2B50A6CA98D36E,FB1B2625EBA94A0281AD95065A546AFD,F974CB757D91497D9E0F58529D918E87,F95D56BB3B1E499ABCB3CB85B5520C0E,F572A6855237472C8A7B422B430E5EF3,F421A0DF88FD44A988A293ECCB381180,F41C2A14741F465A932EE07CD6FFEBBC,EF39BA0931904958AB8A91BC36D2B4EC,ED65A777F6824380856721266386913D,ECA7B310948A478A914405F7A918BE9F,EA092A6B78D343F5897FC4328C897633,E9948F4F822941F9A2BC5065921BA13E,E80ABB58775C4FAB9C24C13AB24436BB,E3D4869CA0A34E3B8FB110418FBBF50E,E0B7A44F4C674D63BA728AED499FD18A,DEADD9ABDE554BF79774B80D25F7C5D5,DE67F772A3DE49C8B78C3A2C7D3B9A8F,DDF1147346EA4DAE8B96E51F08CD8E65,DD67968132604C67BBDB4A6579484F3D,DA60E38381FE45248722805FE870DEF1,D701705CCB6A48199723A207ED1146B1,D503E2FFF9204F6BBC1765C1CEE059E4,D3337B4F074545E0A5D68D36F64DE0D0,D1C7D0C73A744C0AA9B78C3715DAC366,D1405FE61EC84371B36E606D0F07D820,D0B3BBFAE2A5470D88906F87EFB57B9F,D06F60CFD03941F78460BB4737687F06,D055341711154E14AA89BA39278AA072,CED6CCEB673E4C069B8D1167CF54CD5E,CCA64F7F3C114C62B7218B16B7BECB3E,CBF4429631674F87BB3D29A463D475F7,CA9E6A181368432C8A3EA4B9C395776E,C91D20D24575415CB81965907FA80AB9,C72216DA7849460690616CCC0B76E7AF,C39A954461A649339543DD70FCD8DDD0,BE4DA00046EC4EA694B08A38B55C4046,BD46249726054687BB877095378BE8F0,BCB92A47909F4D67A5910722C48D66CD,BC2845E04C604D72A12D795C3577A135,B818621BBC5A43A699933449B8EA37B8,B7B8D2E0EBF74087B2802DD75B2C3AAF,B6A5F340B17246B3B19C7A2F35F1931C,B60F88C677E641879AA07E36F204505C,B5DBCF8701EF485DA55F78E04A1CE627,B552116D5B754A2D9DB6FE49CB5EB2B3,B3C7EF2F11B4443EA3EF7EFD6EE158A2,B2ACE3EF4EC747799EB37C70BE33AB24,B0CA1DA22EE241F79859CC153F96067F,AC14BC4670014376B176B39116ADE55E,ABD3C740184C4A8E892B801CBEC7503B,AAFE16A9BF6E493CB87A8556A0BDC02B,A3B2AF75C3144204AEA18DE4019FD5BC,A35AFDBFD4214132B977821A61AF168F,A1A1D9FA06384AA9B5CC2F35D39B0199,A09702376A7745AEAC55E2ADA23D1FD3,A078C773065C45EB94AE75E1DD46F16C,A06BBA017F0E4DCA9100E2A9092C7DE6,9CEEB4ECBCD14C6EABD9C104C20166D3,9CA9D2765EEE40DE91A15245FB3AA872,9BE0ED62B4EC41E0AA0258877627B882,9AE96E0DBF854535BB6C7C8E5CC83149,99E8D23A83E54E0DB95B51BBFF434C63,95A2353A5223434982403F0700489AAE,95165634C8CE44088AB3A0113084DF0A,91FDEC433CB54A8A803019CF23ADDEB5,8D58D404736B432D901E687893E8E171,8BB8060F43CE4316BCC9639A75E1301B,8A6645244D2B40DBBD8963035E38F6B5,88454885E1A34B16AFC51BC6190DF002,8801836C2A304F1FAD53C2E9200CA2B7,87B1A4B698FD460D97FEFF071F32A6ED,873D1C0F51AF41AAAB56C5DB61F72163,8626F42D0E284E68BC9BD3D9C9E8EDCA,829FB78685104D3AB3C6F5CB4EC895FC,81EF509AC26047C79BB88FB00F62848B,7FA06B6FC46C4187BE1FB346A92D1928,7D693EA6788F44818C46AE9C592EDC18,7B1238161D2D45CB917804C763C8604E,7A243C1DC47E4DDD8052DF306F01CE2A,793A6F70CF0647088CD84E539CDC3950,78CCE833C2D442E0B55FA75C0F46688B,7801B61436E24E52B0078F14F7270B27,76774EF90C894499AEDDE8AF585995A6,766EE695426C4BBAB718F3C204A7DF9C,74A75D4B82774C8F84325B7C6C25E901,6F729785F8CA4B66B1CE8BBBE7257154,6F5A6A0BA4A54D0497CCE82A9F75DF22,6ED25D3CA5CA4AE896D13804C2516FE8,6B8A39712CBD47E692DFD6909674498C,6A85E17259ED43508E6672BB86186246,691321964EFB422C9F9A8431C13BD454,67880CE9DB3A4614B71E5F540E5FD84F,65C7845EF4E440A69415C8235EA85557,6440820F77794731922C332C22EB0C5E,638BFC40E0C64C17955C1D20A3A51679,633D90BC32214B03BC67F88F9BB6CF47,602BF7D1EC0B41BEB412EA532D880F31,5BA277A736C94948A434D340178D9BAC,5A95CE93A8BC424DB6994DFD21BAF960,59332BF8A9324107A30D9F93E048F6F4,59323FEEA128423D8E6CFE52C9469840,57E3793F499148529DEA9DC0B48BA0B5,5448E3E9D0DF4EE78082AD21C8F67693,5277569712DE4809BA93EBC1BEC60A1D,4EEBA27705D74460B125AC3796BB5044,4DBE2BD989D14328A8996E4171AB62F7,4A1E50C7661648BF969CCE6878D199DB,45AB7A0BA23A47E5A5143024C33AD250,43B4C56A6ABC45C2999858601EF452D6,43AF06D02F7F4F26BC3EA1135AC279A9,4086CFCC0FC8449BBACCBFD45A79AB91,4020122B392D4B8FAC4B71ABC007BE8E,3E8E55D9333546A2ADF0591D6BBB5202,3D05638ABCD042BF8AE45048E3EAA993,388F51CA3B09465E8F863A8885364087,387F54B230D84A458223910ECAA46585,340F5D97595F4DDF83EF0AFF133C11DE,31C121B2A0864F37AF23760879DCF376,3030D85BD5854D9783B37D5B1984EBCD,2FAEE704A31645309E77399BE80EF00C,2BB23DF1D10743BD944C3E643F81A21D,2B5C645F90784ED88523C16874D05875,29F668B642D3483D96710097AA715F44,28B527E15A2341EBA3E9842BD18A2A07,261D4C493B9B48948E4042003F914FB0,25C9322D670C4308B484DAE04140AB5C,243AEDF44E2D4CA2A993CADBCC07285F,239E9315970D489CB336D51F8A4C1D6D,2288A0958718423887FE0373CBC79163,227DB929435740A8AC0BF8CC0B3FFDC8,22283C0E837744B6A95DE7798529D149,20409C0E6CD042348E768EF9DD3553F4,1FC710785A214BC7BF069205A0E506FA,1E99A66B12BC41888E3626B1CA24701A,1B48B7801D5A4B45A87EE7EA6BE70CB3,1AEBCB33F47649A798CA7F361BBA66F9,1ABF28E04092430DB70427FA66C51900,14DDB18729144E98B991D5FE2C00E2D2,1294C2248D524FB7AEE22073B3DF1F46,1199229CE3F54357BF83995E23209820,10B10B421D354B068E724911376D0EAB,103E4378B81245428BAD94E1DB9E9841,0EED637FC6DD4FC6A9E4ACE66F349D60,0E5A371867734AE5B9DB22C07EEFF4A6,0E122C9CDC404086A37E86558CE67F17,0DF2CA6752E34596ABD36BCB8886BCB0,0BC83AB4049349F6A970C1E196FECB55,0B051DE44D7D4D069C032B0961A39E13,0616ACD0F4CD4D59933DCCB2BAD12AAC,04FA68D5718F486EB0F6789A205D984E,044D5A64FA214E9B9275A2A7BA08BE6C,02A339D6E06B4E43B9B8FBFF263CB337,023DF36C03AB4BF681C03DC310F0FD14,0}'::text[])))' ' Filter: ((docstatus)::text = ANY ('{CO,CL,IP}'::text[]))' ' -> Hash (cost=254.37..254.37 rows=787 width=32)' ' -> Seq Scan on c_doctype dt_1 (cost=0.00..254.37 rows=787 width=32)' ' Filter: (((docbasetype)::text = 'SOO'::text) AND ((docsubtypeso)::text <> ALL ('{ON,OB,WR}'::text[])))' ' -> Hash (cost=22728.73..22728.73 rows=152673 width=115)' ' -> Seq Scan on c_bpartner bp_1 (cost=0.00..22728.73 rows=152673 width=115)' ' -> Hash (cost=1.01..1.01 rows=1 width=45)' ' -> Seq Scan on c_invoiceschedule si (cost=0.00..1.01 rows=1 width=45)' ' -> Hash (cost=4.76..4.76 rows=176 width=8)' ' -> Seq Scan on c_currency c (cost=0.00..4.76 rows=176 width=8)' ' -> Hash (cost=3.53..3.53 rows=53 width=37)' ' -> Seq Scan on c_tax t (cost=0.00..3.53 rows=53 width=37)' ' -> Hash (cost=11.98..11.98 rows=3 width=44)' ' -> Nested Loop Left Join (cost=0.56..11.98 rows=3 width=44)' ' -> Index Scan using ad_ref_list_value on ad_ref_list l (cost=0.28..4.45 rows=3 width=49)' ' Index Cond: ((ad_reference_id)::text = '150'::text)' ' -> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl ltrl (cost=0.28..2.50 rows=1 width=43)' ' Index Cond: (((l.ad_ref_list_id)::text = (ad_ref_list_id)::text) AND ((ad_language)::text = 'es_MX'::text))' ' -> Hash (cost=15.57..15.57 rows=157 width=51)' ' -> Seq Scan on ad_org o (cost=0.00..15.57 rows=157 width=51)' ' -> Index Only Scan using c_order_key on c_order ord (cost=0.56..6.22 rows=1 width=33)' ' Index Cond: (c_order_id = (o_1.c_order_id)::text)' ' Filter: ((((o_1.invoicerule)::text = 'D'::text) AND ((sum(abs(l_1.qtydelivered))) <> 0::numeric) AND (SubPlan 1)) OR (((o_1.invoicerule)::text = 'I'::text) AND (SubPlan 2)) OR (((o_1.invoicerule)::text = 'O'::text) AND ((sum(abs(l_1.qtyordered))) = (sum(abs(l_1.qtydelivered))))) OR (((o_1.invoicerule)::text = 'S'::text) AND (SubPlan 3)))' ' SubPlan 1' ' -> Index Scan using c_orderline_order on c_orderline ol (cost=0.56..222.74 rows=194 width=0)' ' Index Cond: ((c_order_id)::text = (ord.c_order_id)::text)' ' Filter: ((qtydelivered - qtyinvoiced) <> 0::numeric)' ' SubPlan 2' ' -> Index Scan using c_orderline_order on c_orderline ol_1 (cost=0.56..222.74 rows=194 width=0)' ' Index Cond: ((c_order_id)::text = (ord.c_order_id)::text)' ' Filter: ((qtyordered - qtyinvoiced) <> 0::numeric)' ' SubPlan 3' ' -> Index Scan using c_orderline_order on c_orderline ol_2 (cost=0.56..222.74 rows=166 width=0)' ' Index Cond: ((c_order_id)::text = (ord.c_order_id)::text)' ' Filter: ((qtydelivered <> 0::numeric) AND (qtydelivered <> qtyinvoiced))' ' -> Hash (cost=699.75..699.75 rows=4269 width=83)' ' -> Hash Right Join (cost=281.05..699.75 rows=4269 width=83)' ' Hash Cond: ((dttrl.c_doctype_id)::text = (dt.c_doctype_id)::text)' ' -> Seq Scan on c_doctype_trl dttrl (cost=0.00..348.10 rows=4034 width=58)' ' Filter: ((ad_language)::text = 'es_MX'::text)' ' -> Hash (cost=227.69..227.69 rows=4269 width=57)' ' -> Seq Scan on c_doctype dt (cost=0.00..227.69 rows=4269 width=57)' ' -> Index Scan using c_bpartner_key on c_bpartner bp (cost=0.42..0.86 rows=1 width=63)' ' Index Cond: ((c_bpartner_id)::text = (o_1.c_bpartner_id)::text)'

Notes
(0102449) collazoandy4 (reporter) 2018-02-15 17:52	Test Plan Go to Sales Order and create three new records: 1. Business Partner: Alimentos y Supermercados, S.A, Invoice Terms: Immediate, Product: Agua sin Gas 1L, Ordered Quantity: 5 2. Business Partner: Hoteles Buenas Noches, S.A., Invoice Terms: After Delivery, Product: Zumo de Pera 0,5L, Ordered Quantity: 6 3. Business Partner: Restaurantes Luna Llena, S.A., Invoice Terms: After Order Delivered, Product: Zumo de Piña 0,5L, Ordered Quantity: 7 Go to Goods Shipment window and create a new record: Business Partner: Hoteles Buenas Noches, S.A. Create Lines From the previous order created Edit the line created Movement Quantity: 3 Complete the shipment Create another record in Header tab: Business Partner: Restaurantes Luna Llena, S.A. Create Lines From the previous order created Complete the shipment Open Create Invoices from Orders window and check 3 records are shown (one for each busines partner) Set From Date: Tomorrow and check no record is shown Set From Date: Yesterday and To Date: Today and check three records is shown Check Lines Include Taxes: Y and check Total Gross = Total Lines

(0102450) collazoandy4 (reporter) 2018-02-15 17:53 edited on: 2018-02-16 17:19	With the changes made the execution query cost was reduce from 17724248.29 to 944005.29

(0103140) vmromanos (manager) 2018-03-09 15:18	Moved to design defect: Here we have several problems that affects the performance: 1. In customer environment we have 6M candidate order lines to be invoiced, which results in 191K records to be shown in the window. This crashes the browser when rendering the records because it clearly exceeds the purpose of this manual window. Another process should be used instead to manage such a big number of records. 2. The process is based on the C_Invoice_Candidate_V view and, on top of it, it runs a query that joins with other tables and implements some logic. Problem of this approach is that indexes are not as powerful as having everything in just one query. This situation, and the extremely high number of order lines pending to be invoiced, creates a bottleneck when executing the query. The way to fix it would be to write a separate query without using the view. However this is not a good idea because right now everything related to pending invoicing is centralized in C_Invoice_Candidate_V view.

Issue History
Date Modified	Username	Field	Change
2018-02-05 15:53	JONHM	New Issue
2018-02-05 15:53	JONHM	Assigned To	=> Triage Finance
2018-02-05 15:53	JONHM	File Added: generate_invoices.sql
2018-02-05 15:53	JONHM	Modules	=> Core
2018-02-05 15:53	JONHM	Resolution time	=> 1519599600
2018-02-05 15:53	JONHM	Triggers an Emergency Pack	=> No
2018-02-05 15:53	JONHM	File Added: generate_invoices_test_plan
2018-02-05 15:56	JONHM	Summary	Create Invoices From Orders process takes too long to load => Performance issue in Create Invoices From Orders window
2018-02-05 16:12	jfrances	Issue Monitored: jfrances
2018-02-05 16:24	JONHM	File Deleted: generate_invoices.sql
2018-02-05 16:24	JONHM	File Deleted: generate_invoices_test_plan
2018-02-05 16:24	JONHM	File Added: create_invoices.sql
2018-02-05 16:24	JONHM	File Added: create_invoices_test_plan
2018-02-06 11:20	dmiguelez	Assigned To	Triage Finance => collazoandy4
2018-02-12 20:37	collazoandy4	Status	new => scheduled
2018-02-15 17:52	collazoandy4	Note Added: 0102449
2018-02-15 17:53	collazoandy4	Note Added: 0102450
2018-02-16 17:19	collazoandy4	Note Edited: 0102450	View Revisions
2018-02-21 06:17	AtulOpenbravo	Assigned To	collazoandy4 => AtulOpenbravo
2018-03-09 15:18	vmromanos	Resolution time	1519599600 =>
2018-03-09 15:18	vmromanos	Note Added: 0103140
2018-03-09 15:18	vmromanos	Type	defect => design defect

Relationships [ Relation Graph ] [ Dependency Graph ]