babelfish-for-postgresql / babelfish_extensions / 28022766750

77%

Build Type

push

github

Committed by web-flow

Commit Message

Performance optimization for spatial (geometry/geography) functions  (#4874)

Currently, Babelfish's spatial type wrappers (`STIntersects`, `STContains`, `STEquals`, `STDisjoint`, `STDistance`, `STArea`, `STNumPoints`, `STDimension`,`STIsClosed`, `MakeValid`, `STGeometryType`, `HasZ`, `HasM`, `Z`, `M`, the `=`/`<>` operators, `Parse`, `Point`, `STGeomFromText`, `STPointFromText`, `STLineFromText`,`STPolyFromText`, `STMPointFromText`, `STMPointFromWKB`, `STAsText`, and bpchar/varchar/binary/varbinary cast paths) are implemented as PL/pgSQL wrappers around PostGIS C functions. Each call pays the cost of PL/pgSQL interpretation, multiple deserializations, and full GEOS topology checks even when the answer is trivially derivable from the geometry header.

With this change, all 64 spatial wrappers are reimplemented in C with header-only fast paths, point-trivially-valid short-circuits, cached-bbox short-circuits for binary predicates, and a typecode→T-SQL-name lookup table for `STGeometryType`.  T-SQL semantics — including the `"The geometry/geography instance is not valid"` error contract and `NULL`-on-mismatched-SRID for `=` — are preserved and now have explicit JDBC regression tests.

Spatial workloads on Babelfish were measurably slower than raw PostGIS due to per-row PL/pgSQL overhead. Profiling showed:
- `HasZ` / `HasM` / `STIsClosed` were ~30× slower than necessary (full deserialize for what is effectively a header-bit read).
- Constructors and `STAsText` were paying PL/pgSQL `SELECT` overhead per row.
- Validity checks ran on every row even for inputs (Points) that are valid by construction.

This PR closes those gaps without changing observable behavior or breaking the  T-SQL contract.

Task: BABEL-6444
Signed-off-by: Gopal Verma <gopalgv@amazon.com>

Coverage Stats

775 of 801 new or added lines in 1 file covered. (96.75%)

2 existing lines in 1 file now uncovered.

55749 of 72107 relevant lines covered (77.31%)

595828.65 hits per line