[ES|QL] Sort by timestamp by default

[stratoula]

Description

Although the user can set in the es|ql query the | sort timestamp command we would like in specific applications in Kibana to sort by timestamp by default.

Kibana knows which timefiled is the one that user wants to sort by in Discover:

• @timestamp if the field exists in the dataset
• the time field that the user will have set with the named parameters WHERE @timestamp >= ${earliest} AND @timestamp < ${latest} see ES|QL: Named parameters #107029

so we could send it to the _query api as a query param such as ?sortfield=<timeField> or any other solution you feel is the best to achieve this. I remember also an idea of a ?kibana mode which could also make sense here.

This issue is mostly for initializing the discussions.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[astefan]

CC @tylerperk

[nik9000]

A few of us talked about this and decided that it'd be more surprising that good. The plan is to have a more IDE-like experience in kibana that can point users at expected patterns. Sort of like "if you write FROM logs* then it'll suggest FROM logs* | SORT @timestamp DESC in the same way IDEs suggest likely parameters for things. You don't have to do it, but it's something you should know is an option.

There's a bunch of competing visions here. One is that an ESQL query should, alone, describe as much of the behavior as possible without extra parameters. Because folks just post the ESQL query. At least, internally on slack, that's often all we'll get for debugging. And if we do a good job and people really start using ESQL then blogs and things will just have the text of the query. We won't get the url parameters, so having behavior depend on those is probably not a good idea.

Another vision is that ESQL should work well for folks analyzing data in Kibana but also work well for folks building an application against Elasticsearch directly. Those are very different folks. We can expect the application builders to be quite familiar with SQL, for example, but lots of the kibana-direct users won't be. They just won't have had to use it in their normal job. The SQL-familiar people would be really surprised by a default sort. The kibana-direct folks are surprised we don't have it.

The "suggest a sort" solution feels like it'd help everyone here - at least, everyone that uses kibana and can see it. Which seems good.

[IanLee1521]

@nik9000 - I'm not sure I personally agree with:

that it'd be more surprising that good.

It seems like having the results come out unsorted, when even the UI of the of the results panel suggests that they should be sorted (via the sort arrow) that they are sorted).

I just did a quick FROM logs-* | limit 10 and this experience of the timestamp claiming to be sorted, but the results are not, is most definitely more surprising than if the results list was rendered in order:

[nik9000]

@stratoula it this looks like a UI bug with it thinking these are sorted but not.

I know there's some tension between clicking the header in the UI and it sorting what's come back. that doesn't get pushed into the query and feels confusing too. But I think this picture just looks like a bug. Related, but different bug.

[stratoula]

Correct, we know about it. I just haven't found the time to tackle it

[nik9000]

thanks

[stratoula]

I am tackling this here elastic/kibana#187536

[stratoula]

I am reopening this, to track the performance optimization of the sort @timestamp. I can't proceed with the above PR because we want to sort @timestamp and raise the limit 10 but it seems that is not performant.

@nik9000 @bpintea if you want me to open a different issue for this let me know, but this is important for us

[stratoula]

I am closing this. ES is working on perfrormance atm so when they give us the green light we will continue our work at kibana