sql: trigram inverted index acceleration for % operator doesn't work if indexed column is VARCHAR #85106
Description
For some reason, if a text column is VARCHAR, a trigram inverted index on it is not usable for acceleration of the % operator. The LIKE operator does not suffer from this same issue, and I'm not sure why.
Repro:
demo@127.0.0.1:26257/defaultdb> create table a (a varchar);
demo@127.0.0.1:26257/defaultdb> create index on a using gin(a gin_trgm_ops);
demo@127.0.0.1:26257/defaultdb> explain select * from a where a % '%foobar%';
info
------------------------------------
distribution: local
vectorized: true
• filter
│ filter: a::STRING % '%foobar%' -- Notice the a::STRING
│
└── • scan
missing stats
table: a@a_pkey
spans: FULL SCAN
(10 rows)
demo@127.0.0.1:26257/defaultdb> explain select * from a@a_a_idx where a % '%foobar%'; ERROR: index "a_a_idx" is inverted and cannot be used for this query
SQLSTATE: 42809
Time: 1ms total (execution 1ms / network 0ms)
Notice that in the LIKE case, there is no spurious a::STRING in the filter like we see in the above case, which I think is the reason this is happening but I'm not sure why:
Time: 103ms total (execution 103ms / network 0ms)
demo@127.0.0.1:26257/defaultdb> explain select * from a@a_a_idx where a like '%foobar%';
info
---------------------------------------------
distribution: local
vectorized: true
• filter
│ filter: a LIKE '%foobar%'
│
└── • index join
│ table: a@a_pkey
│
└── • inverted filter
│ inverted column: a_inverted_key
│ num spans: 4
│
└── • scan
missing stats
table: a@a_a_idx
spans: 4 spans
(17 rows)
Jira issue: CRDB-18041
Metadata
Metadata
Assignees
Labels
Type
Projects
Status