having an elastic search query, need to find its optimization because it takes more CPU time and memory, my first thought is to some changes required in parent/child relationship, How can I optimize this elasticsearch query or change the mapping to get the same result.
GET trending/_search
{
"track_total_hits":true,
"size":-1,
"sort":[
{
"id":{
"order":"desc"
}
}
],
"query":{
"bool":{
"must":[
{
"term":{
"type":"post"
}
},
{
"terms":{
"post_type_id":[
1,
2,
5,
7
]
}
},
{
"has_parent":{
"parent_type":"user",
"query":{
"bool":{
"should":[
{
"bool":{
"must":{
"has_child":{
"type":"followers",
"query":{
"bool":{
"must":[
{
"term":{
"status":"A"
}
},
{
"term":{
"user_id":87
}
}
]
}
}
}
}
}
},
{
"bool":{
"must":[
{
"terms":{
"id":[
5,
14,
19,
30,
31,
60,
64,
72,
74,
75,
77,
80,
81,
85,
92,
101,
112,
138,
139,
189,
196,
201,
205,
210,
211,
224,
238,
239,
274,
275,
283,
336,
421,
434,
585,
633,
649,
687,
788,
836,
1442,
1479,
1607,
1699,
1775,
1779,
1784,
1823,
1863,
1868,
1899,
2131,
2170,
2329,
2376,
2389,
2401,
2405,
2508,
2568,
2802,
2892,
3074,
3082,
3196,
3312,
3315,
3326,
3391,
3520,
3765,
3853,
3983,
4037,
4436,
4533,
4936,
5018,
5116,
5131,
5353,
5653,
5673,
5674,
5699,
5713,
5789,
5837,
5889,
6391,
6586,
6641,
6819,
6872,
6942,
7302,
7427,
7765,
7828,
8204,
8205,
8402,
8608,
8625,
8655,
8695,
9026,
9116,
9365,
9430,
9600,
14080,
14594,
16543,
17115,
17118,
17825,
17914,
18323,
18368,
18371,
18636,
19071,
19415,
19418,
19632,
19712,
19727,
19978,
20000,
20433,
21132,
23015,
24514,
25266,
25601,
27300,
28493,
28658,
29433,
29441,
29460,
29604,
30104,
30176,
30525,
30965,
31072,
31130,
31497,
31915,
32004,
32184,
32294,
32337,
34053,
36019,
36246,
36986
]
}
}
]
}
}
]
}
}
}
},
{
"has_child":{
"type":"post_box",
"query":{
"bool":{
"must":[
{
"terms":{
"status":[
"A",
"F"
]
}
}
]
}
}
}
},
{
"range":{
"created_at":{
"lte":"2022-06-28T05:18:17Z"
}
}
}
]
}
}
}
and here is the result of the query,
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-1",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 1,
"name" : "London Restaurants",
"searchable_title" : "London Restaurants",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:48:05Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-2",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 2,
"name" : "Exploring England",
"searchable_title" : "Exploring England",
"user_id" : 3,
"status" : "A",
"created_at" : "2017-01-17T08:53:18Z",
"type" : {
"parent" : "u-3",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-5",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 5,
"name" : """Friends