# Numeric term(and terms) query much slower after 5.6.15 -\> 7.1.1 upgrade

**URL:** <https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894>\
**Category:** Elasticsearch\
**Created:** [March 10, 2020, 9:32am UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894 "2020-03-10T09:32:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![hyf\_bd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyf_bd/32/59911_2.png) [@hyf\_bd](https://discuss.elastic.co/u/hyf_bd)\
**Post date:** [March 10, 2020, 9:32am UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/1 "2020-03-10T09:32:54Z")

</div>

the same settings, mapping , query, and the data. all of things is the same, except es version.

the follow query cost 400+ms in es5, but cost 600+ms in es7.  
we compare the profile, had found that:

1. term query ( terms also) of numeric in es 7 is much slow than es5.
2. match query in es 7 is better than es5.

how we should improve in es7 ??

some body suggest change numeric type to keyword, but it's not take effect.

Looking forward to your reply， thanks

**settings (partially):**

```
      "analysis" : {
          "filter" : {
            "my_shingle_filter" : {
              "max_shingle_size" : "2",
              "min_shingle_size" : "2",
              "output_unigrams" : "false",
              "type" : "shingle"
            }
          },
          "analyzer" : {
            "my_shingle_analyzer" : {
              "filter" : [
                "my_shingle_filter",
                "lowercase"
              ],
              "type" : "custom",
              "tokenizer" : "whitespace"
            }
          }
        },

```

**mapping** :

```
   "mappings" : {
      "properties" : {
        "ai_tags" : {
          "type" : "long"
        },
        "album_meta" : {
          "properties" : {
            "episode_one" : {
              "type" : "long"
            },
            "tags" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "title" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "type" : {
              "type" : "long"
            }
          }
        },
        "chosen" : {
          "type" : "boolean"
        },
        "comment_count" : {
          "type" : "long"
        },
        "create_time" : {
          "type" : "long"
        },
        "create_type" : {
          "type" : "long"
        },
        "digg_count" : {
          "type" : "long"
        },
        "favorite_count" : {
          "type" : "long"
        },
        "go_detail_count" : {
          "type" : "long"
        },
        "hashtags" : {
          "type" : "keyword"
          }
        },
        "heat" : {
          "type" : "integer"
        },
        "human_tags" : {
          "type" : "long"
        },
        "id" : {
          "type" : "long"
        },
        "impr_count" : {
          "type" : "long"
        },
        "language" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "level" : {
          "type" : "long"
        },
        "link_level" : {
          "type" : "integer"
        },
        "link_title_terms" : {
          "type" : "text",
          "fields" : {
            "shingles" : {
              "type" : "text",
              "analyzer" : "my_shingle_analyzer"
            }
          },
          "analyzer" : "whitespace"
        },
        "media_type" : {
          "type" : "integer"
        },
        "post_source" : {
          "type" : "long"
        },
        "region" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "share_count" : {
          "type" : "long"
        },
        "sim_id" : {
          "type" : "long"
        },
        "source" : {
          "type" : "integer"
        },
        "status" : {
          "type" : "integer"
        },
        "text_terms" : {
          "type" : "text",
          "fields" : {
            "shingles" : {
              "type" : "text",
              "analyzer" : "my_shingle_analyzer"
            }
          },
          "analyzer" : "whitespace"
        },
        "update_time" : {
          "type" : "long"
        },
        "user_id" : {
          "type" : "long"
        },
        "user_status" : {
          "type" : "integer"
        },
        "username_terms" : {
          "type" : "text",
          "fields" : {
            "shingles" : {
              "type" : "text",
              "analyzer" : "my_shingle_analyzer"
            }
          },
          "analyzer" : "whitespace"
        }
      }
    }
  }

```

**query:**  
time cost of each step: left is es7, right is es5;  
total cost es7 600+ms, es5 400+ms

```
 "profile": true,
  "from": 0,
  "query": {
    "function_score": {
      "boost_mode": "multiply",
      "functions": [
        {
          "field_value_factor": {
            "field": "favorite_count",
            "missing": 1,
            "modifier": "ln2p"
          }
        },
        {
          "field_value_factor": {
            "field": "digg_count",
            "missing": 1,
            "modifier": "log2p"
          }
        }
      ],
      "query": {
        "bool": {
          "filter": {
            "terms": {
              "media_type": [ //time cost: 143ms ->89ms
                3,
                5
              ]
            }
          },
          "minimum_should_match": "0",
          "must": [
            {
              "term": {
                "status": 3 //time cost: 82ms -->45ms
              }
            },
            {
              "term": {
                "user_status": 100 //time cost: 72ms -->35ms
              }
            },
            {
              "bool": {
                "minimum_should_match": "1",
                "should": [ //time cost: 148ms -->212ms
                  {
                    "match": {
                      "text_terms": {               
                        "boost": 100000,
                        "query": "微信 动态 搞笑 图片 500张"
                      }
                    }
                  },
                  {
                    "match": {
                      "username_terms": {            
                        "query": "微 信 动 态 搞 笑 图 片 5 0 0 张 "
                      }
                    }
                  },
                  {
                    "match": {
                      "hashtags": {
                        "query": "微信动态搞笑图片500张" //time cost: very little， can be ignore
                      }
                    }
                  },
                  {
                    "match": {
                      "hashtags.keyword": {
                        "query": "微信动态搞笑图片500张"
                      }
                    }
                  }
                ]
              }
            }
          ],
          "must_not": [
            {
              "range": {
                "link_level": { //161 ms -> 33ms
                  "from": null,
                  "include_lower": true,
                  "include_upper": false,
                  "to": 5
                }
              }
            },
            {
              "terms": {
                "create_type": [ //0.2 ms --> 0.6ms
                  100,
                  102,
                  103,
                  104
                ]
              }
            },
            {
              "term": {
                "media_type": 5 //time cost: very little， can be ignore
              }
            }
          ]
        }
      },
      "score_mode": "multiply"
    }
  },
  "size": 3000
}

```

**profile:**  
es5\_profile  
[es 5 profile(cost 400+ms)](https://github.com/huyfaeng/ES-debug/wiki/es5_profile)  
es7\_profile

[es 7 profile (cost 600+ms)](https://github.com/huyfaeng/ES-debug/wiki/es7_profile)

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [March 10, 2020, 11:10am UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/2 "2020-03-10T11:10:12Z")

</div>

> [@hyf\_bd](#):
>
> some body suggest change numeric type to keyword, but it's not take effect.

It didn't improve performance or you weren't able to change the mapping?  
Media-type looks like it should be a `keyword` field (numbers like integer are optimised for range queries not exact-match).

---

<div class="post-metadata">

**Author:** ![hyf\_bd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyf_bd/32/59911_2.png) [@hyf\_bd](https://discuss.elastic.co/u/hyf_bd)\
**Post date:** [March 10, 2020, 12:35pm UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/3 "2020-03-10T12:35:23Z")

</div>

> [@Mark\_Harwood](#):
>
> Media-type looks like it should be a `keyword` field (numbers like integer are optimised for range queries not exact-match).

we had tried it, but it's more slower than integer. especially this sub query

```
        "filter": {
            "terms": {
              "media_type": [ //time cost: 143ms ->89ms
                3,
                5
              ]
            }
          },

```

this sub query will cost 300+ms, if it's integer type, it cost 140+ms only. if in es5, it 's cost 89ms.

this sub query change as follow will be better, if it was keyword type, it cost 160+ms

```
 "filter": {
        "bool": {
        "should":[
               { "term":{ "media_type": 3 } }, 
              { "term":{ "media_type": 5 } }
          ]
      }
}

```

but if status and user\_status change to keyword, it will quick a little, "status: 3" cost 70+ms, "user\_status: 100" cost 60+ms, but it's still slower than es5.

```
        "must": [
            {
              "term": {
                "status": 3 //time cost: 82ms -->45ms
              }
            },
            {
              "term": {
                "user_status": 100 //time cost: 72ms -->35ms
              }
            },

```

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [March 10, 2020, 3:58pm UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/4 "2020-03-10T15:58:41Z")

</div>

We're always looking for regressions in our [nightly benchmarks](https://elasticsearch-benchmarks.elastic.co/#tracks/http-logs/release) and this is not one we've observed.

What's the cardinality of the fields you tested? I can't imagine there's that many different media types.

---

<div class="post-metadata">

**Author:** ![zxx\_bd](https://avatars.discourse-cdn.com/v4/letter/z/c2a13f/32.png) [@zxx\_bd](https://discuss.elastic.co/u/zxx_bd)\
**Post date:** [March 11, 2020, 6:50am UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/5 "2020-03-11T06:50:43Z")

</div>

For the cardinality,  
user\_status: 4  
status: 7  
media\_type: 6  
and there are about 69, 000, 000 documents in the index

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [March 11, 2020, 9:44am UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/6 "2020-03-11T09:44:59Z")

</div>

Thanks for that. Just ran my own `terms` search benchmarks [here](https://gist.github.com/markharwood/bd74538560f6a2be2ad0772a747e41be) with longs vs keywords and 7.6.1 vs 5.6.0.

In all cases: 7.6.1 is better than 5.6.0 and keywords are better than longs.

---

<div class="post-metadata">

**Author:** ![hyf\_bd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyf_bd/32/59911_2.png) [@hyf\_bd](https://discuss.elastic.co/u/hyf_bd)\
**Post date:** [March 11, 2020, 1:32pm UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/7 "2020-03-11T13:32:55Z")

</div>

> [@Mark\_Harwood](#):
>
> Thanks for that. Just ran my own `terms` search benchmarks [here](https://gist.github.com/markharwood/bd74538560f6a2be2ad0772a747e41be) with longs vs keywords and 7.6.1 vs 5.6.0.

"my\_id": qTerms # V5.6.0 =3.3s V7.6.1 =1.2s

3ms in es5, 1.2ms in es7 per request ??

we try again, delete other sub query, only remain terms query. but it's cost between 100ms ~200ms in es5.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/5/256fb1dbe9449f829580eb593b7c2f4e9a8a43a8.png)

there are some difference with your benchmarks:

1. the doc count of each media\_type is 11,000,000. your's 200,000
2. the type of media\_type is integer, your's long

besides, can your compare 5.6 with 7.1? may be 7.1 and 7.6 have some difference.

thanks.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [March 11, 2020, 2:30pm UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/8 "2020-03-11T14:30:23Z")

</div>

> [@hyf\_bd](#):
>
> ms in es5, 1.2ms in es7 per request ??

No, 3.3s as in 3.3 seconds to complete the 1,000 searches on random choices of value.  
Timings taken after repeated runs (once file system cache has kicked in and response times for a test config stabilise).

> [@hyf\_bd](#):
>
> there are some difference with your benchmarks:

I tried with integers and 7.1 is roughly similar to 7.6.1.  
When it comes to 5.6 vs 7.x there's a big speed up in relation to `track_total_hits` defaulting to False (newer versions of elasticsearch accelerate matching if it knows you have no aggregations and only need approximate numbers of matches). I updated my test to turn this optimisation off for 7.x tests. Even with this flag set to "true" (the slower mode) 7.x is faster than 5.6 for this test.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 8, 2020, 2:30pm UTC](https://discuss.elastic.co/t/numeric-term-and-terms-query-much-slower-after-5-6-15-7-1-1-upgrade/222894/9 "2020-04-08T14:30:29Z")

</div>

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