Query by the sum of two or more fields

Elastic Stack Elasticsearch
1

Let's say I have the following mapping with hundreds of millions of docs currently populating the index:

{
  "properties": {
    "a": { "type": "integer" },
    "b": { "type": "integer" },
    "c": { "type": "integer" }
  }
}

I would like to query by the sum of two or more of these fields. It seems that script query is the way to go, so I put together the following query:

{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "lang": "painless",
            "source": "(doc['a'].value + doc['b'].value + doc['c'].value) > params.sum",
            "params": {
              "sum": 20
            }
          }
        }
      }
    }
  }
}

However, this is incredibly slow, taking several seconds to complete. Is there a more efficient way to accomplish this?

Thanks

2

You could store the sum in a separate field when you index the document, which will be a lot faster. This way you pay the cost of the calculation once per document at indexing time rather once per document per query.

3

Thanks for the response. Any other solutions? Fields aren't just limited to a, b, and c - and sum queries could be any combination of those fields.

4

Then it sounds like a script is the best solution even though it, as you have noted, comes with a performance penalty.