ES|QL: Add first class support for proper prefix/suffix queries

[felixbarny]

Today, ES|QL offers the STARTS_WITH and ENDS_WITH functions. For example, STARTS_WITH(first_name, "Eric") would match for the value Eric. A so-called "proper prefix" matches if the value starts with the given string but is not equal to it. For example, the proper prefix "Eric" would match for Erica but not for Eric. In regex terms, the difference is between Eric.* (prefix) and Eric.+ (proper prefix).

The use case I have in mind are PromQL queries where regex matching on labels is a common practice. To speed up the regex filters, we're translating regexes starting or ending with .* to STARTS_WITH/ ENDS_WITH functions. We also want to translate proper prefix/suffix regexes (.+), for label selectors like label=~"Eric.+". We can of course translate this into label != "Eric" AND STARTS_WITH(label, "Eric"), but it would be more elegant to have first class support for this. @costin mentioned that this could be a good general addition to ES|QL.

Before starting with the implementation, I wanted to get consensus on whether this is a desirable addition to ES|QL and how the syntax should look like. My suggestion is to add a STARTS_WITH_PROPER/ENDS_WITH_PROPER function, but we could also add an optional parameter to the existing functions, e.g. STARTS_WITH(label, "Eric", true), however that would be less readable in my opinion.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[luigidellaquila]

We have RLIKE operator for it:

FROM person
| WHERE first_name RLIKE "Eric.+"

Do you have something more complex in mind?

[felixbarny]

Is there an optimization for RLIKE that re-writes this to something equivalent to the following?

FROM person
| WHERE first_name != "Eric" AND STARTS_WITH(first_name, "Eric")

In the PromQL POC, it optimizes common special cases including prefix/suffix/exact match/IN that would otherwise be very slow to execute with an actual regex evaluation. Another approach would be to use RLIKE and add these optimizations there.

[luigidellaquila]

There is an optimization that pushed RLIKE down to Lucene, if possible (eg. if it's right after FROM)

[felixbarny]

I'm assuming that when pushing down an RLIKE, Lucene still needs to actually evaluate the regular expression and re-writing to a prefix/suffix/IN query where possible would be a lot faster. Is that accurate @martijnvg @romseygeek?

Some more examples of regexes that can be re-written to something more efficient: foo|bar, foo.*, .*foo, foo|bar.+, .*foo.*, foo.*bar.

[romseygeek]

I think if there's still a regular expression in there somewhere then rewriting to a term query + a regex query probably won't make that much of a difference. Intersecting the regex with the terms dictionary is still going to be by far the most expensive operation.

[felixbarny]

Yeah, this would just be an optimization for specific but common regexes and if would fall back to an actual regex if anything else is used.

This isn't anything that's urgent. We can keep it PromQL specific for now and come back to it later if and when we want to generalize or optimize RLIKE for these special cases.