Improve query performance with EXPLAIN plans in Amazon Aurora DSQL

This article explains how to use EXPLAIN plans to diagnose and improve query performance in Amazon Aurora DSQL, a serverless PostgreSQL-compatible distributed database.

• Aurora DSQL differs from PostgreSQL: primary keys are full covering indexes, compute and storage are physically separated, and filter placement directly impacts data transfer costs
• Understand the three-layer filter model: Index Conditions (Level 1, most efficient), Storage Filters (Level 2), and Query Processor Filters (Level 3, least efficient)
• Key scan types: Full Scan (no index), Index Scan (index with base table lookup), and Index Only Scan (all data in index)
• Common optimization patterns include adding missing indexes, including columns in indexes, using redundant predicates in joins, and restructuring queries with CTEs
• Use EXPLAIN ANALYZE VERBOSE to measure DPU (Distributed Processing Unit) consumption and validate optimization improvements
• Practical workflow: diagnose with EXPLAIN, map predicates to filter layers, apply targeted fixes (indexes, INCLUDE columns), and re-run EXPLAIN to confirm

By pushing filters closer to storage and eliminating unnecessary data movement, teams can significantly reduce query latency and DPU costs in Aurora DSQL workloads.