# Elasticsearch terms query is very slow?

**URL:** <https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169>\
**Category:** Elasticsearch\
**Created:** [February 23, 2021, 7:24am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169 "2021-02-23T07:24:35Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Karthick\_K](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karthick_k/32/84375_2.png) [@Karthick\_K](https://discuss.elastic.co/u/Karthick_K)\
**Post date:** [February 23, 2021, 7:24am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/1 "2021-02-23T07:24:36Z")

</div>

Elasticsearch cluster

```auto
Master node : 1 (16cpus, 128GB RAM) - 32GB is allocated for HEAP
Data nodes : 6 (each node 16cpus, 128GB RAM) - 32GB is allocated for 
HEAP for all nodes

Indices : 9 
Total documents : 48,826,456
Stored size : 2TB

```

This [query](https://pastebin.pl/view/e2334807) is very slow. Takes around `4seconds`

I have profiled it and found terms **profile\_id** in the filter is slow, Since terms array contains around 20k ids. `Without terms` it takes around `700ms`

Any suggestion to improve the performance will be grateful.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 23, 2021, 7:34am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/2 "2021-02-23T07:34:39Z")

</div>

That's, uh, a lot of terms. Why do you have so many?

---

<div class="post-metadata">

**Author:** ![Karthick\_K](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karthick_k/32/84375_2.png) [@Karthick\_K](https://discuss.elastic.co/u/Karthick_K)\
**Post date:** [February 23, 2021, 7:37am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/3 "2021-02-23T07:37:21Z")

</div>

Yes, We have to select only those matching profile\_ids document based on our application logic

---

<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:** [February 23, 2021, 7:42am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/4 "2021-02-23T07:42:32Z")

</div>

Disk seeks are expensive.  
Many terms = many disk seeks.  
You have over 20k query terms.

---

<div class="post-metadata">

**Author:** ![Karthick\_K](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karthick_k/32/84375_2.png) [@Karthick\_K](https://discuss.elastic.co/u/Karthick_K)\
**Post date:** [February 23, 2021, 8:31am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/5 "2021-02-23T08:31:07Z")

</div>

Okay, Any alternate way to fix this

---

<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:** [February 23, 2021, 9:24am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/6 "2021-02-23T09:24:25Z")

</div>

The only way to make an inherently slow task faster is through parallelism.

Use [routing](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-routing-field.html) to organise all data related to each profile on the same machine. Send searches with routing to target specific machines with queries that only contain profile IDs that are known to be stored on those machines. Send these in parallel from your client.

Without being quite so targeted/optimised you could try break a single search into several smaller bundles of profile IDs and run them in parallel. Each node may be looking for profile IDs it doesn't have but may help squeeze more speed out of each server if it is capable of servicing simultaneous requests well.

---

<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:** [March 23, 2021, 9:25am UTC](https://discuss.elastic.co/t/elasticsearch-terms-query-is-very-slow/265169/7 "2021-03-23T09:25:24Z")

</div>

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