[FEATURE] Stabilize preferred SQL implicit sorting method

[Swiddis]

Is your feature request related to a problem? Coming from #3061, there seems to be a few places in-code where we implicitly sort records when ORDER BY clauses aren't supplied by a user. Currently we use _doc, a per-shard incrementing counter for each indexed document. This works fine for single-shard indices, but causes unstable sorting in some cases when there are multiple shards involved. As a workaround, I'm adding a secondary sort by _id in cases where it's known that this causes issues, but this has a large performance impact (~4x latency in local testing).

What solution would you like? There should be a single source of truth for what the "implicit sorting" key is, and that implicit sort should be guaranteed unique per-document. This can probably go in a helper library somewhere, I'm just not quite acquainted enough with the plugin to know where that would be.

What alternatives have you considered?

• Sort just by ID, throw out _doc: this might be faster than trying to sort by two keys, but throws out all ordering.
• Sort by @timestamp: Not guaranteed to be present on all indices anymore.
• Some other field that bundles the rough chronology of _doc with information on shards: doesn't yet exist to my knowledge.

Do you have any additional context? See the comments on #3061