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👬""",
"searchable_title" : """Friends👬""",
"user_id" : 5,
"status" : "A",
"created_at" : "2017-10-02T04:56:57Z",
"type" : {
"parent" : "u-5",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-10",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 10,
"name" : "water",
"searchable_title" : "water",
"user_id" : 7,
"status" : "A",
"created_at" : "2017-01-20T06:11:21Z",
"type" : {
"parent" : "u-7",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-11",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 11,
"name" : "leggings ",
"searchable_title" : "leggings ",
"user_id" : 7,
"status" : "A",
"created_at" : "2017-01-20T06:12:55Z",
"type" : {
"parent" : "u-7",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-14",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 14,
"name" : "new tech",
"searchable_title" : "new tech",
"user_id" : 8,
"status" : "A",
"created_at" : "2017-01-23T04:04:05Z",
"type" : {
"parent" : "u-8",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-16",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 16,
"name" : "Adventure",
"searchable_title" : "Adventure",
"user_id" : 16,
"status" : "A",
"created_at" : "2017-01-26T11:18:56Z",
"type" : {
"parent" : "u-16",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-17",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 17,
"name" : "nights out",
"searchable_title" : "nights out",
"user_id" : 8,
"status" : "A",
"created_at" : "2017-01-27T05:03:22Z",
"type" : {
"parent" : "u-8",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-18",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 18,
"name" : "boxxx",
"searchable_title" : "boxxx",
"user_id" : 18,
"status" : "F",
"created_at" : "2017-01-27T05:03:35Z",
"type" : {
"parent" : "u-18",
"name" : "box"
}
}
},
{
"_index" : "trending",
"_type" : "_doc",
"_id" : "bx-19",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"id" : 19,
"name" : "animals ",
"searchable_title" : "animals ",
"user_id" : 18,
"status" : "F",
"created_at" : "2017-01-27T05:07:27Z",
"type" : {
"parent" : "u-18",
"name" : "box"
}
}
}
]
}
}```
And here is the mappings of the index,
{
"trending" : {
"mappings" : {
"properties" : {
"box_id" : {
"type" : "integer"
},
"categories" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword"
}
},
"fielddata" : true
},
"category_id" : {
"type" : "long"
},
"chat_channel" : {
"type" : "keyword"
},
"created_at" : {
"type" : "date"
},
"delete_one" : {
"type" : "long"
},
"delete_two" : {
"type" : "long"
},
"device_id" : {
"type" : "keyword"
},
"dob" : {
"type" : "date"
},
"email" : {
"type" : "keyword"
},
"friend_box" : {
"type" : "integer"
},
"friend_posts" : {
"type" : "integer"
},
"full_name" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"gender" : {
"type" : "keyword"
},
"id" : {
"type" : "long"
},
"is_live" : {
"type" : "boolean"
},
"is_verified" : {
"type" : "boolean"
},
"item_type_number" : {
"type" : "integer"
},
"message_content" : {
"type" : "keyword"
},
"message_object" : {
"type" : "text"
},
"message_privacy" : {
"type" : "long"
},
"name" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"object_id" : {
"type" : "long"
},
"phone" : {
"type" : "text",
"fields" : {
"raw" : {
"type" : "keyword"
}
},
"term_vector" : "yes"
},
"phone_post_fix" : {
"type" : "long"
},
"picture" : {
"type" : "text"
},
"post_id" : {
"type" : "integer"
},
"post_media" : {
"properties" : {
"bg_color" : {
"type" : "text"
},
"file" : {
"type" : "text"
},
"file_type_number" : {
"type" : "long"
},
"medium_file_height" : {
"type" : "long"
},
"medium_file_width" : {
"type" : "long"
}
}
},
"post_type_id" : {
"type" : "long"
},
"private_box" : {
"type" : "integer"
},
"private_posts" : {
"type" : "integer"
},
"public_box" : {
"type" : "integer"
},
"public_posts" : {
"type" : "integer"
},
"searchable_title" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
},
"source_key" : {
"type" : "keyword"
},
"status" : {
"type" : "keyword"
},
"type" : {
"type" : "join",
"eager_global_ordinals" : true,
"relations" : {
"post" : [
"discover_views",
"post_box"
],
"box" : "box_post",
"user" : [
"followers",
"post",
"blocked",
"followings",
"messages",
"box",
"block"
]
}
},
"uid" : {
"type" : "keyword"
},
"user_id" : {
"type" : "long"
},
"username" : {
"type" : "text",
"fields" : {
"autocomplete" : {
"type" : "text",
"analyzer" : "autocomplete"
},
"edgengram" : {
"type" : "text",
"analyzer" : "edge_ngram_analyzer",
"search_analyzer" : "edge_ngram_search_analyzer"
},
"fv_search" : {
"type" : "text",
"analyzer" : "fv_search_analyzer"
},
"raw" : {
"type" : "keyword"
},
"search_edgenGram" : {
"type" : "text",
"analyzer" : "search_edgenGram_analyzer"
},
"search_nGram" : {
"type" : "text",
"analyzer" : "search_nGram_analyzer"
},
"special_character" : {
"type" : "text",
"analyzer" : "alphanumeric_string_analyzer"
}
},
"term_vector" : "yes",
"analyzer" : "autocomplete",
"search_analyzer" : "standard",
"fielddata" : true
}
}
}
}
}
Here is the somewhat optimized query , I totally remove the has_parent and has child join at above the query to get the friends, getting it from the db and providing the ids in simple terms query,
GET trending/_search
{"size": 40,
"query": {
"bool": {
"must": [
{
"term": {
"type": "post"
}
},
{
"terms": {
"post_type_id": [
1,
2,
5,
7
]
}
},
{ "terms":{
"user_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-07-06T07:19:39Z"
}
}
}
]
}
}
}