0043083: Webpos Login, Initial validations makes inefficient query to application payments

Description

The 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 Reproduce

Login 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 Solution

Create 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 [^]

Tags

Performance

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0117740) hgbot (developer) 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 ---

Issue History
Date Modified	Username	Field	Change
2020-01-31 12:07	ALopetegui	New Issue
2020-01-31 12:07	ALopetegui	Assigned To	=> Retail
2020-01-31 12:07	ALopetegui	Triggers an Emergency Pack	=> No
2020-02-06 10:36	ALopetegui	Tag Attached: Performance
2020-02-12 16:05	markmm82	Assigned To	Retail => markmm82
2020-02-12 16:05	markmm82	Status	new => scheduled
2020-02-13 15:27	hgbot	Checkin
2020-02-13 15:27	hgbot	Note Added: 0117740
2020-02-13 15:27	hgbot	Status	scheduled => resolved
2020-02-13 15:27	hgbot	Resolution	open => fixed
2020-02-13 15:27	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.retail.config/rev/0f7b04ab50a23efa99076a39e3573b709acf1495 [^]
2020-02-14 19:26	guilleaer	Review Assigned To	=> guilleaer
2020-02-14 19:26	guilleaer	Status	resolved => closed
2020-02-14 19:26	guilleaer	Fixed in Version	=> RR20Q2