[RFC] Query Insights - Recommendation framework

[ansjcy]

Is your feature request related to a problem? Please describe

We developed the Query Insights Plugin as part of #11429 to provide query-level insights and surface the potential issues to users, but the complete workflow (elaborated below in [1]) should also involve providing actionable recommendations to the user so that they can improve the performance. The recommendations could be on per-query level (optimize how the query is constructed) or on index/cache level (optimize how the underlying data are stored).

[1] Based on feedback from community, the complete workflow for an OpenSearch admin user to interact with Query insights framework to improve the query performance should be like: 1. The user gain overview info on queries that consume most resources from query insights framework. 2. The user drill down to specific queries to understand the details, including the query shape, the users who sent the queries, etc. 3. Drill down information also suggests what are the potential impact of these queries, and actions users can take to optimize those queries and improve overall search performance.

Describe the solution you'd like

There are two different types of recommendations:

1. Query-specific recommendations
   These recommendations focus on optimizing individual queries, such as removing redundant filters or eliminating routing parameters to prevent shard overload. We can focus on only the "top N heavy queries ([RFC] Real-time Insights into Top N Queries by Latency and Resource Usage #11186)" and implement an async post processor to analyze the top n queries, and if possible, attach the "per query level" recommendation to the top queries.

2. Underlying Index-Level Recommendations
   These recommendations are aimed at improving overall search query performance by optimizing the underlying data. Ideally those recommdations should be like "add a field / reconstruct the documents in the index to improve the CPU utilization by X percent". For example, remodel the document to avoid joins, use copy-to to combine fields to avoid multi_match etc.

We should establish a standard recommendation infrastructure within Query Insights plugin to support those use cases. It should support the complete workflow to add customized rules, detect and match queries with rules, and make recommendations.

Related component

Search:Query Insights

Describe alternatives you've considered

Alternatively, we can potentially utilize Performance Analyzer RCA to offer simple rule-based recommendations. The RCA agent will be responsible for reading and interpreting query insight data, integrating with PA metrics, and generating recommendations. These recommendations can be write back to the cluster through the query insight plugin, making them accessible through the dashboard. Or, they can be simply exposed through an API of the RCA agent. But that will require us to add a dependency on PA/RCA.

Additional context

Query Insights framework: #11429

Any feedback would be appreciated!