Hi team, we have a use case where we’d want to apply fuzzy matching on a string col before doing an aggregation. Our initial plan was to enable text index on the said col as well as include it in a star-tree index. However, we just come across this doc and it says “Using a text index in coexistence with other Pinot indexes is not supported.” Curious if this use case is not currently supported by Pinot? Or is there other approach you’d recommend?

for different query pattern, it will work, queries like aggregation over fuzzy matching is not supported in startree index.

what about fuzzy matching on startree dimension?

that will happen orthogonal. It’s just normal fuzzy matching to return matched docId, then aggregate on those docs, no startree-index is involved.

do you think if we will have perf gain if we add a mirror col of the string col and then use it for star-tree index. A query would be executed like: 1. Fuzzy match to find all matched strings; 2. Use those strings to get aggregations like

mirror_string_col IN (str0, str1…)

what’s the cardinality of

mirror_string_col

? In your case you may need inverted index which force the column to have a dictionary which we usually don’t put on text index column

it is an url col so cardinality would be high

then I would say just use fuzzy matching with your aggregation directly

sorry I meant we are going to have two cols:

url

and

mirror_url

.

url

has text index enabled while

mirror_url

with star-tree

I don’t see a way to speed up with index/ startree

you don’t need to build startree-index, as the rows to aggregate is low

Right, if the number of rows to aggregation is low( <

maxLeafRecords

), we should expect to get the same perf as startree-index.

correct, hence inverted index may help more