# Improving performance of string id filtering and aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [October 20, 2018, 1:04am UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237 "2018-10-20T01:04:43Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Hen\_Gueta](https://avatars.discourse-cdn.com/v4/letter/h/67e7ee/32.png) [@Hen\_Gueta](https://discuss.elastic.co/u/Hen_Gueta)\ **Post date:** [October 20, 2018, 1:04am UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/1 "2018-10-20T01:04:43Z")
With the query i get a result of 900ms - 1200ms, this is not good enough. i want to improve it and i feel i got stuck. my SPEC: 3 nodes 8 ram (each node) 4 cpu (each node) - 2.90GHz, capacity: 4230MHz, width: 64 bits 5 shards, 3 replica each shard ES: 5.2.1 about 7,000,000 docs mapping: > ``` > { > "properties" : { > "user_id": { > "type": "string", > "index": "not_analyzed" > }, > "date": { > "type": "date", > "format": "strict_date_optional_time||epoch_millis" > }, > "tran_value": { > "type": "float" > }, > "type": { > "type": "string", > "index": "not_analyzed" > } > } > > ``` query: > ``` > { > "profile": true, > "query": { > "bool": { > "must": [ > { > "term": { > "doc.type": "transaction" > } > }, > { > "range": { > "doc.date": { > "lt": "2018-10-14T04:51:19.807Z" > } > } > }, > { > "terms": { > "doc.user_id": [ > "u::2-1482136850986-166310", > "u::2-1476646137089-722440", > "u::2-1479194700963-852066", > "u::2-1480265460875-343974", > "u::2-1481480223561-687220", > "u::2-1490893533742-495973", > "u::2-1491369718244-637855", > "u::2-1516387240762-347825", > "u::2-1528653244185-72739", > "u::2-1528655615289-795763", > "u::2-1529855538547-966658", > "u::2-1530351338917-849377", > "u::2-1530370314753-233523", > "u::2-1530895620180-904769", > "u::2-1534446475987-300580", > "u::2-1535168457544-236782", > "u::2-1537987992702-74474", > "u::2-1538136688139-639988", > "u::2-1538496245078-5097" > ] > } > } > ] > } > }, > "size": 0, > "from": 0, > "aggs": { > "transactionTotalValue": { > "sum": { > "field": "doc.tran_value" > } > }, > "transactionMinDate": { > "min": { > "field": "doc.date" > } > } > } > } > > ``` profile result: [profile\_1\_shard](https://gist.github.com/GuetaHen/f9b8e67aa0c81d9818dc8774cf9d2b3c) ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [October 20, 2018, 7:57am UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/2 "2018-10-20T07:57:48Z")
How large are your documents? How large are your shards? How many unique user\_ids do you have in the data set? Is this the full data set you will be using in production? What number of concurrent queries are you expecting? ---
**Author:** ![Hen\_Gueta](https://avatars.discourse-cdn.com/v4/letter/h/67e7ee/32.png) [@Hen\_Gueta](https://discuss.elastic.co/u/Hen_Gueta)\ **Post date:** [October 20, 2018, 12:45pm UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/3 "2018-10-20T12:45:17Z")
i have per shard about 1,266,414 docs with the total size of 820mb. unique user\_ids i have about 6 million. in prod i have a bigger mapping and more aggs inside the query, but i found that the this filter and those aggs are the longest. i will have about 4-5 concurrent queries that will run about 9-10 time in a min. i got the time written here only for running this query alone. is mapping the user\_id as long number instead of a string can improve the filter time? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [October 21, 2018, 3:20pm UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/4 "2018-10-21T15:20:49Z")
As your data set is quite small it is likely that it will be cached, at least after the first query has run. I would recommend looking at [this guide](https://www.elastic.co/guide/en/elasticsearch/reference/6.4/tune-for-search-speed.html), and especially try [forcemerging down to a single segment](https://www.elastic.co/guide/en/elasticsearch/reference/6.4/tune-for-search-speed.html#_force_merge_read_only_indices) to see what effect that has. You can also experiment with the shard count and try increasing it a bit, and try using preference to get a spread of processing across the nodes in the cluster. ---
**Author:** ![ddorian43](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ddorian43/32/36093_2.png) [@ddorian43](https://discuss.elastic.co/u/ddorian43)\ **Post date:** [October 23, 2018, 8:17am UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/5 "2018-10-23T08:17:29Z")
String `user_id` is faster as a filter compared to `long`. You can make it shorter though. And if you can, try to encode the integers in there as base128/256 strings, to maybe make them shorter and maybe make the term-dictionary faster. Otherwise you have slow disk / low-ram / low-cpu if that simple filter takes that much time. Or retry the query several times, to get the cached response and see if your disk is too slow. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 20, 2018, 8:19am UTC](https://discuss.elastic.co/t/improving-performance-of-string-id-filtering-and-aggregation/153237/6 "2018-11-20T08:19:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.