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.
Optional. In the Alert Configuration section, configure an alert template and subscribe to alert notifications. This helps you understand the status of an automatic SQL optimization task at the earliest opportunity.
The system recommends an alert template and adds alert rules for the required autonomy events in the alert template. You can configure the alert template as prompted.
Note
- If you have configured an alert template for your RDS instance, you must add alert rules for the required autonomy events to the alert template as prompted.
- If you have not configured an alert template for your RDS instance but you want to configure one, you can configure the alert template by following the instructions provided in Configure alert templates and Configure alert rules.
1. In the Select Alert Contact Group step, select an alert contact group.
  - Click Add Contact to add an alert contact.
  - Click Create Contact Group to create an alert contact group.
  - Find the alert contact that you want to manage and click Edit or Remove in the Actions column to modify or delete information about the alert contact.
  For more information, see Manage alert contacts.
2. In the Associate with Resources step, confirm the associated resources.
3. Click Submit Configuration. In the dialog box that appears, confirm the configuration.

What to do next

On the Autonomy Center tab, view the optimization events that occurred within a specified time range.

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.