Use the automatic SQL optimization feature for an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

Database Autonomy Service (DAS) provides the automatic SQL optimization feature for ApsaraDB RDS for PostgreSQL. You can use this feature to automatically diagnose and optimize slow SQL queries at the earliest opportunity and create indexes for an ApsaraDB RDS for PostgreSQL instance to ensure the optimal performance of the RDS instance.

Prerequisites

Your RDS instance runs RDS High-availability Edition.
The SQL Explorer and Audit feature is enabled for the RDS instance. For more information, see Use the SQL Explorer and Audit feature.
Note
After slow SQL queries are optimized, the overall performance of the RDS instance may degrade. To ensure that the automatic SQL optimization feature optimizes only abnormal SQL queries, we recommend that you enable the SQL Explorer and Audit feature.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
Go to the Autonomous Function Management panel.
- In the left-side navigation pane, choose Autonomy Services > Diagnostics. On the page that appears, click the Autonomy Center tab. In the upper-right corner of the Autonomy Center tab, click Autonomy Service Settings.
- In the left-side navigation pane, choose Autonomy Services > Dashboard. On the Performance Trends tab of the page that appears, click Autonomy Service Settings.
On the Autonomous Function Settings tab, turn on the switch for the autonomy service. On the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
- SQL Diagnostics Only: DAS performs daily diagnostics on SQL statements and provides index optimization suggestions. However, DAS does not create indexes on your RDS instance.
- SQL Diagnostics and Automatic Index Creation: DAS performs daily diagnostics on SQL statements, provides index optimization suggestions, and then creates indexes on your RDS instance based on the suggestions.
  Note
  DAS creates indexes within the maintenance window that you specify for your RDS instance.
Click OK.

What to do next

On the Autonomy Center tab, you can set the Type parameter to Optimization Events to view the optimization events that occurred within the selected time range. For more information, see Use the autonomy center feature for an ApsaraDB RDS for PostgreSQL instance.

Find the event that you want to view and click Details in the Actions column. On the Root Cause Analysis and Suggestions tab of the Slow SQL Statements Diagnostics (Diagnostics and Optimization) event, you can view the information about Problematic SQL Statements, SQL Statement Optimization, Index Recommendation, and Statement Optimization.