Query optimization

Query optimization

Slow queries are a common issue in OpenSearch, often caused by inefficient user operations, an excessive number of filter conditions, or a sudden spike in QPS. The slow query analysis feature in OpenSearch helps you identify slow queries with high Logical Computing Unit (LCU) consumption within a specified time range. It also provides optimization suggestions to help you reduce costs.

Benefits

• Free of charge: This feature is free to use.

• On-demand analysis: Analyze slow query records from the past month and receive optimization suggestions.

• Comprehensive metrics: Analyze the causes of slow queries with various metrics, including average consumption, peak consumption, and percentage of total queries. The results can be sorted by any of these metrics.

Usage notes

• You can analyze data from the previous day only. Historical analysis results are retained for up to 30 days.

Procedure

1. Enable the slow query analysis service: The first time you use query optimization, navigate to Feature Extensions > Query Optimization in the left-side navigation pane.

Select the application for which you want to enable slow query analysis, select the Enable Slow Query Analysis and Read Commitment for Use checkboxes, and then click Enable.

2. Analyze query data: After the service is enabled, you can analyze data either by date or by RequestId:

By date: Click Analyze.

Wait for the analysis to complete.

By RequestId: Enter a valid RequestId and click Query.

The query returns a results table with columns for optimization suggestion and optimization example. Common suggestions include: reducing sorted_doc_count_in_qrs (by lowering the start+hit value, for example, optimizing hit:500 to hit:50), reducing seek_times (by adding more query conditions or using a filter clause), and reducing match_doc_count (by adding more query or filter conditions, such as appending AND status:4). Each suggestion includes a side-by-side comparison of the original and optimized query.

The system displays an error message if the input format is incorrect.

For example, if you enter an invalid RequestId such as 123 and click Query, an error message appears, stating "No query analysis suggestion found. Check whether the RequestId is valid." The error code is Optimizer.OperationFailed.NoData.

3. View analysis results: Select a time range to view the corresponding slow query results.

You can also sort the analysis results by different metrics.

Click a slow query category to view its search request details, including the RequestId, Query, total consumption (LCU), retrieval consumption (LCU), statistics consumption (LCU), sort consumption (LCU), summary consumption (LCU), other consumption (LCU), and optimization suggestion.

In addition to using slow query analysis to identify high-consumption queries, you can reduce LCU consumption in the following ways:

• Reduce the number of retrieved documents: In the config clause, set hit to no more than 50 to reduce the number of documents retrieved per query. Example: config=start:0,hit:50,format:fulljson.

• Add filter conditions: Add the filter parameter to the config clause to reduce the number of matched documents. Example: config=start:0,hit:10,format:fulljson,filter=id>=0.

• Use numeric analysis and range queries: For fields that are frequently used in fuzzy searches or filter range queries, set the analysis method to numeric analysis in the index schema and use range queries to reduce LCU consumption. On the instance details page, choose Offline Changes > Index Schema, and change the analysis method of the target field from Keyword to Numeric Analysis. When you query data, use a range query, for example, filter=id>100000.