babelfish-for-postgresql / babelfish_extensions / 9834285708 / 1
76%
BABEL_5_X_DEV: 77%

LAST BUILD BRANCH: babel-5844-5x
DEFAULT BRANCH: BABEL_5_X_DEV
Ran
Files 114
Run time 3s
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst
coverage: 73.182% (-0.02%) from 73.197%
9834285708.1

push

github

web-flow 
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)

42835 of 58532 relevant lines covered (73.18%)

110501.41 hits per line

Source Files on job 9834285708.1