Hi, please help me figure out, is there any way to optimize this request? At the moment, the request is being executed for about 30-40 seconds.
The index stores information about the call history.
The query builds a report of employees' calls grouped by their contacts (the aggregation of numbers is the reason for the slowdown, since a pass is made through all to_numbers and then tags are counted for each of them). We using routing by point_id.
In the near future, we plan to move records by point_id to separate indices with a more optimal number of shards
Tell me, what settings can you pay attention to?
Tried setting "eager_global_ordinals": true for to_number on a test index but did not notice any performance gain
Elastic 6.5.4. Index size ~800gb
Cluster contains 4 nodes (each of them 64GB RAM)
rec-all 2 r STARTED 101783042 117.5gb 192.168.5.105 s2t-elastic-prod-n3
rec-all 2 p STARTED 101783043 123.6gb 192.168.4.90 s2t-elastic-prod-n4
rec-all 4 p STARTED 106793966 104.9gb 192.168.4.246 s2t-elastic-prod-n2
rec-all 4 r STARTED 106793966 108.8gb 192.168.4.245 s2t-elastic-prod-n1
rec-all 3 p STARTED 105849535 145.8gb 192.168.4.246 s2t-elastic-prod-n2
rec-all 3 r STARTED 105849535 159.9gb 192.168.4.245 s2t-elastic-prod-n1
rec-all 1 r STARTED 107922833 345.6gb 192.168.5.105 s2t-elastic-prod-n3
rec-all 1 p STARTED 107922833 332gb 192.168.4.90 s2t-elastic-prod-n4
rec-all 0 p STARTED 101688676 108.6gb 192.168.5.105 s2t-elastic-prod-n3
rec-all 0 r STARTED 101688676 114gb 192.168.4.246 s2t-elastic-prod-n2
Mapping:
{
"_all": {
"enabled": false
},
"_routing": {
"required": false
},
"dynamic": "strict",
"properties": {
//{`active`, `hidden`, `deleted`}
"state": {
"type": "keyword"
},
"vpbx": {
"type": "object",
"properties": {
"point_id": {
"type": "long"
},
"time_created": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
},
"from_number": {
"type": "keyword"
},
"to_number": {
"type": "keyword"
},
// [`out`, `in`, `int`]
"conv_direction": {
"type": "keyword"
},
"from_member_id": {
"type": "long"
},
"to_member_id": {
"type": "long"
}
}
},
"s2t": {
"type": "object",
"properties": {
"task_id": {
"type": "keyword"
},
"time_created": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
},
"categories": {
"type": "object",
"properties": {
"id": {
"type": "long"
},
"version": {
"type": "long"
},
"assign_time": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
}
}
},
"categories_history": {
"dynamic": "true",
"enabled": false,
"properties": {}
}
}
}
}
}
Query
{
"query": {
"bool": {
"must": [
{
"constant_score": {
"filter": {
"query_string": {
"query": "vpbx.time_created:>=1623272400000 AND vpbx.time_created:<=1626814799000 AND (vpbx.conv_direction:in OR vpbx.conv_direction:out) AND _exists_:s2t AND (state:active OR state:retrospective)"
}
}
}
},
{
"terms": {
"vpbx.conv_direction": [
"out",
"in"
]
}
},
{
"range": {
"vpbx.from_member_id": {
"from": 1
}
}
}
]
}
},
"size": 0,
"from": 0,
"aggs": {
"term_lvl1": {
"composite": {
"size": 5000,
"sources": [
{
"key": {
"terms": {
"field": "vpbx.from_member_id"
}
}
}
]
},
"aggs": {
"numbers": {
"filter": {
"term": {
"vpbx.to_member_id": -1
}
},
"aggs": {
"numbers": {
"terms": {
"field": "vpbx.to_number",
"size": 32767
},
"aggs": {
"categories": {
"terms": {
"field": "s2t.categories.id",
"size": 32767
}
}
}
}
}
}
}
}
}
}