Date histogram slow on Discover page

[bczifra]

Describe the feature:
When loading the discover page, the date histogram can take a long time to load, especially if a large time frame is selected. Looking at the requests submitted by Kibana, this seems to be due principally to the time_zone property included in that calculation. The request is dramatically faster when dropping that property, which is apparently normal behavior. It would be nice if the histogram could be loaded in batches and not delay the loading of the documents in the discover pane, or if applying the time_zone property would be optional

[jgough]

This sounds similar to the issue I filed in #15662

[Bargs]

Turns out this might be solvable in ES.

[sergii-sakharov]

Was probably introduced by
#13378

[Bargs]

Nope, that PR only changed TSVB which we don’t use in Discover.

[sergii-sakharov]

After some testing - difference only starts being noticeable on datasets ( shards? ) of more than 10mil documents. Affected areas include discover and any visualizations that use datetime histograms.
So far 2 workarounds discovered:

1. Change timezone in Kibana settings to a fixed offset one (will only be acceptable if all users are in the same timezone e.g. no offshore teams or no expectations)
2. Introduce a proxy layer between Elasticsearch and Kibana and modify requests sent by Kibana. E.g. here's our nginx config:

location / {

-- rest of the proxy config

      lua_need_request_body on;

      rewrite_by_lua_block {
        if ngx.req.get_method() = "POST"
          ngx.req.read_body()
          local data = ngx.req.get_body_data()
          if data then
            data = string.gsub(data, '(%\"time_zone%\"%s*:%s*")Europe/London(%\")', "%1UTC%2")
            ngx.req.set_body_data(data)
          end
        end
 }

Note: in our case the difference is dramatic (2+ times faster discover and visualizations with datetime histograms) so for us workaround is worth it.

[timroes]

Since this problem does not only persist on the Discover screen but for all date histograms, I opened the meta issue #18853 for it with a detailed explanation and what we can and what and why we can't do some solutions.

I will close this issue in favor or #18853