elasticsearch bool filter should clause performance <p>first query:</p> <pre><code> { "query" : { "filtered" : { "filter" : { "bool" : { "must" : [ {"term" : {"user1_id" : "1"}}, {"range" : {"day" : {"gte" : "2015-04-01", "lte" : "2015-04-30"}}} ] } } } } </code></pre> <p>In relational SQL, the first query equals to SQL:</p> <pre><code>select * from table where user1_id =1 and (day&gt;=2015-04-01 and day &lt;= 2015-04-30) </code></pre> <p>Second query:</p> <pre><code>{ "query" : { "filtered" : { "filter" : { "bool" : { "should" : [{"term" : {"user1_id" : "1"}}, {"term" : {"user2_id" : "1"}}], "must" : {"range" : {"day" : {"gte" : "2015-04-02", "lte" : "2015-04-30"}} }, "_cache" : true } } } } } </code></pre> <p>In relational SQL, the second query equals to SQL: </p> <pre><code>select * from table where (user1_id =1 or user2_id = 1) and (day&gt;=2015-04-01 and day &lt;= 2015-04-30) </code></pre> <p>The difference is, </p> <p>the first query only considers <code>user1_id</code>, while the second one also check <code>user2_id</code>.</p> <p>both <code>user1_id</code> and <code>user2_id</code> are indexed.</p> <p>However, the performance differs a lot. </p> <p>the first query took 5 seconds, while the second one took 20 seconds.</p> <p>Is there any better way for the second query?</p> <p>Mapping:</p> <pre><code> user1_id: type: "string" index: "not_analyzed" doc_values: true user2_id: type: "string" index: "not_analyzed" doc_values: true day: type: "date" format: "YYYY-MM-dd" ignore_malformed: true doc_values: true </code></pre> <p>If I run the first query twice, the first time is <code>user1_id</code>, the second time is <code>user2_id</code>, then combine two results. It is much faster than using the second query. </p> <p>Have 10 Billion documents in total. AWS, m2.4xlarge, 8 instances in total. </p> <p><code>/usr/bin/java -Xms30g -Xmx30g</code> </p>