opt: add geospatial inverted index recommendations #80934
Description
There should be geospatial inverted index recommendations for queries that use index accelerated geospatial functions:
cockroach/pkg/geo/geoindex/geoindex.go
Lines 36 to 51 in 6f85a0b
For example, the query below has no recommendation:
defaultdb> CREATE TABLE t (k INT PRIMARY KEY, g GEOMETRY);
defaultdb> EXPLAIN SELECT k FROM t WHERE st_covers(g, 'POINT(0 0 0 0)');
info
--------------------------------------------------------------------------------------------------------
distribution: local
vectorized: true
• filter
│ filter: st_covers(g, '01010000C00000000000000000000000000000000000000000000000000000000000000000')
│
└── • scan
missing stats
table: t@t_pkey
spans: FULL SCAN
(10 rows)
But if an index is added, a query plan using it is chosen because it is more efficient:
defaultdb> CREATE INVERTED INDEX ON t(g);
CREATE INDEX
defaultdb> EXPLAIN SELECT k FROM t WHERE st_covers(g, 'POINT(0 0 0 0)');
info
--------------------------------------------------------------------------------------------------------
distribution: local
vectorized: true
• filter
│ filter: st_covers(g, '01010000C00000000000000000000000000000000000000000000000000000000000000000')
│
└── • index join
│ table: t@t_pkey
│
└── • inverted filter
│ inverted column: g_inverted_key
│ num spans: 31
│
└── • scan
missing stats
table: t@t_g_idx
spans: 31 spans
(17 rows)
Jira issue: CRDB-15446
Epic: CRDB-14532