[OAK-9899] Optimize in(x, y, z) constraints to use the terms ES query instead of a union of range queries

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: indexing
• Assignee: Unassigned
• Reporter: Nuno Santos
• Created: 16/Aug/22 09:15
• Updated: 07/Sep/22 09:07
• Resolved: 07/Sep/22 09:07

Description

The Elastic plugin is converting the in(x, y, z) constraint of SQL2 queries to a series of ElasticSearch range queries, one for each element in the set. For instance this query:

select [jcr:path] from [nt:base] where [propa] in('2', '3', '5', '7')" 

is implemented with the following ElasticSearch query:

"query": {
  "bool": {
    "filter": [{
      "bool": {
        "should": [
          {"range": {"propa": {"gte": "2","lte": "2"}}},
          {"range": {"propa": {"gte": "3","lte": "3"}}},
          {"range": {"propa": {"gte": "5","lte": "5"}}},
          {"range": {"propa": {"gte": "7","lte": "7"}}},              
        ]
      }
    }]
  }
}

The resulting ES query may become very large if the set in the in constraint is large, resulting in a large JSON request (which may be slow to generate, send and parse) and a potentially long execution time. In the worst case, the query can hit some size limits of ElasticSearch and fail.

The same in constraint can be expressed much more efficiently using the ElasticSearch terms query:

{
  "bool": {
    "filter": {
      "terms": {
        "propa": [2", "3", "5", "7"]
      }
    }
  }
}

Issue Links

• links to: GitHub Pull Request #689