SQL: Queries with script filter result in a linear scan through all documents even if LIMIT is used

[Luegg]

For example, the query SELECT station.name FROM \"weather-data-2016\" WHERE LENGTH(station.name) > 10 LIMIT 10 takes more than 60s on the NOAA benchmark dataset. The equivalent search request on the other hand only takes a few ms:

POST http://localhost:39200/weather-data-2016/_search
Content-Type: application/json

{
  "size": 10,
  "query": {
    "script": {
      "script": {
        "source": "InternalQlScriptUtils.nullSafeFilter(InternalQlScriptUtils.gt(InternalSqlScriptUtils.length(InternalQlScriptUtils.docValue(doc,params.v0)),params.v1))",
        "lang": "painless",
        "params": {
          "v0": "station.name",
          "v1": 5
        }
      },
      "boost": 1.0
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "station.name"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

Note, the search request does not open a scroll context but SQL does. Opening the scroll context causes ES to count all matching documents which requires to evaluate the script on every doc.

This issue could be addressed by using PIT instead of scroll contexts (see #61873).

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[bpintea]

Opening the scroll context causes ES to count all matching documents which requires to evaluate the script on every doc.

Curious how is the result evaluated when not opening a scroll context (or with PIT), if not going through every doc?
Is the size considered in one case (scroll/PIT), but not the other?

[Luegg]

Sorry, only saw your question today @bpintea. The script filter only needs to be applied until you have 10 matches. If the script is not too selective, you might only have to go through a few docs.

It seems to be a peculiarity of the scroll functionality that it includes a match count. Maybe that's not even necessary and a bug. PIT does not have this issue.