# Aggregation-"sql like" optimization guidance with elasticsearch 1.0.0 **URL:** \ **Category:** Elasticsearch\ **Created:** [January 31, 2014, 1:36am UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519 "2014-01-31T01:36:20Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Maxime\_Nay](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Maxime\_Nay](https://discuss.elastic.co/u/Maxime_Nay)\ **Post date:** [January 31, 2014, 1:36am UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/1 "2014-01-31T01:36:20Z")
Hi, We are experimenting elasticsearch 1.0.0, and are particularly excited about the new aggregation feature. Here is one of our use-case that we would like to optimize : Right now, to imitate a basic SQL group by query that would look like : SELECT day, hour, id, SUM(views), SUM(clicks), SUM(video\_plays) FROM events GROUP BY day, hour, id we are issuing this kind of queries : { "size" : 0, "query":{"match\_all":{}}, "aggs" : { "test\_aggregation" : { "terms" : { "script" : "doc['day'].date + '-' + doc['hour'].value + '-' - doc['id'].value", "order" : { "\_term" : "asc" }, "size": }, "aggs" : { "sum\_click" : { "sum" : { "field" : "clicks" } }, "sum\_views" : { "sum" : { "field" : "views" } }, "sum\_video\_plays" : { "sum" : { "field" : "video\_plays" } } } } } } But the perfs for this kind of queries are kind of low. Thus, we would like to know if there are a more optimized way to get what we want. Thanks ! Maxime -- 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/4fc6e81a-6cc2-4050-84f5-4f82b69e9764%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4fc6e81a-6cc2-4050-84f5-4f82b69e9764%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Binh\_Ly](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@Binh\_Ly](https://discuss.elastic.co/u/Binh_Ly)\ **Post date:** [January 31, 2014, 3:52pm UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/2 "2014-01-31T15:52:40Z")
Maxime, your bottleneck is likely in the script part. It has to dynamically compute that per doc just like in sql. However, if you can precompute that at index time (for example, introduce a field that contains the value of date-hour-id, you should be able to improve that aggregation time significantly. I did a quick test in 1.0 RC1 with an index of about 100K docs, and if I precompute that term field (and eliminate the script part), it is at least 10x faster than the script version. YMMV. -- 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/b3b87708-4435-40bb-9182-1f2a843f31c7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b3b87708-4435-40bb-9182-1f2a843f31c7%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Maxime\_Nay](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Maxime\_Nay](https://discuss.elastic.co/u/Maxime_Nay)\ **Post date:** [January 31, 2014, 6:02pm UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/3 "2014-01-31T18:02:39Z")
Unfortunately, we have about 8 different fields that could serve as aggregation key, and a lot of potential combinations between these fields. Thus, pre-computing all these combinations doesn't seem to be a viable solution. On Friday, January 31, 2014 7:52:40 AM UTC-8, Binh Ly wrote: > Maxime, your bottleneck is likely in the script part. It has to > dynamically compute that per doc just like in sql. However, if you can > precompute that at index time (for example, introduce a field that contains > the value of date-hour-id, you should be able to improve that aggregation > time significantly. I did a quick test in 1.0 RC1 with an index of about > 100K docs, and if I precompute that term field (and eliminate the script > part), it is at least 10x faster than the script version. YMMV. -- 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/134a71d9-7683-4804-9ae9-449d40580b35%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/134a71d9-7683-4804-9ae9-449d40580b35%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Binh\_Ly](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@Binh\_Ly](https://discuss.elastic.co/u/Binh_Ly)\ **Post date:** [January 31, 2014, 6:14pm UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/4 "2014-01-31T18:14:08Z")
Maxime, forgot to mention, you can also distribute the load out by increasing the shard count and adding more nodes. But precomputing the field is probably the quickest way to improve that performance. Keep in mind that unlike SQL, ES aggregations may return approximate metrics if you have more than 1 shard. -- 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/2ee82d59-f8c7-41cc-b777-3af6e18f6200%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2ee82d59-f8c7-41cc-b777-3af6e18f6200%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Maxime\_Nay](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Maxime\_Nay](https://discuss.elastic.co/u/Maxime_Nay)\ **Post date:** [January 31, 2014, 7:27pm UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/5 "2014-01-31T19:27:19Z")
For test purposes we currently have an index containing about 50M docs, distributed on a 4 nodes cluster, with 16 shards. Do you think that drastically increasing the number of shards would help ? On Friday, January 31, 2014 10:14:08 AM UTC-8, Binh Ly wrote: > Maxime, forgot to mention, you can also distribute the load out by > increasing the shard count and adding more nodes. But precomputing the > field is probably the quickest way to improve that performance. Keep in > mind that unlike SQL, ES aggregations may return approximate metrics if you > have more than 1 shard. -- 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/21874399-99c9-4c6b-8c76-f856ff95216f%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/21874399-99c9-4c6b-8c76-f856ff95216f%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Niko\_Nyrhila](https://avatars.discourse-cdn.com/v4/letter/n/e274bd/32.png) [@Niko\_Nyrhila](https://discuss.elastic.co/u/Niko_Nyrhila)\ **Post date:** [May 29, 2014, 11:31am UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/6 "2014-05-29T11:31:16Z")
Hi, You can nest aggregations, so in this case you'd first use Date Histogram aggregation with an interval of one hour: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Then you'd aggregate by "id" field: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Here is an example: [http://www.solinea.com/blog/elasticsearch-aggs-save-the-day](http://www.solinea.com/blog/elasticsearch-aggs-save-the-day) This should be very fast, even when running on a single machine. On Friday, January 31, 2014 3:36:20 AM UTC+2, Maxime Nay wrote: > Hi, > > We are experimenting elasticsearch 1.0.0, and are particularly excited > about the new aggregation feature. > > Here is one of our use-case that we would like to optimize : > > Right now, to imitate a basic SQL group by query that would look like : > SELECT day, hour, id, SUM(views), SUM(clicks), SUM(video\_plays) FROM > events GROUP BY day, hour, id > > we are issuing this kind of queries : > > { > "size" : 0, > "query":{"match\_all":{}}, > "aggs" : { > "test\_aggregation" : { > "terms" : { > "script" : "doc['day'].date + '-' + doc['hour'].value + > '-' + doc['id'].value", > "order" : { "\_term" : "asc" }, > "size": > }, > "aggs" : { > "sum\_click" : { "sum" : { "field" : "clicks" } }, > "sum\_views" : { "sum" : { "field" : "views" } }, > "sum\_video\_plays" : { "sum" : { "field" : "video\_plays" } } > } > } > } > } > > But the perfs for this kind of queries are kind of low. Thus, we would > like to know if there are a more optimized way to get what we want. > > Thanks ! > Maxime -- 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/bb2293a1-b83c-45a1-af42-e48b3fd9a0c9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/bb2293a1-b83c-45a1-af42-e48b3fd9a0c9%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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, 1:26am UTC](https://discuss.elastic.co/t/aggregation-sql-like-optimization-guidance-with-elasticsearch-1-0-0/15519/7 "2017-07-06T01:26:09Z")