Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):
AWS Managed 2.17
Describe the issue:
Since we upgraded our Opensearch clusters from 1.3 to 2.13 and then 2.17 we have been seeing queries being very slow intermittently. Eg the same query which can return in 0.5 seconds, take 35+ seconds.
The intermittent slowness appears to be due to a nested boolean sort condition. We are seeing this against queries that only return a small number of records, 38 in this case.
We have tried using the profiler to determine the cause of the slowness, but oddly, when the profiler is used, the query always returns within 0.7 seconds.
Configuration:
Sort condition
"sort": [
{
"field.values.text.value.lower": {
"order": "asc",
"missing": "_first",
"nested": {
"path": "field.values",
"filter": {
"bool": {
"filter": [
{
"terms": {
"field.values.field": [
"app/profile:name"
],
"boost": 1
}
},
{
"bool": {
"should": [
{
"term": {
"field.values.text.lang": {
"value": "en-us",
"boost": 1
}
}
},
{
"bool": {
"must_not": [
{
"exists": {
"field": "field.values.text.lang",
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"minimum_should_match": "1",
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
"max_children": 1
}
}
},
{
"tip": {
"order": "asc"
}
}
],
Relevant Logs or Screenshots: