[KIBANA][DISCOVER] issue loading data view when too many date fields

[JoupainMD]

Kibana version:
8.11.4

Describe the bug:
We have noticed, when there is too many date fields in a data view, the discover is taking ages to load due to the Elastic query crafted by kibana to format date field to strict_date_optional_time.

Steps to reproduce:

1. Create a data view with indices with a lot of date fields (1000 for example)
2. try to load Discover, look at Inspect, then Requests
3. It should be a huge request 💥

Expected behavior:
Add the possibility to maybe chose which date fields will be formatted, or a limited list of fields.
Other improvement: Only format date field relevant to your time period fields. Maybe filter on indices matching the time picker period of the discover to avoid having too many date fields.

example of query crafted by kibana (a small one, bugged one are way bigger):

GET aws/_search
{
  "sort": [
    {
      "@timestamp": {
        "order": "desc",
        "format": "strict_date_optional_time",
        "unmapped_type": "boolean"
      }
    },
    {
      "_doc": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "track_total_hits": false,
  "fields": [
    {
      "field": "*",
      "include_unmapped": "true"
    },
    {
      "field": "@timestamp",
      "format": "strict_date_optional_time"
    },
    {
      "field": "mycustom-date-field-1",
      "format": "strict_date_optional_time"
    },
    {
      "field": "mycustom-date-field-2",
      "format": "strict_date_optional_time"
    },
    {
      "field": "mycustom-date-field-3",
      "format": "strict_date_optional_time"
    },
    {
      "field": "mycustom-date-field-4",
      "format": "strict_date_optional_time"
    },
    {
      "field": "mycustom-date-field-n",
      "format": "strict_date_optional_time"
    },
    {
      "field": "event.created",
      "format": "strict_date_optional_time"
    },
    {
      "field": "event.ingested",
      "format": "strict_date_optional_time"
    },
    {
      "field": "eventTime",
      "format": "strict_date_optional_time"
    },
  ],
  "size": 500,
  "version": true,
  "script_fields": {},
  "stored_fields": [
    "*"
  ],
  "runtime_mappings": {},
  "_source": false,
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2024-05-07T11:03:00.000Z",
              "lte": "2024-05-07T11:18:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  }

[elasticmachine]

Pinging @elastic/kibana-data-discovery (Team:DataDiscovery)

[kertal]

just wanted to add a note the triage is ongoing, and we to figure out what's causing the slowness. Because even it's a large number of fields we send from Kibana to ES, it shouldn't cause significant slowness on Kibana side, that's why we need number from Inspect (query time vs total time) as a first step

[kertal]

I've set this not to feedback_needed since we would need more information about what's slow. Using the numbers of the Inspector in Discover should reveal this. When there are many date fields, what's slow, is it the Elasticsearch query time, or the Total time it takes in Kibana to return the results. Depending on this, we then need to have a closer look what can be improved

[kertal]

Closing this for now, since feedback would be needed to iterate on it. Feel free to reopen, if you can provide more context.