babelfish-for-postgresql / babelfish_extensions / 9834285708

76%

BABEL_5_X_DEV: 77%

Build Type

push

github

Committed by web-flow

Commit Message

Optimise lookup for babelfish_function_ext catalog (#2677)

Extended catalog `sys.babelfish_function_ext` currently uses as its key the schema name plus the function signature. This dates back to the time when Babelfish did not yet block duplicate functions with different signatures, but that is blocked now.
When doing a lookup for this catalog, the function signature must first be constructed in `get_pltsql_function_signature_internal()`. This uses a GUC to ensure that non-system names are double-quoted (required for the lookup), which appears to be a relatively expensive operation.

Since Babelfish no longer supports function overloading, we can perform a lookup directly using the schema name + function name. This requires an updated primary key on the catalog as (funcname + nspname + funcsignature). With this, we now will first perform lookup with partial key (funcname + nspname) and return the tuple if there is a unique match which will save us from constructing function signature. In case of multiple matches, we will then perform the lookup using complete key (funcname + nspname + funcsignature).

This optimization improves the performance which was previously impacted by WITH RECOMPILE which was added as part of https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/2563 . It introduced a lookup into babelfish_function_ext catalog using get_bbf_function_tuple_from_proctuple which performs the signature-based lookup for every stored procedure call.

### Performance data
Below table shows the time taken (in ms) for 100000 executions of the a procedure,  randomly picking a procedure each time from 10000 existing procedures in the database:

| S.N. | Scenario                           | Sample 1 | Sample 2 | Sample 3  | Sample 4 | Sample 5 | Sample 6 | Average  |
| :--- | :----------------------- | :-------- | :-------- | :--------- | :-------- | :-------- | :-------- | :-------- |
| 1      | Without current changes | 3478... (continued)

Run Details

35 of 45 new or added lines in 3 files covered. (77.78%)

6 existing lines in 1 file now uncovered.

42835 of 58532 relevant lines covered (73.18%)

110501.41 hits per line