[Lens] Warn or prevent users from sorting the terms aggregation by count in ascending order

[markharwood]

This issue has been edited to reflect the discussion below.

Feature request

In addition to making the sorting experience more intuitive, Lens should discourage the use of "ascending count" as a sort order, because it leads to inaccurate results in distributed systems.

Open question

Is it important to keep the ascending sort order for any options?

What about sorting in ascending order by other metrics, like average or max?

Original description

Using Cloud 7.10 I found the defaults for Lens top terms to be the wrong sort order

Ascending order is a bad default because:

1. People typically don't want to find the rarest things
2. Elasticsearch can be wildly inaccurate in this mode. On fields with many values and multiple shards the error margin is unbounded (doc counts can be off by billions or more).

We nearly removed the support for using the terms aggregation with this sort order due to the inaccuracies.
Consider making "descending" the default sort order.

[wylieconlon]

@markharwood This isn't the default sort order for the Terms aggregation in Lens- your screenshot shows that you took some previous steps to configure the sort order of this aggregation, and I would not consider a bug because of the extra steps needed to get into this state.

I agree that we should discourage ascending order, but we already have an issue tracking intuitive sorting experience changes. Closing as a duplicate of that issue.

[markharwood]

@wylieconlon These are the steps to reproduce:

LensCount3.mov

[wylieconlon]

@markharwood That's right, that workflow is being discussed in the issue I linked above. Order of operations matters when constructing the chart: if you had dropped the field onto the middle drop target you would have received the chart you wanted, but instead you dragged it onto a specific dimension.

[markharwood]

Regardless of sequencing, I'd say the bug is that when selecting "Count of records" as a sort order for terms the default should never be ascending for the reasons I outlined above. @colings86 may go further and suggest we never offer it as an option given his previous comments on removing elasticsearch support for this.
In fact, ordering by any numerical summary e.g. unique count of listeners should default to "descending" because of the unbounded errors a distributed data store introduces. Even without the errors, it's not that useful - Zipfs law tells us a lot of data has long-tail distributions so the bars in your bar chart will tend to be a small random selection of the many, many things that occur only once - the bars are all the same length and not interesting.

[wylieconlon]

@markharwood I've proposed something in the linked issue which solves the same problem. Feel free to open an issue asking us to remove the ascending option for sorting by metrics, which we don't have an issue for.

[markharwood]

OK - let's change this issue then to be about the lack of Kibana warnings when sort by ascending results from elasticsearch include the unbounded error warning: "doc_count_error_upper_bound" : -1,

To give a concrete example how this could easily happen:

Given time-based indices, terms are not distributed evenly across the indices. Today for example "Tanya Roberts" is mentioned widely in news articles because she was reported as dead but then it was revealed she was alive. A very popular topic in today's news index. However the 80s Bond girl is very rarely mentioned, if at all, in older indices. So when you do a "rarest terms" aggregation across multiple shards the index for today would not return Tanya Roberts in the list of rarest values because she is seen as far from rare. However, another older index might have one mention of her and return that as being rare. So the final merged results for Tanya are 1 - entirely missing the large count from today's index.

In these cases we take special care to supply a doc_count_error_upper_bound to let client apps know how far out the doc counts can be. When we return -1 it means we could be majorly wrong - as in the Tanya Roberts blind spot. Lens and regular visualisations do not offer any warning when this is the case:

The warning in the response going unreported is this one:

The data/mapping to recreate this error is here. I use shard-routing to recreate the uneven spread of names typical of time-based indices:

DELETE test
PUT test
{
  "settings": {
    "number_of_shards": 4
  },
  "mappings": {
    "properties": {
      "name":{
        "type":"keyword"
      }
    }
  }
}
POST _bulk
{ "index" : { "_index" : "test"} }
{ "name" : "jim" }
{ "index" : { "_index" : "test"} }
{ "name" : "bob" }
{ "index" : { "_index" : "test"} }
{ "name" : "sue" }
{ "index" : { "_index" : "test"} }
{ "name" : "jane" }
{ "index" : { "_index" : "test", "routing": "earlier"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "tanya roberts" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z1" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z2" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z3" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z4" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z5" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z6" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z7" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z8" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z9" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z10" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z11" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z12" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z13" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z14" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z15" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z16" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z17" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z18" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z19" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z20" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z21" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z22" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z23" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z24" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z25" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z26" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z27" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z28" }
{ "index" : { "_index" : "test", "routing": "today"} }
{ "name" : "z29" }

GET test/_search
{
  "size":0,
  "aggs":{
    "terms":{
      "terms":{
        "field": "name",
        "order": {
          "_count": "asc"
        }
      }
    }
  }
}

When working with descending count sort orders the results are much less prone to inaccuracy and are more bounded error margins (most shards agree on Trump, Obama etc being the most popular) but ascending sort order inaccuracies can be wild.

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[wylieconlon]

Since this issue is no longer about the default sort order, but asking for a new feature, I will change the title and description of the issue to be more specific about what feature is being requested.

[flash1293]

Plan of action for the fix-it-week: Try to keep the user on descending metric sorting

• If current sort metric is removed, switch to another one
• If metric defaulted to alphabetical sorting because no metric was available (or got removed) and a new metric gets added, switch to this metric as sort metric (desc)

[markharwood]

Worth pointing out this issue title has been changed to focus on preventing a particularly inaccurate configuration before searches happen and my broader point about Kibana ignoring all forms of accuracy warnings in search results is not addressed.

[flash1293]

Good point @markharwood - I split out a separate issue for this (#94918). Makes a lot of sense to make the user aware