View source | View content page | Page history | Printable version   

Projects:DB Support Indexes With Functions

Contents

Introduction

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:

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:

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.

Documentation

Retrieved from "http://wiki.openbravo.com/wiki/Projects:DB_Support_Indexes_With_Functions"

This page has been accessed 1,718 times. This page was last modified on 8 September 2015, at 15:44. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.