Hi,
(Need to understand why does aggregation query Query1 takes longer than the other)
I am running some aggregation queries as follows. The following query does this:
Query1: Finds out records with iog=XXX and shipmentId=YYY and then finds sum of "receivedQuantity" field in those records, and groups results by the shipmentId.
Time taken: ~7s
I have a similar query:
Query2: Finds out records with iog=XXX and shipmentId=YYY and then finds sum of "receivedQuantity" field in those records, and groups results by the iog. [Difference from Query1 is that it groups results by iog instead of shipmentId]
Time taken: ~600ms
Another similar query:
Query3: Finds out records with iog=XXX and shipmentId=YYY and then finds sum of "receivedQuantity" field in those records, and doesn't groups by the results.
Time taken: ~2ms
Also note that the total hits for each of the above queries is 31, ie. the number of records which meet the query filter criteria are just 31 records, which is not a very big number.
I am not able to understand, why it ES taking huge time for query1? Can you help me with the possible explanation.
I tried to find the number of distinct "iog" and "shipmentId" in the index :
- Distinct iogs: 449255
- Distinct shipmentId: 33359841
- Total records: 478421650
Also did the profiling of the query1 and maximum time taken in all the 11 shards is about 70 ms.I am not able to understand, where is the extra time being taken in the whole query if the aggregations in each shard take about 70 ms.
Pasting the aggregations profile results for one of the shards for reference:
"aggregations": [
{
"breakdown": {
"build_aggregation": 70688973,
"build_aggregation_count": 1,
...
},
"children": [
{
"breakdown": {
"build_aggregation": 0,
"build_aggregation_count": 0,
...
},
"description": "receivedQuantity",
"time": "0.0009580000000ms",
"type": "org.elasticsearch.search.aggregations.metrics.sum.SumAggregator"
}
],
"description": "iog",
"time": "70.70695400ms",
"type": "org.elasticsearch.search.aggregations.bucket.terms.GlobalOrdinalsStringTermsAggregator"
}
]
I am adding the queries and the responses for your reference below.
Query1:
curl -XGET https://$ENDPOINT/$INDEX/_search -d '
{
"size" : 0,
"query" : {
"bool" : {
"should" : [ {
"bool" : {
"filter" : [ {
"bool" : {
"filter" : {
"term" : {
"shipmentId" : "XXX"
}
}
}
}, {
"bool" : {
"filter" : {
"term" : {
"iog" : "YYY"
}
}
}
} ]
}
}
],
"minimum_should_match" : "1"
}
},
"aggregations" : {
"iog" : {
"terms" : {
"field" : "shipmentId",
"size" : 10000
},
"aggregations" : {
"receivedQuantity" : {
"sum" : {
"field" : "receivedQuantity"
}
}
}
}
}
}'
Response:
{
"_shards": {
"failed": 0,
"successful": 11,
"total": 11
},
"aggregations": {
"iog": {
"buckets": [
{
"doc_count": 31,
"key": "XXX",
"receivedQuantity": {
"value": 4078.0
}
}
]
}
},
"hits": {
"hits": [],
"max_score": 0.0,
"total": 31
},
"timed_out": false,
"took": 6585
}
Query2:
{
"size" : 0,
"query" : {
"bool" : {
"should" : [ {
"bool" : {
"filter" : [ {
"bool" : {
"filter" : {
"term" : {
"shipmentId" : "XXX"
}
}
}
}, {
"bool" : {
"filter" : {
"term" : {
"iog" : "YYY"
}
}
}
} ]
}
}
],
"minimum_should_match" : "1"
}
},
"aggregations" : {
"iog" : {
"terms" : {
"field" : "iog",
"size" : 10000
},
"aggregations" : {
"receivedQuantity" : {
"sum" : {
"field" : "receivedQuantity"
}
}
}
}
}
}
Response :
{
"_shards": {
"failed": 0,
"successful": 11,
"total": 11
},
"aggregations": {
"iog": {
"buckets": [
{
"doc_count": 31,
"key": "YYY",
"receivedQuantity": {
"value": 4078.0
}
}
]
}
},
"hits": {
"hits": [],
"max_score": 0.0,
"total": 31
},
"timed_out": false,
"took": 592
}