Why my search slow?


(Ganle Hu) #1

I upgrade my es cluster to 5.5 and have following mapping:

    "append": {
      "type": "short"
    },
    "comment": {
      "type": "text",
      "index": "analyzed"
    },
    "create_time": {
      "type": "date"
    },
    "desc_score": {
      "type": "integer"
    },
    "goods_id": {
      "type": "long"
    },
    "goods_name": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "handler": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "is_first_review": {
      "type": "short"
    },
    "logistic_score": {
      "type": "integer"
    },
    "mall_id": {
      "type": "long"
    },
    "order_id": {
      "type": "long"
    },
    "order_sn": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "parent_id": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "review_id": {
      "type": "long"
    },
    "score": {
      "type": "integer"
    },
    "service_score": {
      "type": "integer"
    },
    "shipping_id": {
      "type": "long"
    },
    "shipping_name": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "status": {
      "type": "short"
    },
    "tracking_number": {
      "type": "keyword",
      "index": "not_analyzed"
    },
    "update_time": {
      "type": "date"
    },
    "user_id": {
      "type": "long"
    }

When I profile in kibana with the following statement and got the result in the picture.

{
      "from": 0,
      "size": 10,
      "query": {
      "bool": {
      "filter": [
        {
          "terms": {
            "goods_id": [
              "262628158"
            ],
            "boost": 1.0
          }
        },
        {
          "terms": {
            "status": [
              "2",
              "4"
            ],
            "boost": 1.0
          }
        },
        {
          "range": {
            "create_time": {
              "from": "1514027649",
              "to": "1514632449",
              "include_lower": true,
              "include_upper": true,
              "boost": 1.0
            }
          }
        }
      ],
      "disable_coord": false,
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "sort": [
    {
      "create_time": {
        "order": "desc"
      }
    }
  ]
}

I am confused why status filter cost so much time, and status is a field with only five value, this is perpahps the reason cause the problem but I do not know how to optimize my search clause. I have search a lot with google but got no answer yet. Any one can help me?


(Ganle Hu) #2

I add profile=true to the search clause and get the following profile result(total 18 shards, I only copy one shard result here):

    {
      "id": "[mCqNoGIbS3qevADl0sGdbA][reviews][3]",
      "searches": [
        {
          "query": [
            {
              "type": "BooleanQuery",
              "description": "+(#goods_id:{262628158} #ConstantScore(status:{2 4}) #create_time:[1514027649 TO 1514632449]) #ConstantScore(_type:customer-review)",
              "time": "808.2106010ms",
              "time_in_nanos": 808210601,
              "breakdown": {
                "..."
              },
              "children": [
                {
                  "type": "BooleanQuery",
                  "description": "#goods_id:{262628158} #ConstantScore(status:{2 4}) #create_time:[1514027649 TO 1514632449]",
                  "time": "807.2568900ms",
                  "time_in_nanos": 807256890,
                  "breakdown": {
                    "..."
                  },
                  "children": [
                    {
                      "type": ""
                      "description": "goods_id:{262628158}",
                      "time": "1.385719000ms",
                      "time_in_nanos": 1385719,
                      "breakdown": {
                        "..."
                      }
                    },
                    {
                      "type": "ConstantScoreQuery",
                      "description": "ConstantScore(status:{2 4})",
                      "time": "804.3832830ms",
                      "time_in_nanos": 804383283,
                      "breakdown": {
                        "..."
                      },
                      "children": [
                        {
                          "type": ""
                          "description": "status:{2 4}",
                          "time": "804.2139920ms",
                          "time_in_nanos": 804213992,
                          "breakdown": {
                            "score": 0,
                            "build_scorer_count": 180,
                            "match_count": 0,
                            "create_weight": 527,
                            "next_doc": 0,
                            "match": 0,
                            "create_weight_count": 1,
                            "next_doc_count": 0,
                            "score_count": 0,
                            "build_scorer": 804197847,
                            "advance": 15401,
                            "advance_count": 36
                          }
                        }
                      ]
                    },
                    {
                      "type": "IndexOrDocValuesQuery",
                      "description": "create_time:[1514027649 TO 1514632449]",
                      "time": "0.8318200000ms",
                      "time_in_nanos": 831820,
                      "breakdown": {
                        "score": 0,
                        "build_scorer_count": 180,
                        "match_count": 0,
                        "create_weight": 1067,
                        "next_doc": 1440,
                        "match": 0,
                        "create_weight_count": 1,
                        "next_doc_count": 4,
                        "score_count": 0,
                        "build_scorer": 812194,
                        "advance": 16902,
                        "advance_count": 32
                      }
                    }
                  ]
                },
                {
                  "type": "ConstantScoreQuery",
                  "description": "ConstantScore(_type:customer-review)",
                  "time": "0.5018580000ms",
                  "time_in_nanos": 501858,
                  "breakdown": {
                    "score": 0,
                    "build_scorer_count": 144,
                    "match_count": 0,
                    "create_weight": 3146,
                    "next_doc": 0,
                    "match": 0,
                    "create_weight_count": 1,
                    "next_doc_count": 0,
                    "score_count": 0,
                    "build_scorer": 297151,
                    "advance": 201380,
                    "advance_count": 36
                  },
                  "children": [
                    {
                      "type": "TermQuery",
                      "description": "_type:customer-review",
                      "time": "0.3674510000ms",
                      "time_in_nanos": 367451,
                      "breakdown": {
                        "score": 0,
                        "build_scorer_count": 144,
                        "match_count": 0,
                        "create_weight": 1520,
                        "next_doc": 0,
                        "match": 0,
                        "create_weight_count": 1,
                        "next_doc_count": 0,
                        "score_count": 0,
                        "build_scorer": 191437,
                        "advance": 174313,
                        "advance_count": 36
                      }
                    }
                  ]
                }
              ]
            }
          ],
          "rewrite_time": 174588,
          "collector": [
            {
              "name": "CancellableCollector",
              "reason": "search_cancelled",
              "time": "0.4277380000ms",
              "time_in_nanos": 427738,
              "children": [
                {
                  "name": "SimpleFieldCollector",
                  "reason": "search_top_hits",
                  "time": "0.3255390000ms",
                  "time_in_nanos": 325539
                }
              ]
            }
          ]
        }
      ],
      "aggregations": []
    }

As you see type is null for status filter, any one know why type is null?


(system) #3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.