# Kibana search performance over time-based indices

**URL:** <https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617>\
**Category:** Kibana\
**Created:** [February 15, 2019, 4:37pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617 "2019-02-15T16:37:51Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![avencar](https://avatars.discourse-cdn.com/v4/letter/a/6bbea6/32.png) [@avencar](https://discuss.elastic.co/u/avencar)\
**Post date:** [February 15, 2019, 4:37pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/1 "2019-02-15T16:37:51Z")

</div>

I have a concern regarding the change in Kibana 6.x to no longer support "time-interval based index patterns". Instead, we now use wildcards. Below is an example of my concern.

Let's say we have a large number of daily indices (i.e., several hundreds for the past few years) and only intend to search over data in the past 24H. Isn't it extremely inefficient to search over ALL of these indices when all relevant data is contained in just 2 indices (today and yesterday) at most? Prior to Kibana 6, it would smartly restrict the indices that are searched over based on the time range selected.

By the way, I did find a similar post to my concern here:

> [@Convert to non-time-interval based index pattern](https://discuss.elastic.co/t/convert-to-non-time-interval-based-index-pattern/146478):
>
> Hello Kibana community, today I came across a problem in my ES-test-cluster. I've got a few nodes running on version 2.4.0 equipped with Kibana 4.6.6. In order to upgrade this cluster though, I need to convert the time-interval based index pattern to a non time-interval based one as this is deprecated in later versions of Kibana. I have got hundreds of objects referencing the time-interval based index pattern, so to manually update these would be a pain. sweat So, is there any recommended…

@Christian_Dahlqvist responded saying this: "more recently querying all indices has been made much more efficient in Elasticsearch". Does this mean that there were improvements made to the time range query performance? Is the overhead (of querying hundreds of indices versus just 1-2 in my example) so minimal that it's not worth restricting queries to only the relevant (time-based) indices?

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![avencar](https://avatars.discourse-cdn.com/v4/letter/a/6bbea6/32.png) [@avencar](https://discuss.elastic.co/u/avencar)\
**Post date:** [February 15, 2019, 11:52pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/2 "2019-02-15T23:52:39Z")

</div>

I want to add some more details to this post, after some quick performance tests. We are seeing a considerable performance hit because of this change on our large cluster.

Below is a quick test I ran to compare query execution times. Both queries return the same results (verified counts).

Query #1:

```auto
PUT index-prefix-*/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte" : "now-1d/d",
                "lt" : "now/d"
            }
        }
    }
}

```

Query #2:

```auto
PUT index-prefix-2019.02.15,index-prefix-2019.02.14/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte" : "now-1d/d",
                "lt" : "now/d"
            }
        }
    }
}

```

On our large cluster, query #1 takes ~5 seconds while query #2 takes ~0.7 seconds, repeatedly.

Thanks again.

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [February 18, 2019, 8:36pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/3 "2019-02-18T20:36:22Z")

</div>

@Bill_McConaghy / @sebastien

Thanks,  
Bhavya

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [February 20, 2019, 5:45pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/4 "2019-02-20T17:45:46Z")

</div>

@mattkime can we please get some help here?

Thanks,  
Bhavya

---

<div class="post-metadata">

**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\
**Post date:** [February 20, 2019, 7:00pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/5 "2019-02-20T19:00:08Z")

</div>

Hello Andre,

Sorry to hear that you're suffering from reduced performance. Lets see if we can fix that.

Do you have a time filter field name selected for your index pattern?  
Do you have a rough idea of the size of your elasticsearch cluster? Number of nodes?

---

<div class="post-metadata">

**Author:** ![avencar](https://avatars.discourse-cdn.com/v4/letter/a/6bbea6/32.png) [@avencar](https://discuss.elastic.co/u/avencar)\
**Post date:** [February 20, 2019, 10:00pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/6 "2019-02-20T22:00:24Z")

</div>

Hi Matthew,

I appreciate the response. We do have a time filter field name selected for the index pattern. Also, we have a large number of nodes and data, each of which is carrying a heavy burden (\> 1000 active shards / node). I know we're pushing the limits here, but it still bothers me that Kibana isn't restricting the time-based indices it's searching over when we know the data is only contained within those indices.

My main question is why was this feature removed to begin with? More specifically, what changes were made in elasticsearch to make querying over all indices/shards so efficient compared to elastic v2? It seems like we'd still have to iterate over all documents to see if it falls within the requested time range.

Thanks again!

---

<div class="post-metadata">

**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\
**Post date:** [February 20, 2019, 10:08pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/7 "2019-02-20T22:08:14Z")

</div>

> My main question is why was this feature removed to begin with?

An optimization was added to elasticsearch that should make the time based index pattern unnecessary. Obviously this isn't your experience. I'm going to do some research and get back to you.

---

<div class="post-metadata">

**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:** [February 21, 2019, 7:27am UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/8 "2019-02-21T07:27:31Z")

</div>

It sounds like part of the problem is that you have too many shards in your cluster. Exactly how many shards do you have? How large data volume does this correspond to? How many data nodes do you have?

The old scheme based on index names was performance, but not very flexible. If data ended up in the wrong index it would give incorrect results and it could not be used with rollover indices. It was replaced by a field stats check, which for large clusters could be slow. This type of check was then optimised in Elasticsearch and merged into the query phase which I believe resulted in the current solution.

---

<div class="post-metadata">

**Author:** ![avencar](https://avatars.discourse-cdn.com/v4/letter/a/6bbea6/32.png) [@avencar](https://discuss.elastic.co/u/avencar)\
**Post date:** [February 21, 2019, 2:12pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/9 "2019-02-21T14:12:41Z")

</div>

Thanks for the reply Christian. I definitely agree that the root of our problem is high shard / data node ratio, and we are working to reduce that for future indices. Unfortunately it won't happen right away and will only improve over time.

As for your explanation on the changes, I have a few follow up questions:

1. Why not keep both schemes for querying Kibana, including the higher performing time-interval based index patterns even with the known caveats. The caveats you mentioned are a non-issue for us, and performance is critical.
2. I noticed that the field stats check is now deprecated in elastic 6.x and to instead use the field caps API. However, the field caps API does not have a way retrieve min/max values for a date field (which I assume is what was used to determine whether we need to search a particular index or not). Instead, it suggests running a min/max aggregation, which seems expensive. Can you please elaborate on what optimization was made to the query phase? I'd like to understand it better.

Thanks again!

---

<div class="post-metadata">

**Author:** ![jasontedor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jasontedor/32/66992_2.png) [@jasontedor](https://discuss.elastic.co/u/jasontedor)\
**Post date:** [February 22, 2019, 5:18pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/10 "2019-02-22T17:18:16Z")

</div>

Hi @avencar. Sorry for the issues that you're facing. I think I might be able to offer some explanations for what is happening behind the scenes within Elasticsearch. First, some clarifying questions:

- Which minor 6.x version are you on?
- How many nodes do you have in your cluster?
- How many shards do you have per index being hit in these search requests?
- How many shards in total are being hit in these search requests?
- Can you please temporarily enable the search slow log with a very low threshold (`1nanos`), execute the "slow" query, and share here the rewritten query from a shard where you expect the query to not match any documents? I want to ensure that these are being rewritten to match none queries. Don't forget to null out the setting when you're done, so that your slow logs are not spammed with the execution of every query.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 22, 2019, 5:18pm UTC](https://discuss.elastic.co/t/kibana-search-performance-over-time-based-indices/168617/11 "2019-03-22T17:18:17Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.