Query optimization

Slow queries are common in OpenSearch during day-to-day business development. Improper user operations, excessive filter content, or sudden QPS spikes during a period of time can all cause slow queries. The Slow Query Analysis feature of OpenSearch lets you view details of slow queries with high LCU consumption within a specified time period and get corresponding optimization suggestions to help you reduce costs.

• Free of charge: The feature is available as soon as you enable it and is free to use.

• Flexible queries: You can quickly query slow query records from the past month and get corresponding optimization suggestions.

• Comprehensive analysis: You can analyze the causes of slow queries based on dimensions such as average consumption, peak consumption, total consumption, and percentage of queries, and sort results by any of these dimensions.

• Currently, only data from the previous day can be analyzed. Sampled historical data that has been analyzed is retained for up to 30 days.

1. Enable slow query optimization: When you use Slow Query Optimization for the first time, choose OpenSearch High-performance Search Edition---->Extended Features---->Slow Query Optimization in the left-side navigation pane to enable slow query optimization.

Select the application for which you want to enable slow query optimization, select the options to activate the slow query analysis service and to confirm that you have read the usage commitment letter, and then click Confirm to enable the slow query analysis service.

2. Analyze query data: After you select an application, select the date that you want to analyze in the date picker and click View. If the data for that date has not been analyzed, the table area displays the message "Data for this date has not been analyzed". Click Analyze Now to trigger slow query data analysis. The table contains columns such as Slow Query Category, Peak Consumption (LCU), Average Consumption (LCU), Total Consumption (LCU), Percentage of Queries, and Optimization Suggestions. Total Consumption (LCU) is the key metric to focus on.

3. After the slow query data is analyzed, view the results: You can select different time ranges to query the slow query results for a specific day. On the analysis page, use the date picker to select the date that you want to analyze (for example, 2020-11-02).

You can also sort the analysis results by different dimensions.

Click a specific slow query category statement to view the RequestId, Query, Total Consumption (LCU), Retrieval Consumption (LCU), Statistics Consumption (LCU), Sorting Consumption (LCU), Summary Consumption (LCU), Other Consumption (LCU), and Optimization Suggestions of the specific search request.