Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0044324
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2020-06-09 17:092020-07-02 08:59
ReporterALopeteguiView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in VersionPR20Q3
StatusclosedFix in branchFixed in SCM revisionde17990dba45
ProjectionnoneETAnoneTarget Versionpi
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionpiSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0044324: 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.
TagsPerformance
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
caused by defect 0043274 closedAtulOpenbravo Payment In/Out Window bad performance because documentno index is missing 

-  Notes
(0121187)
dmiguelez (developer)
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 (developer)
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 (developer)
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 (developer)
2020-07-02 08:59

Code Review + Testing Ok

- Issue History
Date Modified Username Field Change
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 Resolution time => 1592863200
2020-06-09 17:09 ALopetegui Triggers an Emergency Pack => No
2020-06-09 17:58 dmiguelez Resolution time 1592863200 => 1593468000
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 Checkin
2020-07-02 08:58 hgbot Note Added: 0121211
2020-07-02 08:58 hgbot Checkin
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
Powered by Mantis Bugtracker