babelfish-for-postgresql / babelfish_extensions / 28008163716 / 1
77%
BABEL_6_X_DEV: 77%

LAST BUILD BRANCH: BABEL_5_X_DEV
DEFAULT BRANCH: BABEL_6_X_DEV
Ran
Files 127
Run time 5s
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: 77.549% (+0.2%) from 77.333%
28008163716.1

push

github

web-flow 
Performance optimization for spatial (geometry/geography) functions (#4827)

#### What
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.

#### Why

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>

55660 of 71774 relevant lines covered (77.55%)

390542.91 hits per line

Source Files on job 28008163716.1