babelfish-for-postgresql / babelfish_extensions / 27327181874

78%

BABEL_6_X_DEV: 78%

push

github

Spatial Index Support in the babelfish (#4797)

Currently, Babelfish rejects `CREATE SPATIAL INDEX` at the parser and spatial queries (`STIntersects`, `STContains`, `STDistance`, etc.) always fall back to sequential scans even when a GiST index exists on the column. T-SQL applications migrating from T-SQL experience linear latency growth as dataset size increases.

With this change, Babelfish now:
1. Accepts the full T-SQL `CREATE SPATIAL INDEX` syntax (including `USING GEOMETRY_GRID/GEOGRAPHY_GRID` and `WITH (BOUNDING_BOX, GRIDS, CELLS_PER_OBJECT)` options) and translates it to a PostGIS GiST index.
2. Rewrites spatial predicates at parse time in `tsqlIface.cpp` to inject the `&&` (bounding-box overlap) operator, enabling the PostgreSQL planner to use the GiST index.
3. Handles `STDistance(...) < N` patterns via `ST_Expand` for distance-based index acceleration.
4. Adds a GUC `babelfishpg_tsql.escape_hatch_spatial_index` (`strict`/`ignore`) to control whether T-SQL-specific tuning options raise an error or are silently stripped.

The rewrite is correctness-preserving: the original predicate is always retained as an exact recheck conjunct, so false positives from the bounding-box filter are always rejected. Performance improvement is 40x+ on selective spatial queries.

**How the code is organized:**
- Parser changes (`gram-tsql-decl.y`, `gram-tsql-rule.y`, `kwlist.h`, `scan-tsql-rule.l`) to accept `CREATE SPATIAL INDEX` syntax
- Query rewriting in `tsqlIface.cpp` to inject `&&` operator for 7 spatial predicates + STDistance
- `sys.spatial_indexes` view added to `sys_views.sql`
- Operator definitions for `&&` added to `geometry.sql` and `geography.sql`
- GUC registration in `guc.c`
- Unsupported feature handler updates in `tsqlUnsupportedFeatureHandler.cpp`

* **Use case based -** 
  - CREATE SPATIAL INDEX on geometry and geography columns with various syntax forms (bare, with USING clause, with full WITH options)
  - DROP INDEX for spatial indexes
  -... (continued)

225 of 252 new or added lines in 3 files covered. (89.29%)

54509 of 70583 relevant lines covered (77.23%)

400132.24 hits per line