Query optimization
Query optimization
Slow queries are common during application development with OpenSearch. Improper operations, excessive filter conditions, and sudden spikes in queries per second (QPS) can all cause slow queries. The Slow query analysis feature lets you view details of slow queries that consume a large number of logical computing units (LCUs) within a specified time range and provides optimization suggestions to help reduce costs.
Benefits
-
Free of charge: The feature is available at no cost after you enable it.
-
Flexible queries: You can quickly retrieve slow query records from the past month and obtain optimization suggestions.
-
Comprehensive analysis: You can analyze the causes of slow queries based on average consumption, peak consumption, total consumption, and the proportion of queries. You can also sort the results by these metrics in ascending or descending order.
Usage notes
-
The query optimization feature is temporarily disabled. An AI-based query optimization feature is under development. Stay tuned for updates.
-
Currently, you can analyze only data from the previous day. Samples of analyzed historical data are retained for up to 30 days.
Use the feature
1. Enable slow query optimization: When you use query optimization for the first time, choose Extensions ----> Query optimization in the left-side navigation pane to enable the slow query analysis service.
Select the application for which you want to enable slow query optimization. Select the check boxes to enable query analysis and acknowledge the usage commitment, and then click Confirm activation.
2. Analyze query data: After you enable the service, you can query by date or by requestId.
Query by date: Click Analyze now.
Wait for the analysis to complete.
Query by requestId: Enter a valid requestId and click Search.
The system returns a results table that includes the Optimization suggestions and Optimization examples columns. Common suggestions include reducing sorted_doc_count_in_qrs by lowering start+hit, for example, changing hit:500 to hit:50; reducing seek_times by adding query conditions or using a filter clause; and reducing match_doc_count by adding query or filter conditions, for example, appending AND status:4. Each suggestion includes examples that compare the original and optimized queries.
If the input format is invalid, the system displays a message.
For example, if you enter an invalid requestId, such as 123, and click Search, an Error dialog box appears with the message "Query analysis suggestions do not exist. Check whether the RequestId is valid." The error code is Optimizer.OperationFailed.NoData.
3. View the results after the slow query analysis is complete: Select a time range to view slow query results for a specific day.
You can also sort the analysis results by different metrics.
Click a statement in a slow query category to view the RequestId, Query, Total consumption (LCUs), Retrieval consumption (LCUs), Aggregation consumption (LCUs), Sorting consumption (LCUs), Summary consumption (LCUs), Other consumption (LCUs), and Optimization suggestions for the search request.
Other methods to reduce LCU consumption
In addition to using Slow query analysis to identify queries with high consumption, you can reduce LCU consumption in the following ways:
-
Reduce the number of retrieved documents: Set
hitto 50 or less in the config clause to reduce the number of documents retrieved per query. Example:config=start:0,hit:50,format:fulljson -
Add filter conditions: Add the
filterparameter 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 frequently use fuzzy searches and filter range queries, set the analyzer to Numeric in the index schema and use range queries to reduce LCU consumption. In the instance details, choose Offline changes > Index schema and change the analyzer for the field from Keyword to Numeric. Then use range queries, for example:
filter=id>100000