# Profiling and optimizing queries

**URL:** <https://discuss.elastic.co/t/profiling-and-optimizing-queries/22593>\
**Category:** Elasticsearch\
**Created:** [March 10, 2015, 6:45am UTC](https://discuss.elastic.co/t/profiling-and-optimizing-queries/22593 "2015-03-10T06:45:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![unlucio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/unlucio/32/841_2.png) [@unlucio](https://discuss.elastic.co/u/unlucio)\
**Post date:** [March 10, 2015, 6:45am UTC](https://discuss.elastic.co/t/profiling-and-optimizing-queries/22593/1 "2015-03-10T06:45:22Z")

</div>

Hello everybody,  
In the attempt to optimize a query I followed the suggestions I found in  
this presentation:

> **[Elasticsearch Query Optimization](https://speakerdeck.com/polyfractal/elasticsearch-query-optimization)**
>
> Grab bag of tips to help improve your queries in Elasticsearch. Everything may not be applicable to your data/architecture, so feel free to skim and selectively steal tips :)

transforming my query form:

{  
"index": "products\_en",  
"from": 0,  
"size": 100,  
"q": "apple iphone AND visible:1",  
"\_source": [  
"sku",  
"name",  
"brand",  
"link",  
"maxImages",  
"imageKey",  
"sizes",  
"price"  
],  
"body": {  
"aggs": {  
"sim\_type": {  
"terms": {  
"field": "attributesFacets.sim\_type"  
}  
},  
"screen\_type": {  
"terms": {  
"field": "attributesFacets.screen\_type"  
}  
},  
"color": {  
"terms": {  
"field": "attributesFacets.color\_family",  
"size": 0  
}  
},  
"price": {  
"range": {  
"field": "price",  
"ranges": [  
{  
"to": 99  
},  
{  
"from": 100,  
"to": 149  
},  
{  
"from": 150,  
"to": 199  
},  
{  
"from": 200,  
"to": 299  
},  
{  
"from": 300  
}  
]  
}  
},  
"size": {  
"terms": {  
"field": "sizes"  
}  
},  
"brand": {  
"terms": {  
"field": "brand.key"  
}  
},  
"category": {  
"terms": {  
"field": "category"  
}  
}  
}  
}  
}

to

{  
"index": "products\_en",  
"from": 0,  
"size": 100,  
"body": {  
"query": {  
"filtered": {  
"query": {  
"match\_all": {}  
},  
"filter": {  
"bool": {  
"must": [  
{  
"term": {  
"search": "apple",  
"\_cached": true  
}  
},  
{  
"term": {  
"search": "iphone",  
"\_cached": true  
}  
}  
]  
}  
}  
}  
},  
"\_source": [  
"sku",  
"name",  
"brand",  
"link",  
"maxImages",  
"imageKey",  
"sizes",  
"price"  
],  
"aggs": {  
"sim\_type": {  
"terms": {  
"field": "attributesFacets.sim\_type"  
}  
},  
"screen\_type": {  
"terms": {  
"field": "attributesFacets.screen\_type"  
}  
},  
"color": {  
"terms": {  
"field": "attributesFacets.color\_family",  
"size": 0  
}  
},  
"price": {  
"range": {  
"field": "price",  
"ranges": [  
{  
"to": 99  
},  
{  
"from": 100,  
"to": 149  
},  
{  
"from": 150,  
"to": 199  
},  
{  
"from": 200,  
"to": 299  
},  
{  
"from": 300  
}  
]  
}  
},  
"size": {  
"terms": {  
"field": "sizes"  
}  
},  
"brand": {  
"terms": {  
"field": "brand.key"  
}  
},  
"category": {  
"terms": {  
"field": "category"  
}  
}  
}  
}  
}

without any apparent gain in performances.  
How can I profile my query so to understand where I'm spending the most  
time and then been able to optimize my data, query and eventually my  
mapping?

Goggling around I just found "sys admins" side insight on optimization  
(such as java memory configuration, using sdd etc), but what about  
optimizing data and queries?  
How do I know if my data mappings and queries aren't "screwed up"?

As ES noob from my reported optimization attempt just gave me an more  
complicated query but no performance benefit and now I'm not sure how to  
find out/understand what I'm doing wrong.

thanks for help

best  
lucio

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a1459f7c-2c39-491e-a368-af893856aff9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a1459f7c-2c39-491e-a368-af893856aff9%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![unlucio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/unlucio/32/841_2.png) [@unlucio](https://discuss.elastic.co/u/unlucio)\
**Post date:** [March 11, 2015, 6:06am UTC](https://discuss.elastic.co/t/profiling-and-optimizing-queries/22593/2 "2015-03-11T06:06:28Z")

</div>

Anyone have any idea? Or is just not possible? :((

On Tuesday, March 10, 2015 at 10:45:23 AM UTC+4, unlucio wrote:

> Hello everybody,  
> In the attempt to optimize a query I followed the suggestions I found in  
> this presentation:  
> [https://speakerdeck.com/polyfractal/elasticsearch-query-optimization](https://speakerdeck.com/polyfractal/elasticsearch-query-optimization)
> 
> transforming my query form:
> 
> {  
> "index": "products\_en",  
> "from": 0,  
> "size": 100,  
> "q": "apple iphone AND visible:1",  
> "\_source": [  
> "sku",  
> "name",  
> "brand",  
> "link",  
> "maxImages",  
> "imageKey",  
> "sizes",  
> "price"  
> ],  
> "body": {  
> "aggs": {  
> "sim\_type": {  
> "terms": {  
> "field": "attributesFacets.sim\_type"  
> }  
> },  
> "screen\_type": {  
> "terms": {  
> "field": "attributesFacets.screen\_type"  
> }  
> },  
> "color": {  
> "terms": {  
> "field": "attributesFacets.color\_family",  
> "size": 0  
> }  
> },  
> "price": {  
> "range": {  
> "field": "price",  
> "ranges": [  
> {  
> "to": 99  
> },  
> {  
> "from": 100,  
> "to": 149  
> },  
> {  
> "from": 150,  
> "to": 199  
> },  
> {  
> "from": 200,  
> "to": 299  
> },  
> {  
> "from": 300  
> }  
> ]  
> }  
> },  
> "size": {  
> "terms": {  
> "field": "sizes"  
> }  
> },  
> "brand": {  
> "terms": {  
> "field": "brand.key"  
> }  
> },  
> "category": {  
> "terms": {  
> "field": "category"  
> }  
> }  
> }  
> }  
> }
> 
> to
> 
> {  
> "index": "products\_en",  
> "from": 0,  
> "size": 100,  
> "body": {  
> "query": {  
> "filtered": {  
> "query": {  
> "match\_all": {}  
> },  
> "filter": {  
> "bool": {  
> "must": [  
> {  
> "term": {  
> "search": "apple",  
> "\_cached": true  
> }  
> },  
> {  
> "term": {  
> "search": "iphone",  
> "\_cached": true  
> }  
> }  
> ]  
> }  
> }  
> }  
> },  
> "\_source": [  
> "sku",  
> "name",  
> "brand",  
> "link",  
> "maxImages",  
> "imageKey",  
> "sizes",  
> "price"  
> ],  
> "aggs": {  
> "sim\_type": {  
> "terms": {  
> "field": "attributesFacets.sim\_type"  
> }  
> },  
> "screen\_type": {  
> "terms": {  
> "field": "attributesFacets.screen\_type"  
> }  
> },  
> "color": {  
> "terms": {  
> "field": "attributesFacets.color\_family",  
> "size": 0  
> }  
> },  
> "price": {  
> "range": {  
> "field": "price",  
> "ranges": [  
> {  
> "to": 99  
> },  
> {  
> "from": 100,  
> "to": 149  
> },  
> {  
> "from": 150,  
> "to": 199  
> },  
> {  
> "from": 200,  
> "to": 299  
> },  
> {  
> "from": 300  
> }  
> ]  
> }  
> },  
> "size": {  
> "terms": {  
> "field": "sizes"  
> }  
> },  
> "brand": {  
> "terms": {  
> "field": "brand.key"  
> }  
> },  
> "category": {  
> "terms": {  
> "field": "category"  
> }  
> }  
> }  
> }  
> }
> 
> without any apparent gain in performances.  
> How can I profile my query so to understand where I'm spending the most  
> time and then been able to optimize my data, query and eventually my  
> mapping?
> 
> Goggling around I just found "sys admins" side insight on optimization  
> (such as java memory configuration, using sdd etc), but what about  
> optimizing data and queries?  
> How do I know if my data mappings and queries aren't "screwed up"?
> 
> As ES noob from my reported optimization attempt just gave me an more  
> complicated query but no performance benefit and now I'm not sure how to  
> find out/understand what I'm doing wrong.
> 
> thanks for help
> 
> best  
> lucio

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5773cccd-6ef4-4fbe-9e9b-00e20edb33c4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5773cccd-6ef4-4fbe-9e9b-00e20edb33c4%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 12:27am UTC](https://discuss.elastic.co/t/profiling-and-optimizing-queries/22593/3 "2017-07-06T00:27:30Z")

</div>