Hello!
I have query:
GET route/_search
{
"stored_fields": [
"userId"
],
"query": {
"bool": {
"must": [
{
"match": {
"userId": {
"query": 30
}
}
},
{
"nested": {
"query": {
"bool": {
"must": [
{
"nested": {
"query": {
"match_all": {}
},
"path": "settings.customers",
"inner_hits": {
"from": 0,
"size": 10
}
}
},
{
"nested": {
"query": {
"match_all": {}
},
"path": "settings.regions",
"inner_hits": {
"from": 0,
"size": 300
}
}
}
]
}
},
"path": "settings",
"inner_hits": {
"from": 0,
"size": 10
}
}
}
]
}
}
}
as you see, there are 2 nested query. I need implement pagination for second level entities (settings.customers). But this query is exteremly slow on index with 200к entries (~ 1.5 min for request) when threre are a lot on settings.customers length.
Mapping:
"mappings": {
"route": {
"_source": {
"enabled": true
},
"properties": {
"userId": {
"type": "integer"
},
"userName": {
"type": "text",
"analyzer": "rt_text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"structDepartment": {
"type": "text",
"analyzer": "rt_text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"structDepartmentId": {
"type": "integer"
},
"isSpecial": {
"type": "boolean"
},
"hasInn": {
"type": "boolean"
},
"noInn": {
"type": "boolean"
},
"settings": {
"type": "nested",
"properties": {
"id": {
"type": "integer"
},
"customers": {
"type": "nested",
"properties": {
"id": {
"type": "integer"
},
"name": {
"type": "text",
"analyzer": "rt_text"
},
"inn": {
"type": "text"
},
"isKeyClient": {
"type": "boolean"
},
"userId": {
"type": "integer"
},
"settingId": {
"type": "integer"
}
}
},
"regions": {
"type": "nested",
"properties": {
"id": {
"type": "integer"
}
}
},
"startPrice": {
"type": "float"
},
"endPrice": {
"type": "float"
},
"isFederal": {
"type": "boolean"
},
"hasInn": {
"type": "boolean"
},
"userId": {
"type": "integer"
},
"regionCount": {
"type": "integer"
},
"customersCount": {
"type": "integer"
}
}
}
}
}
}
After some experiments, I noticed that the more the settings[] in the object, the longer the request is processed.
ES v 5.6.4