ES|QL runtime search

Runtime search lets ES|QL search values that have no index behind them, such as a column computed by EVAL, a column of a federated dataset, or vectors generated by DENSE_VECTOR. Instead of looking values up in an index, ES|QL evaluates the search against each row as the query runs.

Runtime search is useful to:

  • Search federated datasets, which have no index.
  • Search a column that combines several fields.
  • Search after a command that changes the rows, such as STATS or LIMIT.
  • Search a field that is not mapped, or not mapped the same way, in every index the query reads.
  • Search vectors generated as the query runs by the DENSE_VECTOR command.

MATCH, the match operator :, and MATCH_PHRASE use runtime search automatically when their target has no inverted index. Other search functions, such as QSTR and KQL, need an indexed field.

KNN uses runtime search on dense_vector expressions.

This query searches a column that combines the title and description of each book:

FROM books
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien")

Runtime search can behave differently from a search on an indexed field. It is usually slower, because every row that reaches it is evaluated instead of looked up in an index. Matching and scoring can also differ: text values use the standard analyzer unless you declare another, relevance scores aren't BM25, and KNN runs an exact search instead of an approximate one.

ES|QL automatically uses runtime search in the following cases. You can't control this behavior, and no warning is issued when it occurs.

  • A column of a federated dataset.
  • An expression rather than a field, either a computed column, such as one created by EVAL or STATS, or a function call written directly as the target, like MATCH(TO_TEXT(CONCAT(title, " ", description)), "Tolkien"). This includes FIELD_EXTRACT on a flattened field. A STATS grouping key is not an expression: it keeps the identity of the field it groups by, so searching it after STATS is still a search on an indexed field.
  • A dense_vector expression, such as the embeddings that DENSE_VECTOR generates or a value converted with TO_DENSE_VECTOR. In 9.6.0, KNN on an inline TO_DENSE_VECTOR of a field returns no rows or fails (#163402). Convert the field with EVAL first, and search the resulting column.
  • A field converted to text or keyword when it is not already of that type, for example TO_TEXT on a keyword field. A conversion that does not change the type, like TO_TEXT on a text field, still uses the index.
  • A column expanded by MV_EXPAND.
  • A field that is mapped to different types across the indices the query reads.
  • A field that is unmapped in some or all of the indices the query reads, and that SET unmapped_fields="load" loads from _source.
  • A column output by FORK when a branch contains a command that changes the rows, such as LIMIT or STATS. When no branch does, the search runs inside each branch, where a search on an indexed field uses the index.

A runtime search can appear anywhere in the query, including after STATS, LIMIT, and FORK. The position restriction on searches of indexed fields doesn't apply to it.

KNN on an expression is the exception: it needs a LIMIT after it. Refer to KNN on dense_vector expressions.

This query searches a computed column after LIMIT:

FROM books
| SORT book_no
| LIMIT 10
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien")

The restriction is lifted for each search, not for the whole WHERE command. A search on an indexed field in the same WHERE command is still rejected.

Full-text search on expressions

A runtime search with MATCH, the : operator, or MATCH_PHRASE analyzes and matches values the same way as a search on an indexed field, using the analyzers described in Analyzers.

Options such as operator and fuzziness behave the same way as on an indexed field.

Runtime search in 9.5
  • Only MATCH and the : operator support it, not MATCH_PHRASE.
  • It is subject to the same position restriction as a search on an indexed field.
  • It does not support options, and always analyzes text with the standard analyzer.
  • It does not contribute to _score.

MATCH and the : operator can search expressions of these types:

  • text: the values and the query are analyzed, as for an indexed text field.
  • keyword: the query must equal a whole value exactly, as for an indexed keyword field.
  • boolean, date, date_nanos, double, integer, long, unsigned_long, ip and version: the query must equal a value. The query must be convertible to the type of the expression.

MATCH_PHRASE can search text and keyword expressions. On a keyword expression the whole query must equal a value exactly, as for an indexed keyword field.

Tip

String functions such as CONCAT return keyword, so searching their output requires an exact match of the whole value. To search it as full text, convert it with TO_TEXT, as in the examples on this page.

For a multivalued text column, query terms can match across different values in the same row. Phrase matching uses a position gap between values, so a phrase does not cross value boundaries with the default slop. A sufficiently large slop can allow a cross-value match.

A runtime search on a text expression uses two analyzers, like a search on an indexed text field:

  • The values analyzer analyzes the values being searched. It plays the role of the analyzer in the mapping of an indexed field. Declare it with the analyzer option of TO_TEXT where the column is created. It defaults to standard.
  • The query analyzer analyzes the query string. Set it with the analyzer option of MATCH or MATCH_PHRASE. It defaults to the values analyzer.

For example, with the english analyzer declared on content, a search for ring also matches books whose title or description contains rings, since both stem to ring:

FROM books
| EVAL content = TO_TEXT(CONCAT(title, " ", description), {"analyzer": "english"})
| WHERE MATCH(content, "ring")

Both options take the name of a built-in analyzer or one provided by a plugin. Custom analyzers defined in an index's settings are not available, since the expression does not belong to an index.

TO_TEXT rejects the analyzer option on a field mapped in the index, since declaring it would force a search of that field to run at runtime instead of using the index. Search the field directly to use the analyzer from its mapping.

An indexed text field is sometimes searched at runtime, such as after MV_EXPAND expands it, or when it is not mapped the same way across every index the query reads. Its values are then analyzed with the values analyzer of the column, which is standard unless TO_TEXT declares another, not with the analyzer from its mapping. A search that can use the field's index uses the analyzer from its mapping.

Warning

This substitution is silent. A field whose mapping declares an analyzer other than standard is searched differently depending on where in the query it is searched, and the query succeeds while matching a different set of rows.

Which column MV_EXPAND expanded decides how a field is searched. Both of these queries expand author, which a book can have several of, but only the first searches the expanded column, and so runs at runtime:

FROM books
| MV_EXPAND author
| WHERE MATCH(author, "Tolkien")

The second searches an indexed field instead, which is still subject to the position restriction and is rejected after MV_EXPAND:

FROM books
| MV_EXPAND author
| WHERE MATCH(title, "Tolkien")

To search the expanded field with the analyzer from its mapping, either search it before MV_EXPAND, or declare the analyzer again with TO_TEXT afterward, which is allowed once the column is no longer an indexed field:

FROM books
| MV_EXPAND author
| EVAL a = TO_TEXT(author, {"analyzer": "whitespace"})
| WHERE MATCH(a, "Tolkien")

A field that is mapped differently across the indices the query reads has no such remedy: TO_TEXT rejects the analyzer option on it, because it is still a field from an index mapping. Search it only where standard gives the results you need, or map it the same way in every index.

A column output by FORK is not index-backed either, but a mapped text field in it is rejected rather than searched with a substituted analyzer. Declare the analyzer on the merged column to search it, which is allowed because the merged column is no longer an indexed field, or search the field inside the FORK branches instead:

FROM books
| FORK (SORT book_no | LIMIT 5)
       (SORT book_no DESC | LIMIT 5)
| EVAL t = TO_TEXT(title, {"analyzer": "whitespace"})
| WHERE MATCH(t, "Tolkien")

FORK branches that declare different values analyzers for the same column are rejected too, since the merged column can only have one.

On text expressions, MATCH and MATCH_PHRASE support the same function named parameters as on an indexed field. On other types, MATCH supports only lenient, and MATCH_PHRASE supports none. When the query can't be converted to the type of the expression, lenient makes the search match no rows instead of failing.

When the query requests METADATA _score, a runtime full-text search contributes to the relevance score. There are no index statistics for an expression, so the score is not BM25:

  • MATCH on a text expression scores the boost option (1.0 by default) for each query term the row matches. A term repeated in the query counts once for each repetition.
  • MATCH_PHRASE on a text expression scores the boost option for a matching row.
  • A search on any other type scores 1.0 for a matching row.

These scores are added to _score like any other search's, so in a query that also searches indexed fields they add to BM25 scores, which are on a different scale.

A runtime search after FORK or a FROM with subqueries filters rows but does not contribute to _score unless it can be pushed into the branches. Refer to #161746.

A runtime search contributes to _score only after it runs. Filtering on _score after the search sees its score:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")
| WHERE _score > 1.5

Within one WHERE command, a condition on _score combined with AND sees the score from every search in that command, wherever it appears. Here _score > 1.5 sees the score from both MATCH functions:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings") AND _score > 1.5 AND MATCH(content, "English")

A filter, a sort, or a value computed from _score before the command that creates the searched column sees the score from before the search, which is 0.0 if no earlier search has scored. A WHERE on _score after that command combines with the search and sees its score, and so does a plain copy of _score, such as EVAL s = _score. For example, a WHERE on _score before the expression is computed keeps no books here:

FROM books METADATA _score
| WHERE _score > 1.5
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")

A value computed from _score before or alongside the expression is also 0.0, so s is 0.0 here:

FROM books METADATA _score
| EVAL s = _score + 0.0, content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")

A SORT and LIMIT before the search pick 10 books before any of them have been scored:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| SORT _score DESC
| LIMIT 10
| WHERE MATCH(content, "Tolkien Rings")

On an indexed dense_vector field, KNN runs an approximate search in the vector index. On a dense_vector expression, such as the embeddings that DENSE_VECTOR generates or a value converted with TO_DENSE_VECTOR, it runs an exact search instead: it compares the query vector with the vector in every row that reaches it.

In 9.6.0, KNN on an inline TO_DENSE_VECTOR of a field, like KNN(TO_DENSE_VECTOR(hex), [64, 0, 0]), returns no rows or fails (#163402). Convert the field with EVAL first, and search the resulting column.

KNN on an expression needs a LIMIT after it. To get the nearest neighbors, sort by _score and then limit the results. This query embeds the description of each post in the cooking_blog index from the search tutorial, and returns the 10 nearest to the query vector:

FROM cooking_blog METADATA _score
| DENSE_VECTOR description_embedding = description WITH {"inference_id": "my_embedding_model"}
| WHERE KNN(description_embedding, TEXT_EMBEDDING("vegan recipe", "my_embedding_model"))
| SORT _score DESC
| LIMIT 10

DENSE_VECTOR adds an implicit limit of 1000 rows by default, applied before it runs, so KNN compares at most those 1000 rows, not the whole index. The esql.command.dense_vector.limit setting changes the default.

When the query requests METADATA _score, a runtime KNN adds the similarity between the two vectors, scaled to a relevance score and multiplied by boost, to _score. It contributes to _score only after it runs, the same way as a full-text runtime search.

The similarity_function option picks the similarity metric, and the similarity option drops rows below a threshold. For the options a runtime KNN supports, how it picks a default metric, and the vectors it skips with a warning, refer to the KNN function reference.

A runtime search evaluates every row that reaches it, so the time it takes grows with the number of rows rather than the number of matches. To keep it fast:

  • Combine it with filters on indexed fields. Those run in the index first, so fewer rows reach the runtime search.
  • Prefer faster options. Options such as fuzziness take longer to evaluate for each row.
  • If searching a computed value is a frequent access pattern, index that value instead, for example with copy_to or a multi-field for text, or a dense_vector field for vectors, and search the indexed field.