# Slow terms aggregations **URL:** \ **Category:** Elasticsearch\ **Created:** [August 17, 2015, 11:37am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496 "2015-08-17T11:37:14Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 17, 2015, 11:37am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/1 "2015-08-17T11:37:15Z")
Hi, I have a problem with my ES deployment. My queries are using a few terms aggregations at once, and I have extremely low performance of queries (~8s for one query). Cluster: - Two machines (each with two CPU cores and 13GB of memory) - ES has 6 GB of memory available - SSD storage - ~9M documents My aggregations are basically facets for products catalog, so I have high cardinality fields (brands, categories, etc). I tried several configuration improvements (reducing number of shards, increasing memory, tuning cache) but I didn't manage to fix my issue. When I perform a query with facets, both machines are using their CPUs at 100%. Memory usage and I/O load are low too. My questions: - How to improve queries performance? I don't want to reduce number of used facets - Which approach is better for facetting - store unique IDs in documents or whole names (for example for categories) Regards, Artur ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 17, 2015, 11:37am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/2 "2015-08-17T11:37:52Z")
Just to check, are you using facets or aggs? ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 17, 2015, 11:39am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/3 "2015-08-17T11:39:55Z")
I'm using aggs. Example aggregations from my query: ``` "_filter_brand": { "aggs": { "brand": { "terms": { "field": "brand" } } }, "filter": { "match_all": {} } }, "_filter_categories": { "aggs": { "categories": { "terms": { "field": "category_ids", "size": 100 } } }, "filter": { "match_all": {} } } ``` Is there any way, to debug my queries? Explain returns only information why some results appeared as results. I need information about how long it takes to for example fetch results from another node/shard etc. ---
**Author:** ![msimos](https://avatars.discourse-cdn.com/v4/letter/m/bb73d2/32.png) [@msimos](https://discuss.elastic.co/u/msimos)\ **Post date:** [August 17, 2015, 4:54pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/4 "2015-08-17T16:54:08Z")
You can use the slow log to see the times for each phase: [https://www.elastic.co/guide/en/elasticsearch/reference/current/index-modules-slowlog.html#search-slow-log](https://www.elastic.co/guide/en/elasticsearch/reference/current/index-modules-slowlog.html#search-slow-log) It won't tell you why, but you can see how many shards were searched and how long it took. ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 18, 2015, 6:57am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/5 "2015-08-18T06:57:29Z")
My query has large result set, so always all shards are involved. I tried to reduce number of shards/replicas and now I have 4 shards without any replicas (2 shards per node). My query time reduced to 7s but it's still unacceptable time for a search query for me. I tried to split up my query into two queries (one for aggregations with count API and another with standard query) but always first query is very slow. Any ideas, what should I do to increase the performance? ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [August 18, 2015, 10:15am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/6 "2015-08-18T10:15:30Z")
It's hard to know what the problem could be without seeing your full query (if it's large please paste into a [gist](http://gist.github.com) rather than pasting it here and provide a link to it). Also, I would try removing each aggregation one at a time and see if the performance improve to determine which aggregation(s) is causing the slow performance ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 18, 2015, 11:07am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/7 "2015-08-18T11:07:34Z")
@colings86 here is my query: [https://gist.github.com/artursmet/4c4369cdcf40923fc3b2#file-slow\_query-json](https://gist.github.com/artursmet/4c4369cdcf40923fc3b2#file-slow_query-json) I tried to remove my aggregations one by one and I saw that my author\_ids and category\_ids aggregations are the slowest. They are arrays of integers (ids from another database). I tried to hash them at index time and there is a little improvement, but still it's not so fast. As I saw, the queries are using mostly CPU (both nodes have 100% cpu used, when my query is running) ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [August 18, 2015, 11:30am UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/8 "2015-08-18T11:30:20Z")
Why are you wrapping every aggregation in a filter aggregation which is a `match_all`? this is not going to make any difference to the result and only adds overhead to execution. How many unique `author_id`s and `category_id`s are in the index? ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 18, 2015, 12:21pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/9 "2015-08-18T12:21:52Z")
This query was generated by elasicsearch-dsl-py library, but sure, it can be reduced. As I mentioned before, I have a lot of unique author\_ids and category\_ids there. Category ids (4200 unique), author\_ids (over 2M). How is the best practice for handling terms aggregations when field has many unique values? ---
**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\ **Post date:** [August 18, 2015, 12:45pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/10 "2015-08-18T12:45:13Z")
4200 unique category ids is actually a low cardinality, it should execute very quickly. Since the query takes a long time to execute, could you try to use the nodes hot threads API to capture where elasticsearch is spending time while the query is running? Also it would be interesting to know if your fields are mapped as numerics or strings. Even if I would be surprised if the filters explained the slowness, they might contribute a bit to it so it would be interesting to test without them. ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 18, 2015, 1:13pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/11 "2015-08-18T13:13:18Z")
I changed the query and removed filters part, but still there is no visible improvement. Current query: [https://gist.github.com/artursmet/273fa075d97711ea3edd](https://gist.github.com/artursmet/273fa075d97711ea3edd) Categories and authors are mapped as integers: ``` "author_ids" : { "type" : "integer" } ``` Brand field is mapped as not\_analyzed string. Hot threads looks interesting, I've never heard of it before. Here is the output from hot threads: [https://gist.github.com/artursmet/c97170918f917dbe92f1](https://gist.github.com/artursmet/c97170918f917dbe92f1) but I think that it won't tell me more than I know now. Aggregations are taking most of time. Things like categories or authors, should be indexed as multi value field (string or numeric) or as nested field with some more data? ---
**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\ **Post date:** [August 21, 2015, 3:56pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/12 "2015-08-21T15:56:14Z")
The hot threads look sane, I'm a bit puzzled when you are having such slow response times on 9M docs only. If you have some time for experimenting, you might want to try to enable doc values on those fields and/or to map them as strings instead of numerics (which have different execution paths for terms aggs). ---
**Author:** ![artursmet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artursmet/32/4219_2.png) [@artursmet](https://discuss.elastic.co/u/artursmet)\ **Post date:** [August 21, 2015, 4:01pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/13 "2015-08-21T16:01:12Z")
@jpountz Thanks for reply. I tried setting those fields for doc\_values, one thing that I didn't check is mapping as strings instead of numeric. But I will check this path for sure. I found a little work around - I split my query into two queries (facets for `search_type=count` endpoint and normal hits into standard `_search`. I also added `timeout` for my facet query, because my aggregation counters doesn't have to be super accurate. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 21, 2015, 10:39pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/14 "2015-08-21T22:39:34Z")
FYI you should really call them aggs, not facets, otherwise you will confuse people 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:54pm UTC](https://discuss.elastic.co/t/slow-terms-aggregations/27496/15 "2017-07-05T23:54:25Z")