When we hit the opendistro endpoint with SQL query we are receiving an Opensearch query. For the current query, we get huge query. Is there any way using which we can optimise it ?
POST _opendistro/_sql/_explain
{
"query": "select * from cmbs_dealreport_snapshot_dev where (dealid in (4752, 4503, 6114, 5987)) "
}
The Opensearch query we get is nested with multiple bool and should clauses. Rather can it not create an array and add all the values inside one should
{
"query":{
"bool":{
"should":[
{
"term":{
"dealid":{
"value":4752,
"boost":1
}
}
},
{
"bool":{
"should":[
{
"term":{
"dealid":{
"value":4503,
"boost":1
}
}
},
{
"bool":{
"should":[
{
"term":{
"dealid":{
"value":6114,
"boost":1
}
}
},
{
"term":{
"dealid":{
"value":5987,
"boost":1
}
}
}
],
"adjust_pure_negative":true,
"boost":1
}
}
],
"adjust_pure_negative":true,
"boost":1
}
}
],
"adjust_pure_negative":true,
"boost":1
}
}
}
Expected Query
{
"query": {
"bool": {
"should": [
{
"terms": {
"dealid": [
4752,
4503,
6114,
5987
]
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
}