|View Issue Details|
|Type||Category||Severity||Reproducibility||Date Submitted||Last Update|
|defect||[Openbravo ERP] A. Platform||major||have not tried||2020-06-09 17:09||2020-07-02 08:59|
|Priority||high||Resolution||fixed||Fixed in Version||3.0PR20Q3|
|Status||closed||Fix in branch||Fixed in SCM revision||de17990dba45|
|OS Version||Database version||Ant version|
|Product Version||pi||SCM revision|
|Review Assigned To||dmiguelez|
|Regression introduced in release|
|Regression introduced by commit|
|Triggers an Emergency Pack||No|
0044324: Performance Regression after creating new index
|Description||In the Issue 43274 we created a new index on fin_payment table in the columns (documentno, fin_payment_id), expecting it will improve the performance of the window. But only improves for the users which are administrators, however because of an unexpected behavior of the postgres planer, for the users which have only access for some orgs, the index produces an worsening of the window.|
This happens when the numbers of the documents of the organization selected, are the lowest of the table, because the planer always takes the new index and it has to read almost all the table to reach those documentNo, although in this case it would be faster to take another index to filter with the org first, the planer always takes the new index, and it is worse in many cases.
|Steps To Reproduce||In a high volume environment, Do login with an user having access only for one organization with lowest document numbers, open the "Payment In window" and open the grid without any filters.|
|Proposed Solution||The proposed solution is to delete the last created index FIN_PAYMENT_DOCUMENTNO_ID, in the issue https://issues.openbravo.com/view.php?id=44281 [^]|
Because as explained it gets worsen the grid more times than improves.
The problem with the existing index and the query triggered is raised by the Organization fitler.
If there are plenty of Organizations, and there is a Role with limitted Organization access (one or two Organizations) the query will have an additional filter: 'and ad_org_id in ('org_id_1','org_id_2')
The existing index is not taking into account the Organization filter, so it retrieves the rows ordered by Document Number.
If the filters for the selected Organizations are by any change the first records, the query can be fast, but if the selected Organizations are the ones in the lasts records, then the query can be very slow.
Also, the index will not work as expected in Postgres with the 'in' clause.
This is the reason to delte the index, because in this scenarios the performance is worse than the one obtained with the existing index.
If there are clients for which this index can be useful, because they are not in this situation, then this index should be added as a customization for these specific clients.
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Thu Jul 02 11:33:03 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.highvolumesconfig/rev/c192e77654315f37d151ece90a9037ec68cb69bb [^]
Related To BUG-44324:Performance Regression after
creating new index
**EM_OBPOSHV_P_DOCNO_ID index for column documentno,
fin_payment_id is added as index in Core module i.e
FIN_PAYMENT_DOCUMENTNO_ID for same columns is removed
Author: "atul.gaware" <atul.gaware <at> openbravo.com>
Date: Thu Jul 02 08:52:10 2020 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/de17990dba45ee7e8da935feee037018000abaa0 [^]
Fixes ISSUE-44324:Performance Regression after creating new index
**Remove index fin_payment_documentno_id based on columns
documentno and fin_payment_id
|Code Review + Testing Ok|
|2020-06-09 17:09||ALopetegui||New Issue|
|2020-06-09 17:09||ALopetegui||Assigned To||=> AtulOpenbravo|
|2020-06-09 17:09||ALopetegui||Modules||=> Core|
|2020-06-09 17:09||ALopetegui||Triggers an Emergency Pack||=> No|
|2020-06-10 09:23||ALopetegui||Relationship added||caused by 0043274|
|2020-06-10 09:24||ALopetegui||Tag Attached: Performance|
|2020-06-16 11:48||AtulOpenbravo||Status||new => scheduled|
|2020-07-01 17:47||dmiguelez||Note Added: 0121187|
|2020-07-02 08:58||hgbot||Note Added: 0121211|
|2020-07-02 08:58||hgbot||Note Added: 0121212|
|2020-07-02 08:58||hgbot||Status||scheduled => resolved|
|2020-07-02 08:58||hgbot||Resolution||open => fixed|
|2020-07-02 08:58||hgbot||Fixed in SCM revision||=> http://code.openbravo.com/erp/devel/pi/rev/de17990dba45ee7e8da935feee037018000abaa0 [^]|
|2020-07-02 08:59||dmiguelez||Review Assigned To||=> dmiguelez|
|2020-07-02 08:59||dmiguelez||Note Added: 0121213|
|2020-07-02 08:59||dmiguelez||Status||resolved => closed|
|2020-07-02 08:59||dmiguelez||Fixed in Version||=> 3.0PR20Q3|
|Copyright © 2000 - 2009 MantisBT Group|