# Terms Query performance with increased number of terms

**URL:** <https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888>\
**Category:** Elasticsearch\
**Created:** [November 7, 2019, 4:48am UTC](https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888 "2019-11-07T04:48:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![nishant.saini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishant.saini/32/41489_2.png) [@nishant.saini](https://discuss.elastic.co/u/nishant.saini)\
**Post date:** [November 7, 2019, 4:48am UTC](https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888/1 "2019-11-07T04:48:08Z")

</div>

I would like to understand how the performance of terms query is effected by the number of terms passed in to the terms query. According to very old blogs and Q&A terms around 1000 should not cause issue but what if the number of terms are comparatively much more.

Our current elastic version is 2.4 and I'm using lookup mechanism to filter results. When number of terms are around 5000 I can see significant change in query performance. The query starts to take around 800ms. Is this expected behaviour?

Are there any measures I can take to improve the query performance?

Since we'll be migrating to 7.x , should I expect better performance for the same lookup query? I'm asking this because according to latest documentation as quoted below, by default the limit for number of terms is 65536.

> By default, Elasticsearch limits the `terms` query to a maximum of 65,536 terms. This includes terms fetched using terms lookup.

So should I expect that large number of terms will not slow down the query in latest version of elasticsearch?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 8, 2019, 6:26am UTC](https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888/2 "2019-11-08T06:26:16Z")

</div>

> [@nishant.saini](#):
>
> Our current elastic version is 2.4 and I'm using lookup mechanism to filter results. When number of terms are around 5000 I can see significant change in query performance. The query starts to take around 800ms. Is this expected behaviour?

I would say that it is. The more terms you pass in the more lookups and work Elasticsearch has to perform.

> [@nishant.saini](#):
>
> Since we'll be migrating to 7.x , should I expect better performance for the same lookup query? I'm asking this because according to latest documentation as quoted below, by default the limit for number of terms is 65536.

I would still expect increased number of terms to slow down the query. Whether performance for this type of query has improved or not I will need to leave for someone more familiar with the internals.

> [@nishant.saini](#):
>
> So should I expect that large number of terms will not slow down the query in latest version of elasticsearch?

Probably, but I would spin up two parallel test clusters, ingest the same anount of data into both and simply test it.

---

<div class="post-metadata">

**Author:** ![nishant.saini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishant.saini/32/41489_2.png) [@nishant.saini](https://discuss.elastic.co/u/nishant.saini)\
**Post date:** [November 8, 2019, 6:58am UTC](https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888/3 "2019-11-08T06:58:31Z")

</div>

Thanks for the response.

> [@Christian\_Dahlqvist](#):
>
> Probably, but I would spin up two parallel test clusters, ingest the same anount of data into both and simply test it.

Yes that is definitely an option but there might be a reason that elasticsearch came up with 65536 as the default number of terms. I'm curious to know the reason for this.

---

<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:** [December 6, 2019, 6:58am UTC](https://discuss.elastic.co/t/terms-query-performance-with-increased-number-of-terms/206888/4 "2019-12-06T06:58:37Z")

</div>

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