Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035205Openbravo ERPA. Platformpublic2017-02-08 16:432017-03-15 20:20
Reporteralostale 
Assigned Tovmromanos 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q2 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035205: define partial indexes for not null values
DescriptionReview the indexes that should be set as partial for only null values.

Analyzed data in some actual customers: https://docs.google.com/spreadsheets/d/1KYvFTQgRrAUgjwUJj3L-LJQ_VYfiqO6XEcIb8Sq8j6k/edit#gid=109022771 [^]
Steps To ReproduceSee http://wiki.openbravo.com/wiki/Constraints_and_Triggers#Partial_indexes [^]
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00352043.0PR17Q2 closed inigosanchez Openbravo ERP define partial indexes for not null values 
depends on feature request 0032211 closed caristu Openbravo ERP support for partial indexes 
related to feature request 0035206 closed marvintm Openbravo ERP define partial indexes for not null values 
blocks defect 0038714 closed AtulOpenbravo Openbravo ERP Change to partial index m_movementline_c_aum index 
blocks defect 0038713 closed AtulOpenbravo Openbravo ERP Change to partial index c_invoiceline_account index 
blocks defect 0038719 closed AtulOpenbravo Modules Change to partial index rem_remittance_payment index 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-02-08 16:43alostaleNew Issue
2017-02-08 16:43alostaleAssigned To => Triage Finance
2017-02-08 16:43alostaleModules => Core
2017-02-08 16:43alostaleTriggers an Emergency Pack => No
2017-02-08 16:43alostaleIssue generated from0035204
2017-02-08 16:43alostaleRelationship addedrelated to 0035204
2017-02-08 16:44alostaleRelationship addeddepends on 0032211
2017-02-08 16:45alostaleIssue cloned0035206
2017-02-08 16:45alostaleRelationship addedrelated to 0035206
2017-02-23 18:17vmromanosStatusnew => scheduled
2017-02-23 18:17vmromanosAssigned ToTriage Finance => vmromanos
2017-02-24 09:10hgbotCheckin
2017-02-24 09:10hgbotNote Added: 0094591
2017-02-24 09:10hgbotStatusscheduled => resolved
2017-02-24 09:10hgbotResolutionopen => fixed
2017-02-24 09:10hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a6db92f321b6a5441e81bd0e5696a4d0f5177436 [^]
2017-02-24 09:11hgbotCheckin
2017-02-24 09:11hgbotNote Added: 0094592
2017-02-24 09:19vmromanosNote Added: 0094593
2017-02-24 09:51vmromanosReview Assigned To => alostale
2017-02-24 11:51hgbotCheckin
2017-02-24 11:51hgbotNote Added: 0094607
2017-03-02 10:53vmromanosNote Added: 0094680
2017-03-03 09:01alostaleNote Added: 0094712
2017-03-03 09:01alostaleStatusresolved => closed
2017-03-03 09:01alostaleFixed in Version => 3.0PR17Q2
2017-03-15 20:20hudsonbotCheckin
2017-03-15 20:20hudsonbotNote Added: 0095228
2017-03-15 20:20hudsonbotCheckin
2017-03-15 20:20hudsonbotNote Added: 0095229
2017-03-15 20:20hudsonbotCheckin
2017-03-15 20:20hudsonbotNote Added: 0095231
2018-06-07 16:28SandrahuguetRelationship addedblocks 0038714
2018-06-07 16:28SandrahuguetRelationship addedblocks 0038713
2018-06-07 16:29SandrahuguetRelationship addedblocks 0038719

Notes
(0094591)
hgbot   
2017-02-24 09:10   
Repository: erp/devel/pi
Changeset: a6db92f321b6a5441e81bd0e5696a4d0f5177436
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Feb 23 18:40:37 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a6db92f321b6a5441e81bd0e5696a4d0f5177436 [^]

Fixed issue 35205: Partial indexes functional stuff

The only performance drawback in transforming a normal index to a partial index is in the queries with a where clause filtering by a indexed column when this column is null.
In this case, and only when the ratio of null values is very low, the query will probably execute a seq. scan over the table instead of using the index, thus killing the performance.

To detect this scenario, we have "greped" the PI code for "is*null" string (case insensitve), and filtered the places where the candidate indexes appear. Then, we have individually analize each of the queries to detect whether we could safely transform the index to partial index based on the previous explanation.

The result can be found in https://docs.google.com/spreadsheets/d/1m5GZaaoEkYvyGdyxddtxxb3SeAhvtavfdq53iumOT7s [^] Functional Analysis tab.
Values with Candidate = Y are the ones to be transformed to partial indexes, Candidate = N means to keep the normal index, and TBCN is to create a normal index because it would help in performance.
When there is no query that uses this index inside an "IS NULL" condition, we have automatically set it to partial index as there shouldn't be any risk
For the rest of the candidates there are individual explanations which can be found as a note (in the Candidate column and/or in the places where this index might be used).

For future references, this is the command used to grep the PI code:
grep -n --color=always -riI "is[[:space:]]*null" --exclude \*.js --exclude-dir src-wad --exclude-dir build /home/openbravo/HG/erp/devel/pi | grep -vi case | more

---
M modules/org.openbravo.userinterface.selector/src-db/database/model/tables/OBUISEL_SELECTOR_TRL.xml
M src-db/database/model/tables/AD_USER.xml
M src-db/database/model/tables/A_AMORTIZATIONLINE.xml
M src-db/database/model/tables/A_ASSET.xml
M src-db/database/model/tables/C_ACCTSCHEMA_PROCESS.xml
M src-db/database/model/tables/C_BPARTNER.xml
M src-db/database/model/tables/C_CITY.xml
M src-db/database/model/tables/C_CONVERSION_RATE_DOCUMENT.xml
M src-db/database/model/tables/C_INVOICE.xml
M src-db/database/model/tables/C_INVOICELINE.xml
M src-db/database/model/tables/C_INVOICETAX_CASHVAT.xml
M src-db/database/model/tables/C_ORDER.xml
M src-db/database/model/tables/C_ORDERLINE.xml
M src-db/database/model/tables/C_POC_DOCTYPE_TEMPLATE.xml
M src-db/database/model/tables/C_POC_EMAILDEFINITION.xml
M src-db/database/model/tables/C_TAXREGISTER.xml
M src-db/database/model/tables/C_TAXREGISTERLINE.xml
M src-db/database/model/tables/C_TAXREGISTER_TYPE_LINES.xml
M src-db/database/model/tables/C_TAX_ZONE.xml
M src-db/database/model/tables/FIN_FINACC_TRANSACTION.xml
M src-db/database/model/tables/FIN_ORIG_PAYMENT_SCHEDULE.xml
M src-db/database/model/tables/FIN_ORIG_PAYM_SCHEDDETAIL.xml
M src-db/database/model/tables/FIN_PAYMENT_SCHEDULE.xml
M src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml
M src-db/database/model/tables/GL_JOURNAL.xml
M src-db/database/model/tables/MA_MAINTENANCE.xml
M src-db/database/model/tables/MA_MEASURE_GROUP.xml
M src-db/database/model/tables/MA_PROCESS.xml
M src-db/database/model/tables/MA_WEINCIDENCE.xml
M src-db/database/model/tables/MRP_RUN_PRODUCTIONLINE.xml
M src-db/database/model/tables/M_COSTADJUSTMENTLINE.xml
M src-db/database/model/tables/M_COSTING.xml
M src-db/database/model/tables/M_INOUT.xml
M src-db/database/model/tables/M_INOUTLINE.xml
M src-db/database/model/tables/M_LANDEDCOST.xml
M src-db/database/model/tables/M_LC_COST.xml
M src-db/database/model/tables/M_LC_MATCHED.xml
M src-db/database/model/tables/M_LC_RECEIPT.xml
M src-db/database/model/tables/M_LC_RECEIPTLINE_AMT.xml
M src-db/database/model/tables/M_LOCATOR.xml
M src-db/database/model/tables/M_MATCHPO.xml
M src-db/database/model/tables/M_PRODUCT.xml
M src-db/database/model/tables/M_RAPPEL_BPARTNER.xml
M src-db/database/model/tables/M_RAPPEL_INVOICE.xml
M src-db/database/model/tables/M_RAPPEL_PRODUCT.xml
M src-db/database/model/tables/M_RAPPEL_PRODUCTCATEGORY.xml
M src-db/database/model/tables/M_RAPPEL_SCALE.xml
M src-db/database/model/tables/M_RESERVATION.xml
M src-db/database/model/tables/M_RESERVATION_STOCK.xml
M src-db/database/model/tables/M_STOCK_AUX.xml
M src-db/database/model/tables/M_STOCK_VALUATION.xml
M src-db/database/model/tables/M_TRANSACTION.xml
M src-db/database/model/tables/M_TRANSACTION_COST.xml
M src-db/database/model/tables/M_WAREHOUSE_SHIPPER.xml
M src-db/database/model/tables/S_TIMEEXPENSELINE.xml
---
(0094592)
hgbot   
2017-02-24 09:11   
Repository: erp/devel/pi
Changeset: ca296b5b4dd655ba2ce812c752f371b0b8ae6629
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Feb 23 18:26:45 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/ca296b5b4dd655ba2ce812c752f371b0b8ae6629 [^]

Related to issue 35205: new normal index C_CONVERSION_RATE_DOCUMENT.C_INVOICE_ID
Added this new normal indexes because it has been detected to avoid seq. scan in several flows.
More info at https://docs.google.com/spreadsheets/d/1m5GZaaoEkYvyGdyxddtxxb3SeAhvtavfdq53iumOT7s [^]

---
M src-db/database/model/tables/C_CONVERSION_RATE_DOCUMENT.xml
---
(0094593)
vmromanos   
2017-02-24 09:19   
Tested in a high volume instance.
An smartbuild -Dlocal=no before the change took 130 seconds, including the change that transforms indexes to partial indexes took 211 seconds.

As it was expected it took more time to transform to partial indexes but the result is something acceptable.
(0094607)
hgbot   
2017-02-24 11:51   
Repository: erp/devel/pi
Changeset: ad4f5d64125d75eba37ee4512b0855fd27a421af
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Fri Feb 24 11:52:40 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/ad4f5d64125d75eba37ee4512b0855fd27a421af [^]

Related to issue 35205: reverted some partial indexes

Some indexes have been reverted to normal indexes because they belong to high volume tables and the benefit to transform them to partial index is very reduced (if any) because most of the values will usually be not null.
Besides, transforming them to partial index would create a big delay in the update database process.

---
M src-db/database/model/tables/A_AMORTIZATIONLINE.xml
M src-db/database/model/tables/C_INVOICELINE.xml
M src-db/database/model/tables/C_INVOICETAX_CASHVAT.xml
M src-db/database/model/tables/C_ORDERLINE.xml
M src-db/database/model/tables/C_TAX_ZONE.xml
M src-db/database/model/tables/FIN_FINACC_TRANSACTION.xml
M src-db/database/model/tables/M_INOUTLINE.xml
M src-db/database/model/tables/M_LANDEDCOST.xml
---
(0094680)
vmromanos   
2017-03-02 10:53   
In a real customer environment with high volume, the smartbuild -Dlocal=no took 18 minutes more to rebuild the indexes, which is something acceptable.
(0094712)
alostale   
2017-03-03 09:01   
Reviewed, those indexes make sense. Even update took 18min it was performed in a single thread, so real expected time should be much lower.
(0095228)
hudsonbot   
2017-03-15 20:20   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/54e102bef53e [^]
Maturity status: Test
(0095229)
hudsonbot   
2017-03-15 20:20   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/54e102bef53e [^]
Maturity status: Test
(0095231)
hudsonbot   
2017-03-15 20:20   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/54e102bef53e [^]
Maturity status: Test