# Terms lookup mechanism cause too\_many\_clauses exception

**URL:** <https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766>\
**Category:** Elasticsearch\
**Created:** [November 30, 2017, 2:19pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766 "2017-11-30T14:19:03Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [November 30, 2017, 2:19pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/1 "2017-11-30T14:19:04Z")

</div>

Hi guys,

I have a problem with big queries using the Terms lookup mechanism.

@see [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html#query-dsl-terms-lookup](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html#query-dsl-terms-lookup)

As we have more and more customers (it's a good news) we have more and more values.  
I 'm trying to refactor our queries and I found the Terms lookup mechanism, it's very  
interesting in our case. BUT, the "lookup" values may be bigger, the max size actually is 118195 keys for 1 user.

So when I add the terms query, I have the following error (that's normal I understand)  
Caused by: NotSerializableExceptionWrapper[too\_many\_clauses: maxClauseCount is set to 1024]

If I change the configuration a big size it's works OK, but is it a good solution ? What problem we can have ?  
Some of you have a hight value for index.query.bool.max\_clause\_count: 100000 ?

bye,  
Xavier

---

<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 30, 2017, 2:56pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/2 "2017-11-30T14:56:57Z")

</div>

What is the problem you are solving with all these terms? What does the terms represent?

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [November 30, 2017, 3:13pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/3 "2017-11-30T15:13:11Z")

</div>

Hi Christian,

Here is the "project" :  
Let's say we have products in a index, and imagine that customers can "subcribe" to products.  
A customer can search his products, so I use the Terms lookup mechanism as a Join between products  
and user selections. The problem is that some users have suscribed to more than 1024 products.  
If it was a super simple query, I would run multiple queries with splitted subscriptions, but the exisiting query  
is already so big and complex that it's not possible to do that.

NOTE: The solution we have actually is that the product index has a field with userid, but I 'm looking for another architecture.

Xavier

---

<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 30, 2017, 3:22pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/4 "2017-11-30T15:22:49Z")

</div>

Maybe you could use parent-child to maintain the relationship. Let the product document be the parent and create a child document per user that subscribed to this product. Add a child to subscribe and remove it to unsubscribe.

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [November 30, 2017, 3:30pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/5 "2017-11-30T15:30:25Z")

</div>

> [@Christian\_Dahlqvist](#):
>
> Maybe you could use parent-child to maintain the relationship. Let the product document be the parent and create a child document per user that subscribed to this product. Add a child to subscribe and remove it to unsubscribe.

I have questions about this solution:  
First, you need to know that we run full reindexations and index rolling every weekend.

1°/ would you create a child document per user in the product index or into another. If you mean into the product, it's the same solution we already have , and that's why we want to change (fullreindex is to long because the query is superrrrrr long)

2°/ Is it a problem to have a parent-child relationchip between indices that are dropped and reindexed ? (But they are knowned by a fix alias)

thx !

---

<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 30, 2017, 3:32pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/6 "2017-11-30T15:32:37Z")

</div>

Well, that is new information. Why are you reindexing and rolling indices every weekend?

> [@xavierfacq](#):
>
> 1°/ would you create a child document per user in the product index or into another. If you mean into the product, it's the same solution we already have , and that's why we want to change (fullreindex is to long because the query is superrrrrr long)

Parent-child requires it to be in the same index.

> [@xavierfacq](#):
>
> 2°/ Is it a problem to have a parent-child relationchip between indices that are dropped and reindexed ? (But they are knowned by a fix alias)

The parent and all associated children must reside in the shard.

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [November 30, 2017, 3:36pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/7 "2017-11-30T15:36:00Z")

</div>

> [@Christian\_Dahlqvist](#):
>
> Parent-child requires it to be in the same

Yes that's what I just read, and it's not possible ☹

I'll search another solution because setting index.query.bool.max\_clause\_count: 100000  
is probably not a solution...

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 1, 2017, 8:08am UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/8 "2017-12-01T08:08:23Z")

</div>

> [@xavierfacq](#):
>
> index.query.bool.max\_clause\_count: 100000

@Christian_Dahlqvist Do you have a recommendation about the max limit for this field ? It's Long ids in our case... And the biggeste array is about 118K :-/

---

<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:** [December 1, 2017, 8:12am UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/9 "2017-12-01T08:12:00Z")

</div>

No, I have never tried anything at that scale.

How come you are reindexing and rolling indices every weekend? Is there no way to enhance this process to allow a different solution to the problem?

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 1, 2017, 8:20am UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/10 "2017-12-01T08:20:55Z")

</div>

Ok, thank you.

Reindexing are done every weekend (and partial in the night) because there are changes not synchronized from our MySQL database. For old crappy reasons 😉

thx,  
Xavier

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 1, 2017, 12:26pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/11 "2017-12-01T12:26:47Z")

</div>

FYI: [https://wiki.apache.org/lucene-java/LuceneFAQ#Why\_am\_I\_getting\_a\_TooManyClauses\_exception.3F](https://wiki.apache.org/lucene-java/LuceneFAQ#Why_am_I_getting_a_TooManyClauses_exception.3F)

_Increase the number of terms using BooleanQuery.setMaxClauseCount(). Note that this will increase the memory requirements for searches that expand to many terms. To deactivate any limits, use BooleanQuery.setMaxClauseCount(Integer.MAX\_VALUE)_.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [December 1, 2017, 2:24pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/12 "2017-12-01T14:24:27Z")

</div>

I would discourage to increase the maximum number of clauses, it tends to be a source of problem as Lucene may need to read from {max\_clause\_count} locations on the disk in parallel.

In general, `terms` queries are only subject to the maximum clause count if their score is required. So I'm wondering that you could work around your issue by just putting your `terms` query in a filter context, such as under a `constant_score` query or in a `bool` `filter` clause (assuming that you don't need scores for that query)?

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 1, 2017, 3:08pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/13 "2017-12-01T15:08:54Z")

</div>

> [@jpountz](#):
>
> In general, terms queries are only subject to the maximum clause count if their score is required. So I'm wondering that you could work around your issue by just putting your terms query in a filter context, such as under a constant\_score query or in a bool filter clause (assuming that you don't need scores for that query)?

You are right, changing a query terms into a bool.filter.terms does not throw a too\_many\_clauses exception anymore.

(Having an index with a doc and a field "concurrent\_company\_ids" \> 1024 values)  
This cause an exception:

```
  {
    "from": 0,
    "size": 10,
    "query": {
      "terms": {
	"external_company_id": {
	  "index": "index-user-territory",
	  "type": "user_territory",
	  "id": "85",
	  "path": "concurrent_company_ids"
	}
      }
    }
  }

```

This one doesn't:

```
  {
    "from": 0,
    "size": 10,
    "query": {
      "bool": {
	"filter": [
	  {
	    "terms": {
	      "external_company_id": {
		"index": "index-user-territory",
		"type": "user_territory",
		"id": "85",
		"path": "concurrent_company_ids"
	      }
	    }
	  }
	]
      }
    }
  }

```

Now, I have to try to change all queries to filtered or constant\_score... it's going to be tough ☹

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [December 1, 2017, 4:55pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/14 "2017-12-01T16:55:35Z")

</div>

Cool, thanks for bringing closure!

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 11, 2017, 3:11pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/15 "2017-12-11T15:11:43Z")

</div>

Note that it also works when the terms lookup is encapsulated into a constant\_score query. 👍

thx,  
Xavier

---

<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:** [January 8, 2018, 3:12pm UTC](https://discuss.elastic.co/t/terms-lookup-mechanism-cause-too-many-clauses-exception/109766/16 "2018-01-08T15:12:04Z")

</div>

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