cockroachdb / cockroachdb/cockroach
sql: generic query plan cannot use geography inverted index for filtering
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We implemented `extractGeoFilterCondition` to generate the inverted expression representing an inverted filter condition over the given geospatial index, but this doesn't include the case where there're only placeholders in the filter expression, thus limit its usage in generic query plan.
This is similar to #128908.
https://github.com/cockroachdb/cockroach/blob/2d469fe04c50a8dabf8b6fa2f3f8dc64e9d90a08/pkg/sql/opt/invertedidx/geo.go#L625-L627
The reproduction steps:
```sql
DROP TABLE IF EXISTS geo_test CASCADE;
CREATE TABLE geo_test (
id INT PRIMARY KEY,
location GEOGRAPHY NOT NULL -- Use GEOGRAPHY for better index support
);
-- Insert test data (10,000 points for better statistics)
INSERT INTO geo_test (id, location)
SELECT
i,
ST_MakePoint((i % 100)::FLOAT / 10.0, (i / 100)::FLOAT / 10.0)::GEOGRAPHY
FROM generate_series(1, 10000) AS i;
-- Create the inverted index
CREATE INVERTED INDEX idx_geo_test_location ON geo_test(location);
-- Force statistics collection
ANALYZE geo_test;
-- Test 1: Direct query with literals - SHOULD USE THE INDEX
EXPLAIN ANALYZE
SELECT id
FROM geo_test
WHERE ST_DWithin(location, ST_MakePoint(5.0, 5.0)::GEOGRAPHY, 100); -- 100 meters
-- Test 2: Prepare a statement
PREPARE geo_query (FLOAT, FLOAT, FLOAT) AS
SELECT id
FROM geo_test
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::GEOGRAPHY, $3);
-- Execute with custom plan (default) - execute 5 times to ensure plan is cached
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
-- Show the custom plan - SHOULD USE idx_geo_test_location
EXPLAIN ANALYZE EXECUTE geo_query(5.0, 5.0, 100);
-- Test 3: Force generic plan - WILL NOT USE THE INDEX
SET plan_cache_mode = force_generic_plan;
-- Execute to trigger generic plan generation
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
EXECUTE geo_query(5.0, 5.0, 100);
-- Show the generic plan - WILL NOT USE idx_geo_test_location
EXPLAIN ANALYZE EXECUTE geo_query(5.0, 5.0, 100);
-- Test 4: Try to force index usage with hint - WILL ERROR
PREPARE geo_query_hint (FLOAT, FLOAT, FLOAT) AS
SELECT id
FROM geo_test@idx_geo_test_location
WHERE ST_DWithin(location, ST_MakePoint($1, $2)::GEOGRAPHY, $3);
-- ERROR: index "idx_geo_test_location" is inverted and cannot be used for this query
EXPLAIN ANALYZE EXECUTE geo_query_hint(5.0, 5.0, 100);
```
Jira issue: CRDB-58996
Contributor guide
Assessment
This issue has not been assessed yet.