cockroachdb / cockroachdb/cockroach

sql: generic query plans cannot use trigram inverted index for fuzzy similarity search

Open
#128,908 0 comments 0 reactions 0 assignees View on GitHub
A-generic-query-plans A-sql-optimizer C-performance O-qa T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.