title: elasticsearch bool filter should clause performance
body: <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>