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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0035205
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Openbravo ERP] A. Platformminorhave not tried2017-02-08 16:432017-03-15 20:20
ReporteralostaleView Statuspublic 
Assigned Tovmromanos 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q2
StatusclosedFix in branchFixed in SCM revisiona6db92f321b6
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toalostale
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035205: 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 [^]
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to feature request 00352043.0PR17Q2 closedinigosanchez Openbravo ERP define partial indexes for not null values 
depends on feature request 0032211 closedcaristu Openbravo ERP support for partial indexes 
related to feature request 0035206 closedmarvintm Openbravo ERP define partial indexes for not null values 
blocks defect 0038714 closedAtulOpenbravo Openbravo ERP Change to partial index m_movementline_c_aum index 
blocks defect 0038713 closedAtulOpenbravo Openbravo ERP Change to partial index c_invoiceline_account index 
blocks defect 0038719 closedAtulOpenbravo Modules Change to partial index rem_remittance_payment index 

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

- Issue History
Date Modified Username Field Change
2017-02-08 16:43 alostale New Issue
2017-02-08 16:43 alostale Assigned To => Triage Finance
2017-02-08 16:43 alostale Modules => Core
2017-02-08 16:43 alostale Triggers an Emergency Pack => No
2017-02-08 16:43 alostale Issue generated from 0035204
2017-02-08 16:43 alostale Relationship added related to 0035204
2017-02-08 16:44 alostale Relationship added depends on 0032211
2017-02-08 16:45 alostale Issue cloned 0035206
2017-02-08 16:45 alostale Relationship added related to 0035206
2017-02-23 18:17 vmromanos Status new => scheduled
2017-02-23 18:17 vmromanos Assigned To Triage Finance => vmromanos
2017-02-24 09:10 hgbot Checkin
2017-02-24 09:10 hgbot Note Added: 0094591
2017-02-24 09:10 hgbot Status scheduled => resolved
2017-02-24 09:10 hgbot Resolution open => fixed
2017-02-24 09:10 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a6db92f321b6a5441e81bd0e5696a4d0f5177436 [^]
2017-02-24 09:11 hgbot Checkin
2017-02-24 09:11 hgbot Note Added: 0094592
2017-02-24 09:19 vmromanos Note Added: 0094593
2017-02-24 09:51 vmromanos Review Assigned To => alostale
2017-02-24 11:51 hgbot Checkin
2017-02-24 11:51 hgbot Note Added: 0094607
2017-03-02 10:53 vmromanos Note Added: 0094680
2017-03-03 09:01 alostale Note Added: 0094712
2017-03-03 09:01 alostale Status resolved => closed
2017-03-03 09:01 alostale Fixed in Version => 3.0PR17Q2
2017-03-15 20:20 hudsonbot Checkin
2017-03-15 20:20 hudsonbot Note Added: 0095228
2017-03-15 20:20 hudsonbot Checkin
2017-03-15 20:20 hudsonbot Note Added: 0095229
2017-03-15 20:20 hudsonbot Checkin
2017-03-15 20:20 hudsonbot Note Added: 0095231
2018-06-07 16:28 Sandrahuguet Relationship added blocks 0038714
2018-06-07 16:28 Sandrahuguet Relationship added blocks 0038713
2018-06-07 16:29 Sandrahuguet Relationship added blocks 0038719


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker