Open rishabhtanwar29 opened 1 week ago
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tsql/src/catalog.c | 6 | 84.49% | ||
<!-- | Total: | 6 | --> |
Totals | |
---|---|
Change from base Build 9596046248: | -0.01% |
Covered Lines: | 42351 |
Relevant Lines: | 57959 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 6 | --> |
Totals | |
---|---|
Change from base Build 9613188327: | -0.01% |
Covered Lines: | 42825 |
Relevant Lines: | 58507 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 6 | --> |
Totals | |
---|---|
Change from base Build 9613188327: | -0.02% |
Covered Lines: | 42823 |
Relevant Lines: | 58507 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tds/src/backend/tds/tdscomm.c | 3 | 73.33% | ||
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 9 | --> |
Totals | |
---|---|
Change from base Build 9647525510: | -0.02% |
Covered Lines: | 42820 |
Relevant Lines: | 58507 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tds/src/backend/tds/tdscomm.c | 3 | 73.33% | ||
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 9 | --> |
Totals | |
---|---|
Change from base Build 9647525510: | -0.02% |
Covered Lines: | 42820 |
Relevant Lines: | 58507 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 6 | --> |
Totals | |
---|---|
Change from base Build 9766489889: | -0.02% |
Covered Lines: | 42835 |
Relevant Lines: | 58532 |
Changes Missing Coverage | Covered Lines | Changed/Added Lines | % | ||
---|---|---|---|---|---|
contrib/babelfishpg_tsql/src/pltsql_ruleutils.c | 2 | 3 | 66.67% | ||
contrib/babelfishpg_tsql/src/hooks.c | 19 | 21 | 90.48% | ||
contrib/babelfishpg_tsql/src/catalog.c | 14 | 21 | 66.67% | ||
<!-- | Total: | 35 | 45 | 77.78% | --> |
Files with Coverage Reduction | New Missed Lines | % | ||
---|---|---|---|---|
contrib/babelfishpg_tsql/src/catalog.c | 6 | 85.51% | ||
<!-- | Total: | 6 | --> |
Totals | |
---|---|
Change from base Build 9766489889: | -0.02% |
Covered Lines: | 42835 |
Relevant Lines: | 58532 |
Description
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 inget_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:
Performance gain with current changes (b/w SN1 and SN2): 10.65% % perf loss with RECOMPILE (b/w SN2 and SN3): 2.2%
Task: BABEL-4976 Signed-off-by: Rishabh Tanwar ritanwar@amazon.com
Engine PR: https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish/pull/393
Test Scenarios Covered
Use case based -
Boundary conditions -
Arbitrary inputs -
Negative test cases -
Minor version upgrade tests -
Major version upgrade tests -
Performance tests -
Tooling impact -
Client tests -
Check List
By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.