# Elasticsearch query over multiple indexes very slow **URL:** \ **Category:** Elasticsearch\ **Created:** [January 25, 2020, 1:33pm UTC](https://discuss.elastic.co/t/elasticsearch-query-over-multiple-indexes-very-slow/216520 "2020-01-25T13:33:37Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Pedro\_Torres](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pedro_torres/32/61421_2.png) [@Pedro\_Torres](https://discuss.elastic.co/u/Pedro_Torres)\ **Post date:** [January 25, 2020, 1:33pm UTC](https://discuss.elastic.co/t/elasticsearch-query-over-multiple-indexes-very-slow/216520/1 "2020-01-25T13:33:37Z")
**Question** : I create one index with one shard per day on Elasticsearch. When I perform queries against several days worth of these indices, it is very slow. Would anyone that has implemented a similar architecture give some guidance on what could be changed to make the queries more efficient? **Description of the system:** - Using [elasticsearch hot-warm-cold](https://www.elastic.co/pt/blog/implementing-hot-warm-cold-in-elasticsearch-with-index-lifecycle-management) architecture for [time-base indices](https://www.elastic.co/pt/blog/managing-time-based-indices-efficiently) - One index of data per day of around 140MB - One shard per index. - An index stays in the hot phase for 1 day - From day 1 to day 60 it is in the warm phase - From day 60 to day 180 the index goes to the cold phase. After that it is deleted - The health metrics of our ES cluster seem just fine. It doesn't seem to have too much CPU, Memory pressure The machines on the cluster are described below. The **high IO** ones are used in the HOT phase. The **high storage** are used in the **warm and cold** phases. The data is replicated in two regions as shown in the picture. [![enter image description here](https://i.stack.imgur.com/vHPih.png)](https://i.stack.imgur.com/vHPih.png) **Description of the problem:** When I do a query over these indexes, the query is very slow. To illustrate that, I performed a query for a single _\_id_. It takes more than 40 seconds. The image below is the the kibana output of that query. [![enter image description here](https://i.stack.imgur.com/mfQ72.png)](https://i.stack.imgur.com/mfQ72.png) It seems for me that ES does not perform the query of each index in parallel. Since when I go to the profiler, the total time it takes seem to be the sum of time times for each of the indices. Below there is the image reflecting the profiler result while also querying for the _\_id_ [![enter image description here](https://i.stack.imgur.com/7DA6P.png)](https://i.stack.imgur.com/7DA6P.png) I also posted this on SO: [https://stackoverflow.com/questions/59898636/elasticsearch-query-over-multiple-indexes-very-slow?noredirect=1#comment105932184\_59898636](https://stackoverflow.com/questions/59898636/elasticsearch-query-over-multiple-indexes-very-slow?noredirect=1#comment105932184_59898636) ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [January 27, 2020, 9:44am UTC](https://discuss.elastic.co/t/elasticsearch-query-over-multiple-indexes-very-slow/216520/2 "2020-01-27T09:44:25Z")
What actions do you execute in each of the phases? Are you freezing your indices? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 24, 2020, 9:57am UTC](https://discuss.elastic.co/t/elasticsearch-query-over-multiple-indexes-very-slow/216520/3 "2020-02-24T09:57:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.