How to optimize elasticsearch query dsl to improve the query performance?

Dear elasticsearch technology experts,I have a elasticsearch index which size is almost 18Gb,and the amount of documents is about 400000.There are 3 nodes in the elasticserch cluster,and each nodes server is 2Cpu 4Gb memory. It costs almost 90ms to execute a dsl to get search response based on 40K documents. How can I optimize the mapping settings or the query dsl to improve query performance? Based on what principles to solve this problem?

The mapping settings of my index is as follows:

{
  "settings" : {
    "similarity": {
      "scripted_tfidf": {
        "type": "scripted",
        "script": {
          "source": "double tf = doc.freq; return query.boost * tf;"
        }
      },
      "scripted_tfidf_new": {
        "type": "scripted",
        "script": {
          "source": "double tf = doc.freq; return query.boost * 1;"
        }
      }
    },
    "analysis": {
        "normalizer": {
          "lowercase_normalizer": {
            "type": "custom",
            "char_filter": [],
            "filter": ["lowercase"]
          }},
        "char_filter" : {
          "my_filter" : {
            "type" : "mapping",
            "mappings" : [":=>,",".=>,","!=>,","؛=>,","*=>,","/=>,",")=>,","(=>,","+=>,","-=>,","_=>,","$=>,","@=>,","£=>,","¢=>,","¥=>,","^=>,","°=>,","==>,","{=>,","}=>,","✓=>,","™=>,","®=>,","©=>,","٪=>,","~=>,","`=>,","|=>,","•=>,","√=>,","÷=>,","×=>,","∆=>,","<=>,",">=>,","?=>,",";=>,","。=>,",",=>,",";=>,","#=>,","%=>,",":=>,","“=>,","”=>,","'=>,"]
          }
        },
        "filter": {
          "stop_filter":{
            "type" : "stop",
            "stopwords": "_english_"
          },
            "pinyin_filter" : {
              "remove_duplicated_term" : "true",
              "keep_joined_full_pinyin" : "true",
              "none_chinese_pinyin_tokenize" : "false",
              "type" : "pinyin",
              "lowercase": true,
              "keep_none_chinese_in_joined_full_pinyin" : "true",
              "keep_full_pinyin" : "false"
            }
        }, 
            "tokenizer" : {
            "my_ngram" : {
              "token_chars" : [
                "letter",
                "digit",
                "punctuation",
                "symbol"
              ],
              "min_gram" : "2",
              "type" : "ngram",
              "max_gram" : "3"
            },
            "my_edge_ngram": {
                          "token_chars": [
                            "letter",
                            "digit",
                            "punctuation",
                            "symbol",
                                "whitespace"
                        ],
                        "min_gram": "1",
                        "type": "edge_ngram",
                        "max_gram": "20"
                    },
                    "edge_ngram_letter_digit": {
                        "token_chars": [
                            "letter",
                            "digit"
                        ],
                        "min_gram": "1",
                        "type": "edge_ngram",
                        "max_gram": "30"
                }
        },
        "analyzer" : {
        "my_analyze" : {
            "char_filter":["my_filter"],
            "tokenizer":"standard",
            "filter":["lowercase","stop_filter"]
          },
            "ik_pinyin_analyzer" : {
              "filter" : [
                "pinyin_filter"
              ],
              "tokenizer" : "my_ngram"
          },
        "my_analyze_edge_ngram": {
                    "tokenizer": "my_edge_ngram",
                    "filter": "lowercase"
                },
                "edge_ngram_letter_digit_analyze": {
                    "tokenizer": "edge_ngram_letter_digit",
                    "filter": "lowercase"
                }
        }
      },
    "index" : {
      "number_of_shards" : "3",
      "number_of_replicas" : "0"
    }
  },
  "mappings" : {
      "dynamic" : "strict",
      "properties" : {
        "name_lang" : {
          "properties": {
          "ru":  { 
              "type" : "text",
              "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "my_analyze",
                  "fields" : {
                "keyword_field" : {
                  "type" : "keyword",
                  "similarity":"scripted_tfidf_new",
                  "normalizer" : "lowercase_normalizer"
               },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }
                  } 
            },
          "zh-cn": {
              "type" : "text",
              "store" : true,
              "similarity":"scripted_tfidf_new",
               "fields" : {
                "keyword_field" : {
              "type" : "keyword",
              "similarity":"scripted_tfidf_new",
              "normalizer" : "lowercase_normalizer"
                },
                        "pinyin" : {
                          "type" : "text",
                          "term_vector" : "with_positions_offsets",
                          "analyzer" : "ik_pinyin_analyzer",
                              "similarity" : "scripted_tfidf_new",
                          "search_analyzer" : "pinyin"
                      }
                 ,
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }
               },
                    "analyzer" : "ik_max_word"
            },
                "es-es":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                      "analyzer": "spanish",
                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                "th":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                      "analyzer": "thai",
                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                "mai":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                     "analyzer": "my_analyze",
                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                "tl":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                      "analyzer": "my_analyze",
                  "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                "ta":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                      "analyzer": "my_analyze",
                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                
                
                "bg":  { 
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "bulgarian",
                                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                }, 
          
                
                "mk": {
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "my_analyze",
                                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                
                "lo": {
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "my_analyze",
                                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                
                "hi": {
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "hindi",
                                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
                },
                "de": {
                  "type" : "text",
                  "store" : true,
                  "similarity":"scripted_tfidf_new",
                  "analyzer": "german",
                                      "fields" : {
                    "keyword_field" : {
                      "type" : "keyword",
                      "similarity":"scripted_tfidf_new",
                      "normalizer" : "lowercase_normalizer"
                   },
                   "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }}
            }
          }
        },
        "name_lang_list" : {
          "type": "text",
          "store": true,
              "similarity":"scripted_tfidf_new",
              "analyzer": "my_analyze",
              "fields" : {
                "keyword_field" : {
                  "type" : "keyword",
                  "store": true,
                  "similarity":"scripted_tfidf_new",
                  "normalizer" : "lowercase_normalizer"
               },
                                        "edge_ngram_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "my_analyze_edge_ngram"
                            },
                            "edge_ngram_letter_digit_field": {
                                "type": "text",
                                "store": true,
                                "similarity": "scripted_tfidf_new",
                                "analyzer": "edge_ngram_letter_digit_analyze"
                            }
              } 
        },
        "des_list" : {
          "type": "text",
          "store": true,
              "analyzer": "my_analyze"
        },

      }
    }
} 

And my query dsl is as follows:

{
        "explain":"false", 
        "from": 0,
        "size": 50,
        "query": {
            "dis_max": {
                "queries": [
              { "function_score": {
                      "query": {
                          "bool": {
                              "should": [
                                    {
                                     "dis_max": {
                                       "boost": 1,
                                        "queries": [
                                             {"match_phrase": {
                                               "name_lang.zh-cn.pinyin": {
                                                  "query":"google", "boost": 20
                                             }}},
                                             {"match_phrase": {
                                               "name_lang.zh-tw.pinyin": {
                                                  "query":"google", "boost": 20}
                                             }},
                                             {"match_phrase": {
                                               "name_lang.zh-hk.pinyin": {
                                                  "query":"google", "boost": 20}
                                             }},
                                             {"match": {
                                              "name_lang_list": {
                                                  "query": "google",
                                                  "minimum_should_match": "50%",
                                                  "boost": 30,
                                                  "fuzziness": "auto",
                                                  "prefix_length": 1,
                                                  "max_expansions": 10,
                                                  "fuzzy_rewrite":"constant_score_boolean"
                                              }}}
                                      ]
                                    }},
                                     {"match": {
                                      "description_list": {
                                          "query": "google",
                                          "boost": 2
                                      }}},
                                     {
                                      "dis_max": {
                                        "boost": 120, 
                                        "queries": [
                                             {"term": {"name_lang.ru.keyword_field": "google"}},
                                             {"term": {"name_lang.zh-cn.keyword_field": "google"}},
                                             {"term": {"name_lang.es-es.keyword_field": "google"}},
                                             {"term": {"name_lang.th.keyword_field": "google"}},
                                             
                                             {"term": {"name_lang.sr.keyword_field": "google"}},
                                             {"term": {"name_lang.kk.keyword_field": "google"}},
                                             {"term": {"name_lang.si.keyword_field": "google"}},
                                             {"term": {"name_lang.ka.keyword_field": "google"}},
                                             {"term": {"name_lang.sk.keyword_field": "google"}},
                                             {"term": {"name_lang.pt-pt.keyword_field": "google"}},
                                             {"term": {"name_lang.sl.keyword_field": "google"}},
                                             {"term": {"name_lang.bn.keyword_field": "google"}},
                                             {"term": {"name_lang.jv.keyword_field": "google"}},
                                             {"term": {"name_lang.bo.keyword_field": "google"}},
                                             {"term": {"name_lang.bs.keyword_field": "google"}},
                                             {"term": {"name_lang.es-us.keyword_field": "google"}},
                                             {"term": {"name_lang.fi.keyword_field": "google"}},
                                             {"term": {"name_lang.be.keyword_field": "google"}},
                                             {"term": {"name_lang.bg.keyword_field": "google"}},
                                             {"term": {"name_lang.zh-hk.keyword_field": "google"}},
                                             {"term": {"name_lang.zh-tw.keyword_field": "google"}},
                                             {"term": {"name_lang.fa.keyword_field": "google"}},
                                             {"term": {"name_lang.ro.keyword_field": "google"}},
                                             {"term": {"name_lang.nl.keyword_field": "google"}},
                                             {"term": {"name_lang.as.keyword_field": "google"}},
                                             {"term": {"name_lang.vi.keyword_field": "google"}},
                                             {"term": {"name_lang.my-mm.keyword_field": "google"}},
                                             {"term": {"name_lang.nb.keyword_field": "google"}},
                                             {"term": {"name_lang.ja.keyword_field": "google"}},
                                             {"term": {"name_lang.ne.keyword_field": "google"}},
                                             {"term": {"name_lang.az.keyword_field": "google"}},
                                             {"term": {"name_lang.it.keyword_field": "google"}},
                                             {"term": {"name_lang.am.keyword_field": "google"}},
                                             {"term": {"name_lang.iw.keyword_field": "google"}},
                                             {"term": {"name_lang.et.keyword_field": "google"}},
                                             {"term": {"name_lang.eu.keyword_field": "google"}},
                                             {"term": {"name_lang.ar.keyword_field": "google"}},
                                             
                                             {"term": {"name_lang.pt-br.keyword_field": "google"}},
                                             {"term": {"name_lang.lt.keyword_field": "google"}},
                                             {"term": {"name_lang.pl.keyword_field": "google"}},
                                             {"term": {"name_lang.da.keyword_field": "google"}},
                                             {"term": {"name_lang.tr.keyword_field": "google"}},
                                             {"term": {"name_lang.hi.keyword_field": "google"}},
                                             {"term": {"name_lang.de.keyword_field": "google"}}
                                          ]
                                      }},
                                      {
                                      "dis_max": {
                                        "boost": 20, 
                                        "queries": [
                                             {"prefix": {"name_lang.ru.keyword_field": "google"}},
                                             {"prefix": {"name_lang.zh-cn.keyword_field": "google"}},
                                             {"prefix": {"name_lang.es-es.keyword_field": "google"}},
                                             {"prefix": {"name_lang.th.keyword_field": "google"}},
                                             {"prefix": {"name_lang.mai.keyword_field": "google"}},
                                             {"prefix": {"name_lang.tl.keyword_field": "google"}},
                                             {"prefix": {"name_lang.ta.keyword_field": "google"}},
                                             {"prefix": {"name_lang.gu.keyword_field": "google"}},
                                             {"prefix": {"name_lang.cs.keyword_field": "google"}},
                                             {"prefix": {"name_lang.te.keyword_field": "google"}},
                                             {"prefix": {"name_lang.pa.keyword_field": "google"}},
                                             {"prefix": {"name_lang.km.keyword_field": "google"}},
                                             {"prefix": {"name_lang.kn.keyword_field": "google"}}
                                             {"prefix": {"name_lang.sk.keyword_field": "google"}},
                                            
                                             {"prefix": {"name_lang.de.keyword_field": "google"}}
                                          ]
                                      }},
                                      {
                                      "dis_max": {
                                        "boost": 5, 
                                        "queries": [
                                             {"prefix": {"name_lang.ru": "google"}},
                                             {"prefix": {"name_lang.zh-cn": "google"}},
                                             {"prefix": {"name_lang.es-es": "google"}},
                                             {"prefix": {"name_lang.th": "google"}},
                                             {"prefix": {"name_lang.mai": "google"}},
                                             {"prefix": {"name_lang.tl": "google"}},
                                             {"prefix": {"name_lang.ta": "google"}},
                                             {"prefix": {"name_lang.gu": "google"}},
                                             {"prefix": {"name_lang.cs": "google"}},
                                             {"prefix": {"name_lang.te": "google"}},
                                             {"prefix": {"name_lang.pa": "google"}},
                                             {"prefix": {"name_lang.km": "google"}},
                                             {"prefix": {"name_lang.kn": "google"}},
                                             {"prefix": {"name_lang.or": "google"}},
                                             {"prefix": {"name_lang.sv": "google"}},
                                             {"prefix": {"name_lang.ko": "google"}},
                                             {"prefix": {"name_lang.sw": "google"}},
                                             {"prefix": {"name_lang.gl": "google"}},
                                             {"prefix": {"name_lang.en-us": "google"}},
                                             {"prefix": {"name_lang.ca": "google"}},
                                             {"prefix": {"name_lang.sr": "google"}},
                                             {"prefix": {"name_lang.kk": "google"}},
                                             {"prefix": {"name_lang.si": "google"}},
                                             {"prefix": {"name_lang.ka": "google"}},
                                             {"prefix": {"name_lang.sk": "google"}},
                                             {"prefix": {"name_lang.pt-pt": "google"}},
                                             {"prefix": {"name_lang.sl": "google"}},
                                             {"prefix": {"name_lang.bn": "google"}},
                                             {"prefix": {"name_lang.jv": "google"}},
                                             {"prefix": {"name_lang.bo": "google"}},
                                             {"prefix": {"name_lang.bs": "google"}},
                                             {"prefix": {"name_lang.es-us": "google"}},
                                             
                                             {"prefix": {"name_lang.de": "google"}}
                                          ]
                                      }}
                                    ]
                          }
                      },
                
                     "script_score":{
                          "script": {
                            "source": "if(doc['order_num'].size() > 0 && doc['order_num'].value > 0 && doc['popularity_long'].size() > 0 && doc['popularity_long'].value > 0) {return 30.0 * Math.log1p(doc['popularity_long'].value) / Math.log1p(20) + 5.0 * Math.log1p(doc[\"download_num\"].value) / Math.log1p(1000000000) + 30.0 * Math.log1p(10.0 * doc['order_num'].value) / Math.log1p(1000); } if(doc['popularity_long'].size() > 0 && doc['popularity_long'].value > 0) {return 30.0 * Math.log1p(doc['popularity_long'].value) / Math.log1p(20) + 5.0 * Math.log1p(doc[\"download_num\"].value) / Math.log1p(1000000000) / Math.log1p(1000); } if(doc['order_num'].size() > 0 && doc['order_num'].value > 0) {return 5.0 * Math.log1p(doc[\"download_num\"].value) / Math.log1p(1000000000) + 30.0 * Math.log1p(10.0 * doc['order_num'].value) / Math.log1p(1000); } else return 5.0 * Math.log1p(doc[\"download_num\"].value) / Math.log1p(1000000000);"
                          }
                        },
                      "boost_mode": "sum",
                      "max_boost": 300
                  
                
              }}
                ]
            }
        }
}

I used Search Profiler to check which query cost too much time,and I found that is
”des_list “ query take most of time, which is a long text.
How can I improve my query performance?

Welcome to our community! :smiley:

90ms seems pretty good, what do you want to get it to?

2 Likes

Is it any room to improve the latency of retriving results?
We expect the latency of excuting dsl to reach 30ms.

Why 30ms? Wondering how a human would make the difference between 90ms and 30ms TBH.

But to answer your question, if you want to make everything faster, I'd suggest to remove all the scripts. That might be the place it's "slowing down".
And may be a native java plugin could be a bit faster than the script... But not sure.

3 Likes

Plus your ngrams are very expensive.

2 Likes

Because our retrieval link involves many modules, retrieving data from es is only one of the small modules, so we need to strictly limit the latency of each module.

We can not stand our api P99 latency larger than 100ms.

Thanks, dear elasticsearch technology experts.
I will check for that if a native java plugin help.
Best regards.