# Why is my query slow?
**URL:** https://discuss.elastic.co/t/why-is-my-query-slow/52752
**Category:** Elasticsearch
**Created:** [June 14, 2016, 3:05pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752 "2016-06-14T15:05:16Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@TroyAndAbed](https://discuss.elastic.co/u/TroyAndAbed)
#### Post date: [June 14, 2016, 3:05pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/1 "2016-06-14T15:05:16Z")
Hi,
The following query is taking around 2s to complete. It seems very slow knowing that I will need to search for far more documents.
The query:
```
POST /mail_logs/_search
{
"fields": [
"MAIL_ID",
"SENDER",
"RECEIVER",
"STATUS",
"INSTANCE",
"SERVER"
],
"size":10000,
"query": {
"filtered": {
"filter": {
"bool": {
"must": [
{
"range" : {
"CREATEDTIME" : {
"gte":"2016-01-29 00:00:00",
"lt" :"2016-01-30 00:00:00"
}
}
},
{
"query": {
"query_string" : {
"default_field" : "STATUS.raw",
"query" : "sent OR bounced OR deferred OR \"Email moved into the appropriate queue\""
}
}
}
]
}
}
}
}
}
```
The response:
```
{
"took": 1968,
"timed_out": false,
"_shards": {
"total": 3,
"successful": 3,
"failed": 0
},
"hits": {
"total": 190090,
"max_score": 1,
"hits": [
...
]
}
}
```
Others information:
Elasticsearch 2.3.3
There is 10M documents in the index.
3 shards.
16GB RAM.
How can I improve the performance of my query?
Thank you.
---
### Author:  [@TroyAndAbed](https://discuss.elastic.co/u/TroyAndAbed)
#### Post date: [June 14, 2016, 3:06pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/2 "2016-06-14T15:06:05Z")
The mapping:
```
{
"mail_logs": {
"mappings": {
"jdbc": {
"properties": {
"CREATEDTIME": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss"
},
"ERROR": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"FLAG_ALERT": {
"type": "long"
},
"INSTANCE": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"MAIL_ID": {
"type": "string"
},
"MESSAGE_ID": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"RECEIVER": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"RELAY": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"SENDER": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"SERVER": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
},
"STATUS": {
"type": "string",
"fields": {
"raw": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
}
}
}
}
```
Elasticsearch.yml:
> script.engine.groovy.inline.aggs: on
> script.engine.groovy.inline.update: on
> script.engine.groovy.inline.search: on
> script.groovy.sandbox.enabled: true
> index.codec: best\_compression
> indices.fielddata.cache.size: 20%
> node.master: true
> node.data: true
> bootstrap.mlockall: true
> discovery.zen.ping.unicast.hosts: ["127.0.0.1"]
> marvel.agent.exporters:
> id1:
> type: http
> host: ["[http://localhost:9200](http://localhost:9200)"]
> marvel.agent.interval: 120s
> index.merge.scheduler.max\_thread\_count: 1
---
### Author:  [@Glen\_Smith](https://discuss.elastic.co/u/Glen_Smith)
#### Post date: [June 14, 2016, 11:01pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/3 "2016-06-14T23:01:29Z")
Take a look at [the Profile API](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-profile.html)
---
### Author:  [@forloop](https://discuss.elastic.co/u/forloop)
#### Post date: [June 16, 2016, 6:58am UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/4 "2016-06-16T06:58:42Z")
> [@TroyAndAbed](#):
>
> "size":10000,
Do you _need_ 10,000 hits in one response? Could you paginate or use the scroll API here?
---
### Author:  [@TroyAndAbed](https://discuss.elastic.co/u/TroyAndAbed)
#### Post date: [June 16, 2016, 12:58pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/5 "2016-06-16T12:58:27Z")
I actually use the scoll api. But i need to scroll through 190 000 docs. And it takes 25 seconds to finish.
---
### Author:  [@forloop](https://discuss.elastic.co/u/forloop)
#### Post date: [June 17, 2016, 5:19am UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/6 "2016-06-17T05:19:53Z")
As @Glen_Smith noted, the [Profile API](https://www.elastic.co/guide/en/elasticsearch/reference/2.3/search-profile.html) with Elasticsearch 2.2+ should provide more details over which parts are taking the most time.
[You're using a filtered query filter in the query; I'd update this to use bool filter as recommended in the docs.](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-filtered-query.html) Additionally, since you're doing OR operations on a `not_analyzed` string field in the query string query, could you use a [`terms` query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html) here? Are you looking for **exact** matches to the provided terms? And should scores be computed for matching documents based on this query, or should they simply match or not match i.e. should this simply be a filter? I suspect you don't want scores computed.
Moving to a terms query in the filter context and converting to a bool filter, the query would look like
```
{
"size": 10000,
"fields": [
"MAIL_ID",
"SENDER",
"RECEIVER",
"STATUS",
"INSTANCE",
"SERVER"
],
"query": {
"bool": {
"filter": [
{
"range": {
"CREATEDTIME": {
"gte": "2016-01-29 00:00:00",
"lt": "2016-01-30 00:00:00"
}
}
},
{
"terms": {
"STATUS.raw": [
"sent",
"bounced",
"deferred",
"Email moved into the appropriate queue"
]
}
}
]
}
}
}
```
---
### Author:  [@TroyAndAbed](https://discuss.elastic.co/u/TroyAndAbed)
#### Post date: [June 17, 2016, 2:13pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/7 "2016-06-17T14:13:43Z")
No, I don't need the score. I tried your version but I didn't see a huge amelioration.
I used the profile API but I'm not sure how to interpret it: (I didn't put the data for the 2 others shards but they are pretty similar to the first one)
```
"profile": {
"shards": [
{
"id": "[RgUxGA4YRzm3nXrjwLe5zQ][mail_logs][0]",
"searches": [
{
"query": [
{
"query_type": "BooleanQuery",
"lucene": "#CREATEDTIME:[1454025600000 TO 1454112000000} #ConstantScore(STATUS.raw:Email moved into the appropriate queue STATUS.raw:bounced STATUS.raw:deferred STATUS.raw:sent)",
"time": "48.12477700ms",
"breakdown": {
"match": 0,
"score": 2815607,
"create_weight": 945792,
"build_scorer": 1327916,
"next_doc": 27191608,
"advance": 0
},
"children": [
{
"query_type": "MultiTermQueryConstantScoreWrapper",
"lucene": "CREATEDTIME:[1454025600000 TO 1454112000000}",
"time": "9.296138000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 2026,
"build_scorer": 1291446,
"next_doc": 5322958,
"advance": 2679708
}
},
{
"query_type": "ConstantScoreQuery",
"lucene": "ConstantScore(STATUS.raw:Email moved into the appropriate queue STATUS.raw:bounced STATUS.raw:deferred STATUS.raw:sent)",
"time": "6.547716000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 885008,
"build_scorer": 10536,
"next_doc": 0,
"advance": 4026820
},
"children": [
{
"query_type": "BooleanQuery",
"lucene": "STATUS.raw:Email moved into the appropriate queue STATUS.raw:bounced STATUS.raw:deferred STATUS.raw:sent",
"time": "1.625352000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 857048,
"build_scorer": 0,
"next_doc": 0,
"advance": 0
},
"children": [
{
"query_type": "TermQuery",
"lucene": "STATUS.raw:Email moved into the appropriate queue",
"time": "0.3468720000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 346872,
"build_scorer": 0,
"next_doc": 0,
"advance": 0
}
},
{
"query_type": "TermQuery",
"lucene": "STATUS.raw:bounced",
"time": "0.1316970000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 131697,
"build_scorer": 0,
"next_doc": 0,
"advance": 0
}
},
{
"query_type": "TermQuery",
"lucene": "STATUS.raw:deferred",
"time": "0.1430440000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 143044,
"build_scorer": 0,
"next_doc": 0,
"advance": 0
}
},
{
"query_type": "TermQuery",
"lucene": "STATUS.raw:sent",
"time": "0.1466910000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 146691,
"build_scorer": 0,
"next_doc": 0,
"advance": 0
}
}
]
}
]
}
]
},
{
"query_type": "BooleanQuery",
"lucene": "",
"time": "0.05389400000ms",
"breakdown": {
"match": 0,
"score": 0,
"create_weight": 3647,
"build_scorer": 50247,
"next_doc": 0,
"advance": 0
}
},
{
and 18 others BooleanQuery...
}
],
"rewrite_time": 164923,
"collector": [
{
"name": "SimpleTopScoreDocCollector",
"reason": "search_top_hits",
"time": "18.13502800ms"
}
]
}
]
}
]
}
```
---
### Author:  [@TroyAndAbed](https://discuss.elastic.co/u/TroyAndAbed)
#### Post date: [June 20, 2016, 1:41pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/8 "2016-06-20T13:41:20Z")
I started again from a fresh install of ElasticSearch. The time got down to 200ms. So i guess the problem was from some config.
Thanks for the help.
---
### Author:  [@forloop](https://discuss.elastic.co/u/forloop)
#### Post date: [June 21, 2016, 4:43am UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/9 "2016-06-21T04:43:14Z")
No worries, glad you got it sorted 🙂
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 10:41pm UTC](https://discuss.elastic.co/t/why-is-my-query-slow/52752/10 "2017-07-05T22:41:55Z")