Inefficiency when no field is specified for filtering (Specifically, FullTextFieldTransformation when doing schema_transformer)

[hsuanyi]

On kibana, people tend to not specify field when querying. For example,
instead of doing field:XXX, users just type XXX.

I get why quesma attempts to match it with all fields. However it is inefficient. esp when, many fields are low cardinality .Do you plan to do some field inference? I have 2 ideas:

• in configuration at startup time, allow specifying regex. and if the right hand side (XXX in this example) does not matcg regex. Do not add it to WHERE clause.
• periodically, do SELECT distinct for the low cardinality fields (either specified in ch table comment or in conf.) and just match that.

[jakozaur]

Elasticsearch model has two types of families, keyword and text, that specify this behaviour. By default, all text fields are searched when you search XXX for contains. The challenge is that if you configure SQL, you just have to type string and you need to guess whether it should be treated as keyword or text. The Elasticsearch defaults to text, same with Quesma.

Currently, there is a configuration that allows you to override that on the Quesma side, see YAML schemaOverride and type:
https://docs.quesma.com/config-primer.html

I'm happy to improve our documentation or add more features there, like the pattern you described. Also, using table comments seems like a good solution. Any thoughts on what would work for you the best? Definitely, we need to expand in this area.

On the other hand, some automation that would either match against columns based on cardinality sounds very tricky and may confuse. Prefer not to go that route.

[hsuanyi]

It is a different issue. The inefficiency is about the attempt to match the query to every single string column in clickhouse.
Let's assume in clickhouse table you have 10 columns. They are all string type.

{
  col_0: string,
  col_1: string,
  ...
  col_9: string,
}

Now let's assume query is "xyz" (notice that user does not specify which column. It is their habit... ). The current FullTextFieldTransformation would just expand all columns in WHERE clause

{
 col_0 like '%xyz%'
  OR
 col_1 like '%xyz%'
  OR
 col_2 like '%xyz%'
  ...
  OR
 col_9 like '%xyz%'
  ...
}

by the query "xyz" itself we should know some columns do not need to be filtered on (in fact, most likely, users only want to filter on log_message columns and not label columns such as "pod_name", "level", ). For example, log_level column should not contain such a word. Essentially, i am thinking if we can enhance "field inference" from the search word.

[jakozaur]

Thanks for explaining. It sounds interesting, though we would need to think about how to implement it properly. I wonder whether it should be a database feature or on the proxy side.

[jakozaur]

Any chance @hsuanyi, you have some test data with a real use case where we would see a significant benefit from this optimisation?

[hsuanyi]

I had very simple experiment.

testing query: "test"
schema is:
{
time,
message (indexed with bf_token),
low_cardinality_field1,
low_cardinality_field2,
}

Base side: the current code. so where clause is
WHERE message like '%test%' OR low_cardinality_field1 like '%test%' OR low_cardinality_field2 like '%test%'
Test side: i just hacked the code so quesma only filter by message:
WHERE message like '%test%'

test data set is 1b of random data.

Test side is roughly 10% faster. peak memory is close though.

[hsuanyi]

Like to give updates. This optimization can give significant improvement for if the message field has bf token index. Roughly > 3X. I think it does have great potential.

The challenge is how do we infer? (in my testing case, i use regex)