Projects:DB Support Indexes With Functions
The current dbsourcemanager needs to be extended to support db index definitions which include basic sql functions such as UPPER.
There is a need for such function indexes because in our search queries from Web POS we use the UPPER function to do case insensitive searching.
As our current indexes do not have the UPPER function they are not being used by these search queries. In high volume environments we need to support/use indexes to obtain good search user experience.
Here are some examples of indexes with the UPPER function:
CREATE INDEX c_bpartner_value_upper ON c_bpartner USING btree (upper(value) COLLATE pg_catalog."default" varchar_pattern_ops); CREATE INDEX c_bpartner_name_upper ON c_bpartner USING btree (upper(name) COLLATE pg_catalog."default" varchar_pattern_ops); CREATE INDEX c_bpartner_referenceno_upper ON c_bpartner USING btree (upper(referenceno) COLLATE pg_catalog."default" varchar_pattern_ops)
This project aims to extend dbsourcemanager so that it can import/export indexes with functions.
For now we only need to support functions which are valid for both Postgres as well as Oracle.
Some restrictions apply to the function based indexes:
- Multicolumn indexes are still supported, but only one of the index columns can use a function
Tracking issue: https://issues.openbravo.com/view.php?id=30179
Consolidation of the use of UPPER in queries
Currently the Openbravo codebase uses both UPPER and LOWER to achieve case insensitive filters. We should only use one of those, so that all of them can take advantage of function based indexes without the need of defining indexes that use both UPPER and LOWER.
UPPER is used much more frequently throughout the Openbravo codebase, so the LOWER instances that can take advantage of using indexes will be replaced with UPPER. This link contains the list of classes that use LOWER to build case insensitive filters. All of them will be replaced with UPPER.
This is the list of Java, SQL and XML files where LOWER is used. The current approach is not to replace the LOWER occurrence if the query cannot benefit from using an index (i.e. because the queried table is inherently small or because the operator used is iContains). The reason why not to change them all is to minimize the regression risk and also the impact of the change for those clients who have defined themselves indexes that use the lower function.
The changes done to consolidate the use of UPPER will be associated with this feature request
Note that these changes can be somewhat of an API change, since if someone had defined function based indexes using the lower function, those indexes will no longer be used. The API change has been reported here.
What to do with current OB indexes
There are several options:
- Leave them as they are. We would not be able to take advantage of function based indexes when filtering using the iStartsWith operator. It is also true that there are only a few places where we use the iStartsWith operator, for instance in the Assets and Warehouse selectors.
- Replace them (all those that make sense, i.e. those that defined just for one text column which is not an ID) with its upper function based versions. We could take advantage of the new performance improval, but removing the current non function based indexes could make the performance of some queries worse. For instance, it seems that if a query is done to sort a table using a column, the query will only take advantage of basic indexes defined on that column. Also, the first build could potentially be very slow, as the creation of indexes can take a lot of time in environments with high volumes.
- Add the function based indexes on top of the current ones. We would avoid making some queries slower, and we could also take advantage of the performance improval when using the iStartsWithOperator. The disadvantages would be that the first build could also potentially take a lot, and that defining two indexes for the same column would result in more consumed disk space and would make insertions and updates on that table a little slower.
The instances that would benefit the most of the new function based indexes would be those that define grid configuration to use the iStartsWith operator to filter the grid. We could consider defining the function based indexes in a separate module, to be installed in those environments that define the grid configuration. We still have not found a way to remove indexes using modularity, so the function based indexes would be added on top of the current ones.