[RFC] Change the default value of plugins.query.size_limit to 10000 (MAX_RESULT_WINDOW)

[LantaoJin]

Is your feature request related to a problem?

The plugins.query.size_limit configure the maximum amount of rows returned by query. The default value is 200 which inherited from the legacy settings opendistro.query.size_limit. 200 is insufficient for many use cases which results incorrect outcomes:

• When operator pushdown is not able to work
  • [BUG] Inaccurate PPL query results due to plugins.query.size_limit restriction #2802 described a scenario that query with eval command caused correctness issue.
• When head(PPL) or limit(SQL) set greater than the default value 200.
  • [BUG] aggregation works incorrectly with LIMIT #1774 described a scenario that query with limit 2000 which overwritten by the default value 200.

On the other hand, plugins.query.size_limit is not the only threshold to protect the service from OOM issue. plugins.query.memory_limit which default value is 85% could be another gatekeeper.
In OpenSearch, the maximum result for searches on index controlled by index.max_result_window, which is 10000 by default. That is the suggested new default value of plugins.query.size_limit.

Goals

• Mitigate the impacts caused by insufficient default settings. Be aware in the vast majority of cases, users will not adjust the default value.

Non-Goals

• Users specify a larger number by head or LIMIT over 10000.
  • Extend size_limit setting in query engine to support unlimited index query. #703 (comment) proposed a solution that leverage scrollRequest to handle the case which requests docs more than 10000.
• Sort and limit operators (TopK) against a large mount of documents (for example, 9000) will consume much memory than 200.
  • [FEATURE] Top-K enhancement when having plan like SortOperator + LimitOperator #2857 proposed a solution to do a top-K sort.

What solution would you like?

Change the default value of plugins.query.size_limit to the default value of index.max_result_window (10000 by default)

What alternatives have you considered?

#703 (comment) proposed a more complex solution that leverage scrollRequest to handle the case which requests docs more than 10000.
E.g. source=index | head 11000 returns 11000 rows using scroll
but source=index | fields a,b | head 11000 still returns 200 rows

Do you have any additional context?

1. Plugin setting: https://github.com/opensearch-project/sql/blob/main/docs/user/admin/settings.rst#plugins-query-size-limit
2. Current limitation: https://github.com/opensearch-project/sql/blob/main/docs/user/dql/basics.rst#limitation