Query governance
Database Autonomy Service (DAS) analyzes slow SQL statements on your ApsaraDB RDS for PostgreSQL instance and automatically classifies them by root cause. Each night at 01:00, DAS processes the previous day's slow query logs, applies rule tags to SQL templates, and surfaces optimization suggestions. You can filter and export tagged results, and manually tag statements to track your optimization work.
Prerequisites
Before you begin, ensure that you have:
A High-availability Edition ApsaraDB RDS for PostgreSQL instance
An instance deployed in the Chinese mainland, China (Hong Kong), or Singapore region
Support for the China (Hong Kong) and Singapore regions was added on April 1, 2023.
Limitations
T+1 analysis: DAS analyzes data from the previous day. Changes you make — such as adding the Optimization Not Required tag — take effect the following day.
System-added tags cannot be modified or removed.
200-template cap: For each instance, DAS tracks only the top 200 most frequently executed slow queries. An instance can have at most 200 slow SQL templates.
Key concepts
SQL template
DAS groups structurally similar slow SQL statements into a single SQL template, replacing literal values with placeholders. For example, the following two statements belong to the same template:
SELECT * FROM orders WHERE user_id = 123
SELECT * FROM orders WHERE user_id = 456Template: SELECT * FROM orders WHERE user_id = ?
This grouping lets DAS apply tags and suggestions at the pattern level rather than to individual queries.
Optimizable SQL vs. Optimization Not Required
DAS divides all slow SQL templates into two categories:
| Category | Description |
|---|---|
| Optimizable SQL | Slow SQL templates that need optimization |
| Optimization Not Required | Slow SQL templates tagged Ignored (by DAS automatically) or No Need to Optimize (by you manually) |
Ignored is the system-applied version of Optimization Not Required. It covers SQL templates that start with SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN — statements DAS determines do not benefit from optimization.
View query governance results
Go to the Instances page. In the top navigation bar, select the region where your instance resides. Find the instance and click its ID.
In the left navigation pane, choose Autonomy Services > Slow Query Logs > Query Governance.
On the Query Governance page, review the following sections.
Query governance overview
Shows how DAS has classified and tagged your slow SQL statements after the latest analysis run.
Statistics for Failed SQL Executions appear only for instances with DAS Enterprise Edition enabled.
Query governance trend
Shows how governance results have changed over the selected time range.
Top rankings
Displays Best-performing Instances and Worst-performing Instances charts for Optimizable SQL.
Worst-performing Instances — shows the absolute number of slow SQL executions on each instance.
Best-performing Instances — shows the change in slow SQL executions. A negative number means a decrease (successful optimization); a positive number means an increase.
Focus on the worst-performing chart to find instances that need immediate attention, and the best-performing chart to confirm that optimization work is having an effect.
SQL to be optimized
Lists slow SQL templates that still require governance. Filter by DB name, SQL keyword, rule tag, or database username to narrow results.
Filter logic:
| Filter type | Separator | Logic |
|---|---|---|
| DB names | Comma (,) | OR |
| SQL keywords | Space | AND |
| Database usernames | Comma (,) | OR |
| Rule tags | Multiple selections | OR |
| Different filter types combined | — | AND |
For each SQL template in the list:
Click Suggestions in the Actions column to view optimization suggestions for that template.
Click Add Tag in the Actions column to manually add a tag. You can also select multiple templates and tag them in a batch.
Click Sample in the Actions column to view the slow query log sample.
Click Trend in the Actions column to view the slow query log analysis for that template.
Failed SQL
Lists failed SQL statements. Filter by DB name and SQL keyword.
Failed SQL statistics are only available for instances with DAS Enterprise Edition enabled.
Filter logic:
| Filter type | Separator | Logic |
|---|---|---|
| DB names | Comma (,) | OR |
| SQL keywords | Space | AND |
| Different filter types combined | — | AND |
Click Sample in the Actions column to view the sample details for a failed statement.
Rule tags reference
DAS automatically applies rule tags to classify slow SQL templates. The following table lists all system-defined rule tags.
Tags marked Yes in the Requires attention column indicate patterns with a direct path to optimization — prioritize these.
| ID | Tag name | Severity | Requires attention | Description |
|---|---|---|---|---|
| NEW_SQL | New Slow SQL | Critical | Yes | SQL templates that caused slow queries within the past seven days. |
| WITHOUT_PREDICATE | No Predicates | Critical | Yes | Templates with no WHERE clause conditions. Check whether your business requires a full table scan. If not, add predicates to restrict the query. |
| INDEX_ADVISOR | Index Suggestions | Normal | Yes | Templates querying tables with no index. Create indexes on the relevant columns to speed up these queries. |
| DEEP_PAGING | Deep Paging | Critical | No | Templates using LIMIT for deep pagination. Rewrite these queries using the JOIN operator instead of the LIMIT operator. |
| NULL_COMPARE | Null Match Error | Critical | No | Templates using ISNULL() to check for NULL values. ISNULL() returns NULL when any input is NULL, not TRUE or FALSE. |
| COUNT_NOT_STAR | Invalid COUNT Syntax | Critical | No | Templates using COUNT(column_name) or COUNT(constant) instead of COUNT(*). COUNT(*) is the SQL92 standard and counts all rows including those with NULL values; COUNT(column_name) skips NULL values. |
| FUZZY_LIKE | LIKE Query | Normal | No | Templates using LIKE for fuzzy matching. Indexes cannot be used for leading-wildcard LIKE patterns. |
| HAS_EXPR | Expressions Contained | Normal | No | Templates with calculation expressions on indexed columns. Indexes cannot be used when a function or expression wraps a column. |
| LARGE_IN_LIST | Large List Query | Normal | No | Templates with more than 200 elements in an IN clause. These are often auto-generated by application code and prevent index use on the involved columns. |
| SELECT_STAR | Full-column Query | Normal | No | Templates using SELECT * with no filter conditions. Querying all columns wastes resources when only a subset is needed. |
| COMPLEX_JOIN | Complex JOIN Query | Normal | No | Templates joining more than three tables. Make sure the join columns have matching data types and that indexes exist on the columns being joined. |
| CROSS_DB | Cross-database Query | Normal | No | Templates querying across databases. Cross-database queries can fail after a database migration. |
| SUBQUERY | Subqueries Included | Normal | No | Templates containing subqueries. Rewrite as JOIN queries for better performance. |
| LARGE_ROWS_EXAMINED | Excessive Scanned Rows | Normal | No | Templates scanning more than 50,000 rows on average. Use more specific filter conditions to reduce scan size. |
| LARGE_ROWS_SENT | Excessive Returned Rows | Normal | No | Templates returning more than 5,000 rows on average. Reduce the result set size in the query logic. |
| PERIOD_SQL | Periodic | Normal | No | Templates that execute at a fixed time each day. |
| DAS_IGNORE | Ignored | Normal | No | Templates that DAS automatically marks as Optimization Not Required. Covers statements starting with SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN. |
| NO_ADVICE | No suggestion | Normal | No | Templates for which DAS has no optimization suggestion. |
Manual tags
Use manual tags to track the status of your optimization work. Unlike system rule tags, manual tags can be applied and changed by you.
| ID | Tag name | Effect |
|---|---|---|
| USER_IGNORE | No Need to Optimize | Removes the template from Optimizable SQL starting the next day. |
| DAS_IMPORTANT | High-priority SQL | Marks the template as high priority. |
| DAS_NOT_IMPORTANT | Low-priority SQL | Marks the template as low priority. |
| DAS_IN_PLAN | Optimize Later | Marks the template for future optimization. |
Because DAS uses T+1 analysis, tagging a template today takes effect in the next day's results.
To add a manual tag, click Add Tag in the Actions column of the SQL to Be Optimized list. To apply a tag to multiple templates at once, select them and use the batch tag option.
Best practices
Use tags to prioritize optimization
The core workflow is to continuously shrink the Optimizable SQL list. Use SQL tags to quickly filter statements and prioritize them for optimization.
For templates that do not need optimization (for example, operational queries or intentional full table scans), add the No Need to Optimize tag to remove them from the list and reduce noise.
Export and share results
Export data:
In the SQL Details section, set your filter conditions.
Click Export in the upper-right corner to create a download task for the filtered templates.
Download the file within three days — exported files expire after that.
You can also select specific templates and export only those. This lets you create separate download tasks filtered by database or rule tag, then assign them to the owners responsible for each area.
Share filtered results:
| Method | How |
|---|---|
| Share selected templates | Select the templates, then click Batch Sharing. DAS generates a URL showing information for those specific templates. |
| Share filtered results | Apply filter conditions, then click Share next to Export. DAS generates a URL showing all templates that match the filter. |
Anyone with DAS permissions can open the shared URL to view the SQL information.
What's next
Slow SQL — Analyze individual slow query log entries for deeper diagnosis.