Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0044324Openbravo ERPA. Platformpublic2020-06-09 17:092020-07-02 08:59
ReporterALopetegui 
Assigned ToAtulOpenbravo 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target VersionpiFixed in VersionPR20Q3 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0044324: Performance Regression after creating new index
DescriptionIn 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 ReproduceIn 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 SolutionThe 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.
Additional Information
TagsPerformance
Relationships
caused by defect 0043274 closed AtulOpenbravo Payment In/Out Window bad performance because documentno index is missing 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-06-09 17:09ALopeteguiNew Issue
2020-06-09 17:09ALopeteguiAssigned To => AtulOpenbravo
2020-06-09 17:09ALopeteguiModules => Core
2020-06-09 17:09ALopeteguiResolution time => 1592863200
2020-06-09 17:09ALopeteguiTriggers an Emergency Pack => No
2020-06-09 17:58dmiguelezResolution time1592863200 => 1593468000
2020-06-10 09:23ALopeteguiRelationship addedcaused by 0043274
2020-06-10 09:24ALopeteguiTag Attached: Performance
2020-06-16 11:48AtulOpenbravoStatusnew => scheduled
2020-07-01 17:47dmiguelezNote Added: 0121187
2020-07-02 08:58hgbotCheckin
2020-07-02 08:58hgbotNote Added: 0121211
2020-07-02 08:58hgbotCheckin
2020-07-02 08:58hgbotNote Added: 0121212
2020-07-02 08:58hgbotStatusscheduled => resolved
2020-07-02 08:58hgbotResolutionopen => fixed
2020-07-02 08:58hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/de17990dba45ee7e8da935feee037018000abaa0 [^]
2020-07-02 08:59dmiguelezReview Assigned To => dmiguelez
2020-07-02 08:59dmiguelezNote Added: 0121213
2020-07-02 08:59dmiguelezStatusresolved => closed
2020-07-02 08:59dmiguelezFixed in Version => 3.0PR20Q3

Notes
(0121187)
dmiguelez   
2020-07-01 17:47   
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.
(0121211)
hgbot   
2020-07-02 08:58   
Repository: erp/pmods/org.openbravo.retail.highvolumesconfig
Changeset: c192e77654315f37d151ece90a9037ec68cb69bb
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

---
M src-db/database/model/modifiedTables/FIN_PAYMENT.xml
---
(0121212)
hgbot   
2020-07-02 08:58   
Repository: erp/devel/pi
Changeset: de17990dba45ee7e8da935feee037018000abaa0
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

---
M src-db/database/model/tables/FIN_PAYMENT.xml
---
(0121213)
dmiguelez   
2020-07-02 08:59   
Code Review + Testing Ok