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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0044342
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2020-06-11 09:512020-06-24 13:41
ReporterALopeteguiView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in VersionPR20Q3
StatusclosedFix in branchFixed in SCM revision96e85683b865
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Todmiguelez
Web browserGoogle Chrome
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0044342: Created index for Financial account window performance does not work efficiently if there are no reconciliations

DescriptionIn the issue 0042426, a new index was created because of the auxiliary inputs for LASTRECON and APRM_LASTREC. The query is this one:
select fin_reconciliation_id from fin_reconciliation where
created = (select max(created) from fin_reconciliation where fin_financial_account_id = "$1")

And the index created is this one:
 "fin_reconcile_created_finacc" btree (created, fin_financial_account_id)

This index works efficiently if the financial account have at least one reconciliation, because the index starts filtering for the second column of the index and when finds the fin_financial_account_id, gets the max created immediately. it needs very few buffers hit: 63
Here explain plan: https://explain.depesz.com/s/4ILN [^]

However, if the financial account does not have any reconciliation (the query returns 0 rows), the index does not work efficiently because again it starts filtering for the second column fin_financial_account_id and as it does not match any case it needs to search in all the fin_financial_account_id till realize that there are no values, in this case it needs to hit much more shared hits: 93794
Here the explain plan: https://explain.depesz.com/s/PoQU [^]
Steps To ReproduceOpen the Financial Account -> Reconciliation tab in a financial account without reconciliations and see the query launched in the database.
Proposed SolutionThe proposed solution is to invert the order of the index, putting fin_financial_account_id column in first position and created column in the second position. "fin_reconcile_finacc_created" btree (fin_financial_account_id, created)
In that way, The filter condition is in the first part of the index so it can detect immediately if there is no rows.
With this index the query works properly in both cases.
If exists: 10 hit -> https://explain.depesz.com/s/GWFo [^]
If not exists: 4 hit -> https://explain.depesz.com/s/JDyR [^]

As already exists the single index "fin_reconcile_finaccount" for fin_financial_account_id, this index should be deleted/replaced with the new index.

with the new index we lose the index for the column created, for the purpose of this query and issue is not necessary to have an index for created, but if there is some other query using this index it could maintain the index but only for created.


So in summaty fin_reconciliation table should have these indexex:

Indexes:
    "fin_reconciliation_key" PRIMARY KEY, btree (fin_reconciliation_id)
    "fin_reconcile_finacc_created" btree (fin_financial_account_id, created)
    "fin_reconcile_created" btree (created) --OPTIONAL IF IT IS NEEDED FOR OTHER QUERIER
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0042426 closedcollazoandy4 Financial account window performance for LASTRECON and APRM_LASTREC auxiliary inputs 

-  Notes
(0121026)
AtulOpenbravo (developer)
2020-06-24 13:00

 In an high volume environment, improvement can be seen as below

explain (BUFFERS, ANALYZE) select fin_reconciliation_id
from fin_reconciliation
where created = (select max(created)
from fin_reconciliation
where fin_financial_account_id = '0875AFBD636C4DB288DBEA9EF5DF14B1')

Query plan before change:-

Index Scan using fin_reconcile_created_finacc on fin_reconciliation (cost=380.71..382.93 rows=1 width=33) (actual time=10.153..10.154 rows=1 loops=1)
Index Cond: (created = $1)
Buffers: shared hit=46 read=1085
InitPlan 1 (returns $1)
-> Aggregate (cost=380.14..380.15 rows=1 width=8) (actual time=8.622..8.622 rows=1 loops=1)
Buffers: shared hit=45 read=1081
-> Index Scan using fin_reconcile_finaccount on fin_reconciliation fin_reconciliation_1 (cost=0.56..379.30 rows=336 width=8) (actual time=0.021..8.483 rows=1115 loops=1)
Index Cond: ((fin_financial_account_id)::text = '0875AFBD636C4DB288DBEA9EF5DF14B1'::text)
Buffers: shared hit=45 read=1081
Planning Time: 0.136 ms
Execution Time: 10.181 ms

Query plan after the change:-

Index Scan using fin_reconcile_created on fin_reconciliation (cost=2.13..4.35 rows=1 width=33) (actual time=0.036..0.036 rows=1 loops=1)
Index Cond: (created = $1)
Buffers: shared hit=9
InitPlan 2 (returns $1)
-> Result (cost=1.69..1.70 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1)
Buffers: shared hit=5
InitPlan 1 (returns $0)
-> Limit (cost=0.56..1.69 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
Buffers: shared hit=5
-> Index Only Scan Backward using fin_reconcile_finacc_created on fin_reconciliation fin_reconciliation_1 (cost=0.56..380.17 rows=336 width=8) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: ((fin_financial_account_id = '0875AFBD636C4DB288DBEA9EF5DF14B1'::text) AND (created IS NOT NULL))
Heap Fetches: 1
Buffers: shared hit=5
Planning Time: 0.134 ms
Execution Time: 0.057 ms
(0121028)
hgbot (developer)
2020-06-24 13:34

 Repository: erp/devel/pi
Changeset: 96e85683b865ac07628c417da4726031391a65b1
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Sat Jun 20 21:28:47 2020 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/96e85683b865ac07628c417da4726031391a65b1 [^]

Fixes BUG-44342:Created index for Financial account
window performance does not work efficiently if there
are no reconciliations

**Added index on column FIN_FINANCIAL_ACCOUNT_ID, CREATED in
same order instead of order CREATED, FIN_FINANCIAL_ACCOUNT_ID
**Added index on column CREATED

---
M src-db/database/model/tables/FIN_RECONCILIATION.xml
---
(0121030)
dmiguelez (developer)
2020-06-24 13:41

 Code Review + Testing Ok

- Issue History
Date Modified Username Field Change
2020-06-11 09:51 ALopetegui New Issue
2020-06-11 09:51 ALopetegui Assigned To => Triage Finance
2020-06-11 09:51 ALopetegui Web browser => Google Chrome
2020-06-11 09:51 ALopetegui Modules => Core
2020-06-11 09:51 ALopetegui Triggers an Emergency Pack => No
2020-06-11 09:54 ALopetegui Tag Attached: Performance
2020-06-11 09:54 ALopetegui Relationship added related to 0042426
2020-06-11 11:00 dmiguelez Web browser Google Chrome => Google Chrome
2020-06-11 11:00 dmiguelez Resolution time => 1593640800
2020-06-16 15:12 AtulOpenbravo Status new => scheduled
2020-06-18 10:12 dmiguelez Assigned To Triage Finance => AtulOpenbravo
2020-06-24 13:00 AtulOpenbravo Note Added: 0121026
2020-06-24 13:34 hgbot Checkin
2020-06-24 13:34 hgbot Note Added: 0121028
2020-06-24 13:34 hgbot Status scheduled => resolved
2020-06-24 13:34 hgbot Resolution open => fixed
2020-06-24 13:34 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/96e85683b865ac07628c417da4726031391a65b1 [^]
2020-06-24 13:41 dmiguelez Review Assigned To => dmiguelez
2020-06-24 13:41 dmiguelez Note Added: 0121030
2020-06-24 13:41 dmiguelez Status resolved => closed
2020-06-24 13:41 dmiguelez Fixed in Version => 3.0PR20Q3

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker