# First time query operations are pretty slow

**URL:** <https://discuss.elastic.co/t/first-time-query-operations-are-pretty-slow/201760>\
**Category:** Elasticsearch\
**Created:** [October 1, 2019, 9:06am UTC](https://discuss.elastic.co/t/first-time-query-operations-are-pretty-slow/201760 "2019-10-01T09:06:26Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sudheervarmam](https://avatars.discourse-cdn.com/v4/letter/s/85f322/32.png) [@sudheervarmam](https://discuss.elastic.co/u/sudheervarmam)\
**Post date:** [October 1, 2019, 9:06am UTC](https://discuss.elastic.co/t/first-time-query-operations-are-pretty-slow/201760/1 "2019-10-01T09:06:26Z")

</div>

For the first time elasticsearch query time is too long on a collection with 400+ million documents.The problem basically is "first time query operations are pretty slow".  
However, we want to understand the ROOT CAUSE and unable to identify the same.

My setup consists of:

> 2 nodes with 8 cores and 16 GB RAM each and 5 GB allocated to Elasticsearch.  
> 500 GB hard disk  
> doc size : 9kb

Top Command screenshots

![ES](https://us1.discourse-cdn.com/elastic/original/3X/b/f/bfa7215157b8f0b6a586130c52ce4a048eab6c0c.png)

I'm using this kind of mapping

```
{
  "event": {
    "mappings": {
      "doc": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "event": {
            "properties": {
              "contextProperties": {
                "properties": {
                  "carId": {
                    "type": "text",
                    "fields": {
                      "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                      }
                    }
                  }
                }
              },
              "eventName": {
                "type": "keyword",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "projectId": {
                "type": "keyword",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "time": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "ts": {
                "type": "long"
              },
              "userId": {
                "type": "keyword",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              },
              "value": {
                "type": "long"
              }
            }
          },
          "id": {
            "type": "keyword",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "timestamp": {
            "type": "date"
          },
          "type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

```

And the type of query I usually make:

```
{
  "aggs": {
    "catView": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "1d",
        "time_zone": "Asia/Kolkata",
        "min_doc_count": 1
      },
      "aggs": {
        "sub_Data": {
          "terms": {
            "field": "event.contextProperties.carId.keyword",
            "size": 13
          },
          "aggs": {
            "mou_Count": {
              "sum": {
                "field": "event.value"
              }
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "match_phrase": {
                  "event.contextProperties.carId.keyword": "12345"
                }
              },
              {
                "match_phrase": {
                  "event.contextProperties.carId.keyword": "NA"
                }
              }
            ]
          }
        },
        {
          "match_phrase": {
            "event.projectId.keyword": {
              "query": "123456"
            }
          }
        },
        {
          "range": {
            "timestamp": {
              "gte": 1569349800000,
              "lte": 1569868200000,
              "format": "epoch_millis"
            }
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "match_phrase": {
                  "event.contextProperties.carId.keyword": "45678"
                }
              },
              {
                "match_phrase": {
                  "event.contextProperties.carId.keyword": "NA"
                }
              }
            ]
          }
        },
        {
          "match_phrase": {
            "event.projectId.keyword": {
              "query": "8765"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        },
        {
          "match_all": {}
        }
      ],
      "should": []
    }
  },
  "timeout": "30000ms"
}

```

On this query it has to search on the fields carouselId, projectId, eventName and timestamp and doing the aggregation on value.

Any recommendation for boosting/caching the first query is welcome. Thanks in advance

I’m using Elasticsearch 6.8.3.

---

<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:** [October 29, 2019, 9:06am UTC](https://discuss.elastic.co/t/first-time-query-operations-are-pretty-slow/201760/2 "2019-10-29T09:06:28Z")

</div>

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