How do I use CloudWatch Database Insights Advanced mode to log and analyze query execution plans in Aurora PostgreSQL-Compatible?

Resolution

Note: To use the Advanced mode of Database Insights, your DB instance must use Aurora PostgreSQL-Compatible version 14.10, 15.5, or later. To upgrade your DB cluster, see Upgrading Amazon Aurora PostgreSQL DB clusters.

Turn on Advanced mode

To turn on Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon Aurora.

Activate execution plan logs

Complete the following steps:

1. Open the Amazon Relational Database Service (Amazon RDS) console.
2. In the navigation pane, choose Databases.
3. Select your Aurora PostgreSQL DB cluster.
4. Choose Configuration.
5. In the DB cluster parameter group section, choose the parameter group link.
6. Search for the aurora_compute_plan_id parameter, and then set the value to 1.
   Note: For a list of other parameters that you can configure, see Parameter reference for Aurora PostgreSQL query execution plans.
7. Choose Save changes.

Analyze execution plans

To analyze your execution plans, see Analyze execution plans. Also, see Monitoring query execution plans and peak memory for Aurora PostgreSQL.

Troubleshoot execution plan issues

If you can't view your execution plans, then take the following actions:

• Extend the time range that your CloudWatch dashboard displays data from Database Insights.
  Note: If you didn't set up a dashboard for Database Insights, then see Creating a customized CloudWatch dashboard.
• Use the aurora_stat_plans function to check the last time that Aurora captured the plan to make sure it's within the time range that's displayed on the Database Insights dashboard.
• Check the aurora_stat_plans.minutes_until_recapture and aurora_stat_plans.calls_until_recapture parameters for execution plan recaptures.

Note: Database Insights captures connection sessions at 1-second intervals. However, it might not display queries with short execution times or little effect on the DB load.