cockroachdb / cockroachdb/cockroach
sql: generic query plans cannot use trigram inverted index for fuzzy similarity search
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
In v24.1 we added the new GenerateTrigramSimilarityInvertedIndexScans rule to build plans that more efficiently accelerate fuzzy similarity search using a trigram inverted index. But this rule does not yet work with placeholders, so generic query plans cannot use trigram inverted indexes for fuzzy similarity search.
Here's a demonstration on v24.2.0-rc.1:
```sql
CREATE TABLE rs (r INT PRIMARY KEY, s STRING, INVERTED INDEX (s gin_trgm_ops));
-- can use the trigram inverted index
EXPLAIN SELECT * FROM rs WHERE s % 'abc';
SET plan_cache_mode = force_generic_plan;
PREPARE p AS SELECT * FROM rs WHERE s % $1;
-- generic query plan cannot use the trigram inverted index
EXPLAIN ANALYZE EXECUTE p ('abc');
-- it still cannot, even if we force it
PREPARE p2 AS SELECT * FROM rs@rs_s_idx WHERE s % $1;
EXPLAIN ANALYZE EXECUTE p2 ('abc');
-- surprisingly, it also cannot if we fall back to the old behavior
SET optimizer_use_trigram_similarity_optimization = off;
PREPARE p3 AS SELECT * FROM rs@rs_s_idx WHERE s % $1;
EXPLAIN ANALYZE EXECUTE p3 ('abc');
```
Jira issue: CRDB-41269
Contributor guide
Assessment
This issue has not been assessed yet.