# Slow elasticsearch sql query with aggregation to get Top-N results **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [October 24, 2019, 2:01am UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985 "2019-10-24T02:01:47Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![letwefly](https://avatars.discourse-cdn.com/v4/letter/l/d26b3c/32.png) [@letwefly](https://discuss.elastic.co/u/letwefly)\ **Post date:** [October 24, 2019, 2:01am UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/1 "2019-10-24T02:01:47Z")
Hi, I am trying to visualize some data and I am using elasticsearch sql to build some visualizations. I am importing data about 346M with 2000000 docs. ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/5/858f6551b5739ac9cf6086fb85cc06b55607c575.png) This is my slow sql: ``` select userId, count(*) from tableA where metricTime>0 group by userId order by cnt limit 10 ``` but it cost 1min to get results. ``` select userId, count(*) from tableA where metricTime>0 group by userId ``` this query is fast, only 140ms. This is there response pic: ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/1/712d12a39e29680a649e6f118fb6f47f3bb9ea4f.png) The dataset has 597443 distinct userId(actually 600000 because of the [HyperLogLog++](http://static.googleusercontent.com/media/research.google.com/fr//pubs/archive/40671.pdf) algorithm used in elasticsearch ) and 2000000 lines. ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/0/50136249aeceb094bb34c3e680d50d8b567a9f4e.png) So, my question is why the elasticsearch sorts on aggregate to get Top-N results slow? Or it is naturally? Or how can I improve it ? ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 25, 2019, 7:27am UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/2 "2019-10-25T07:27:27Z")
@letwefly ES-SQL uses a `composite` aggregation to run any query that needs an aggregation in it, for the simple reason that it has pagination support. But `composite` does come with a disadvantage as well: [it cannot sort on anything else other than its keys](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_order). In your query case, though, the sorting needs to happen on the bucket size. On the server side (ES) this is not possible, so ES-SQL implemented **client-side** sorting in [this PR](https://github.com/elastic/elasticsearch/pull/38042). Sorting being performed client-side, I guess this is where the slowdown comes from. ---
**Author:** ![letwefly](https://avatars.discourse-cdn.com/v4/letter/l/d26b3c/32.png) [@letwefly](https://discuss.elastic.co/u/letwefly)\ **Post date:** [October 28, 2019, 1:04am UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/3 "2019-10-28T01:04:40Z")
Thanks. And is there anything we can do to speed up the query? Or to by-pass the client-side limit? Anyway what can i do if i want to get the Top-N sorting results in a short time(several seconds not minutes)? ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 29, 2019, 12:32pm UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/4 "2019-10-29T12:32:21Z")
There is no "bypassing" the limit, as this is how sorting by COUNT(\*), in this specific case, works. "Top-N" can mean several things, depending on how you sort the results. As I mentioned, the problem here is sorting by `COUNT(*)` and aggregating (`group by userId`) at the same time and there is no way around it, unless you change your query. I think I was not clear in my previous message - _this is actually a limitation in Elasticsearch and not in SQL. Elastisearch doesn't know how to sort the aggregation results by their COUNT. SQL just tries to offer the functionality and the only way it can do this is client-side (in memory)._ ---
**Author:** ![letwefly](https://avatars.discourse-cdn.com/v4/letter/l/d26b3c/32.png) [@letwefly](https://discuss.elastic.co/u/letwefly)\ **Post date:** [November 1, 2019, 12:54am UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/5 "2019-11-01T00:54:12Z")
Thank you very much for your kindness.😁 ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [November 2, 2019, 12:09pm UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/6 "2019-11-02T12:09:58Z")
Apologies @letwefly. Re-reading my earlier message, my tone wasn't the kindest one :-). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 30, 2019, 12:10pm UTC](https://discuss.elastic.co/t/slow-elasticsearch-sql-query-with-aggregation-to-get-top-n-results/204985/7 "2019-11-30T12:10:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.