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

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0044342

Openbravo ERP

09. Financial management

public

2020-06-11 09:51

2020-06-24 13:41

Reporter

ALopetegui

Assigned To

AtulOpenbravo

Priority

high

Severity

major

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

PR20Q3

Merge Request Status

Review Assigned To

dmiguelez

OBNetwork customer

Web browser

Google Chrome

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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

Description

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

Open the Financial Account -> Reconciliation tab in a financial account without reconciliations and see the query launched in the database.

Proposed Solution

The 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

Additional Information