Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0035205 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| feature request | [Openbravo ERP] A. Platform | minor | have not tried | 2017-02-08 16:43 | 2017-03-15 20:20 | |||
| Reporter | alostale | View Status | public | |||||
| Assigned To | vmromanos | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | 3.0PR17Q2 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | a6db92f321b6 | ||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | PostgreSQL | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Review Assigned To | alostale | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0035205: define partial indexes for not null values | |||||||
| Description | Review 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 Reproduce | See http://wiki.openbravo.com/wiki/Constraints_and_Triggers#Partial_indexes [^] | |||||||
| Tags | No tags attached. | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||
 Relationships |
|
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 (manager) 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 (manager) 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 (manager) 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 |
|
Notes |
|
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 |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |