Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0043083Retail ModulesWeb POSpublic2020-01-31 12:072020-02-14 19:26
ReporterALopetegui 
Assigned Tomarkmm82 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target VersionFixed in VersionRR20Q2 
Merge Request Status
Review Assigned Toguilleaer
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0043083: Webpos Login, Initial validations makes inefficient query to application payments
DescriptionThe initial validation in webpos makes a bad performance query to applications payments, doing a sequential scan on obretco_cmevents for each payment.

with high concurrency and many obretco_cmevents, the read in memory/disk is very high.

This is the explain plan:
https://explain.depesz.com/s/ddMBe [^]
Steps To ReproduceLogin in the webpos in a terminal with many payments and cash management Events.
The query launched is the folloging:

select obpos_app_0_.* from OBPOS_APP_PAYMENT obpos_app_0_ where obpos_app_0_.Obpos_Applications_ID='XX' and (obpos_app_0_.FIN_Financial_Account_ID is not null) and (exists (select 1 from OBRETCO_CMEvents obretco_ca1_ where obretco_ca1_.FIN_Financial_Account_ID=obpos_app_0_.FIN_Financial_Account_ID)) and (obpos_app_0_.AD_Org_ID in ('0' , 'XX')) and (obpos_app_0_.AD_Client_ID in ('XX' , '0')) and obpos_app_0_.Isactive='Y'
Proposed SolutionCreate an index in obretco_cmevents for the fin_financial_account_id in order to evict the seq scan.

CREATE INDEX <index-name> ON obretco_cmevents (fin_financial_account_id)

The explain plan after the index:
https://explain.depesz.com/s/pUb8b [^]
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-01-31 12:07ALopeteguiNew Issue
2020-01-31 12:07ALopeteguiAssigned To => Retail
2020-01-31 12:07ALopeteguiTriggers an Emergency Pack => No
2020-02-06 10:36ALopeteguiTag Attached: Performance
2020-02-12 16:05markmm82Assigned ToRetail => markmm82
2020-02-12 16:05markmm82Statusnew => scheduled
2020-02-13 15:27hgbotCheckin
2020-02-13 15:27hgbotNote Added: 0117740
2020-02-13 15:27hgbotStatusscheduled => resolved
2020-02-13 15:27hgbotResolutionopen => fixed
2020-02-13 15:27hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/0f7b04ab50a23efa99076a39e3573b709acf1495 [^]
2020-02-14 19:26guilleaerReview Assigned To => guilleaer
2020-02-14 19:26guilleaerStatusresolved => closed
2020-02-14 19:26guilleaerFixed in Version => RR20Q2

Notes
(0117740)
hgbot   
2020-02-13 15:27   
Repository: erp/pmods/org.openbravo.retail.config
Changeset: 0f7b04ab50a23efa99076a39e3573b709acf1495
Author: Mark Molina <mark.molina <at> doceleguas.com>
Date: Wed Feb 12 14:15:34 2020 -0300
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/0f7b04ab50a23efa99076a39e3573b709acf1495 [^]

Fixes issue 43083: Improved performance in query on Webpos Login

The initial validation in webpos was making a bad performance query to applications
payments, doing a sequential scan on obretco_cmevents for each payment.

To fix that, was added a new index on the FIN_FINANCIAL_ACCOUNT_ID column of OBRETCO_CMEVENTS
table to improves the execution of queries.

---
M src-db/database/model/tables/OBRETCO_CMEVENTS.xml
---