Logo for AiToolGo

Improve Query Performance with EXPLAIN Plans in Amazon Aurora DSQL

In-depth discussion
Technical
 0
 0
 5
This AWS Database Blog post explains how to read and optimize EXPLAIN plans in Amazon Aurora DSQL, a serverless PostgreSQL-compatible database. It contrasts Aurora DSQL's architecture (no heap storage, primary key as table, physically separated compute and storage) with standard PostgreSQL, and introduces a three-layer filter model (Index Condition, Storage Filter, Query Processor Filter) for reasoning about predicate placement. The post walks through scan types (Full Scan, Index Scan, Index Only Scan), a hands-on schema and seeding example, and a step-by-step query tuning workflow aimed at reducing latency and DPU consumption.
  • main points
  • unique insights
  • practical applications
  • key topics
  • key insights
  • learning outcomes
  • • main points

    • 1
      Clearly explains architectural differences between Aurora DSQL and PostgreSQL that directly affect how EXPLAIN plans should be interpreted
    • 2
      Provides a practical three-layer filter model that gives readers an actionable mental framework for predicate placement and optimization
    • 3
      Includes reproducible setup SQL and concrete EXPLAIN output examples annotated with optimization signals
  • • unique insights

    • 1
      In Aurora DSQL, primary keys are fully covering indexes by default, making primary key selection a high-impact design decision compared with PostgreSQL
    • 2
      Filter placement has real network and DPU cost because compute and storage are physically separated, so pushing filters down matters far more than in a single-node PostgreSQL instance
  • • practical applications

    • Gives database engineers a concrete method to diagnose slow Aurora DSQL queries using EXPLAIN, decide between index design changes and query rewrites, and reduce DPU cost through covering indexes, INCLUDE column selection, and filter pushdown.
  • • key topics

    • 1
      Architectural differences between Amazon Aurora DSQL and standard PostgreSQL (heap vs. B-tree storage, compute/storage separation)
    • 2
      Reading and interpreting Aurora DSQL EXPLAIN plan nodes (Index Cond, Storage Filter, Storage Lookup, Projections)
    • 3
      Three-layer filter model and step-by-step query optimization workflow
  • • key insights

    • 1
      Maps PostgreSQL EXPLAIN knowledge onto Aurora DSQL's distributed architecture, reducing the learning curve for PostgreSQL practitioners
    • 2
      Quantifies optimization impact through DPU consumption and data-transfer reasoning rather than only latency
    • 3
      Provides a reproducible dataset and schema so readers can verify plan changes themselves
  • • learning outcomes

    • 1
      Interpret Aurora DSQL EXPLAIN plan nodes, including Index Cond, Storage Filter, Storage Lookup, and Projections, to identify performance bottlenecks
    • 2
      Apply the three-layer filter model to move predicates from query processor filters to storage-level or index-level conditions
    • 3
      Choose and design indexes (including INCLUDE columns and primary keys) to achieve Index Only Scans and reduce DPU consumption
examples
tutorials
code samples
visuals
fundamentals
advanced content
practical tips
best practices

“ Introduction: Why EXPLAIN Plans Matter in Aurora DSQL

Amazon Aurora DSQL is a serverless, PostgreSQL-compatible database designed to scale from small prototypes to large active-active single-Region or multi-Region workloads. Although its EXPLAIN command follows familiar PostgreSQL conventions, Aurora DSQL's distributed architecture introduces new optimization opportunities that go beyond traditional PostgreSQL tuning. Understanding how Aurora DSQL executes queries helps you reduce latency and control Distributed Processing Unit (DPU) costs. This guide shows how to read and act on EXPLAIN plans to diagnose and improve query performance.

“ How Aurora DSQL Differs from PostgreSQL

Two architectural differences shape Aurora DSQL execution plans. First, there is no heap storage: every table is a B-tree organized by its primary key, so the primary key is the data itself. Tables without a user-defined primary key receive a hidden system-generated ID, which produces Full Scans rather than Sequential Scans. Secondary indexes implicitly carry primary key columns, and a well-chosen primary key can deliver performance benefits from day one. Second, compute and storage are physically separated across a network. Every byte transferred from storage to the query processor has a real latency and DPU cost, which makes filter placement a critical performance decision. In PostgreSQL, a filter applied in memory after a heap fetch is cheap; in Aurora DSQL, unfiltered data has already crossed the network by the time a query-processor filter runs.

“ Prerequisites and Test Environment Setup

To follow the examples you need an AWS account with permissions to create Aurora DSQL clusters, AWS CLI v2 with Aurora DSQL support, and a PostgreSQL-compatible client such as psql 16 or later. The examples use three tables: customer, account, and transaction. The transaction table intentionally lacks an explicit primary key so the impact of a later index can be observed, and the account index idx1 intentionally excludes created_at to demonstrate Storage Lookup behavior. Sample data is inserted in batches of 3,000 rows to respect Aurora DSQL's per-transaction mutation limit, and ANALYZE is run manually to produce deterministic plan output. In production, Auto Analyze keeps statistics current automatically. Plan outputs and DPU values in the post were captured on a single-Region us-east-1 cluster and will vary with data volume and cluster configuration.

“ Anatomy of an Aurora DSQL EXPLAIN Plan

Each element of an Aurora DSQL EXPLAIN plan signals a specific part of execution. Index Cond marks a predicate evaluated at the index level, the best case for limiting storage reads. Storage Filter marks a predicate applied inside the storage layer before data transfer. A top-level Filter is applied at the query processor after data has already been transferred, making it expensive. Projections list the columns fetched from storage for a scan node, so SELECT * inflates this list. Each Storage Scan is a network round trip to storage, so fewer is better. A Storage Lookup indicates a base-table fetch triggered when an Index Scan needs columns absent from the index. The B-Tree Scan is the underlying traversal node present in every scan type.

“ Aurora DSQL Scan Types Explained

A Full Scan reads the entire table and is chosen when no index can satisfy the predicates, such as a query on an unindexed column of a table without a user-defined primary key. Even then, filters are pushed to the storage layer as Storage Filters to reduce transfer volume. An Index Only Scan is the ideal outcome: all filter predicates and projected columns are satisfied by the index alone, so the base table is never touched. An Index Scan appears when the index satisfies the filter but not every projected column, producing a separate Storage Lookup on the base table. The post recommends adding missing columns to an INCLUDE clause to avoid this round trip, while noting that included columns increase index size and storage cost, so only columns used by important queries should be included.

“ The Three-Layer Filter Model

Every predicate is evaluated at one of three layers, and the layer determines how much data moves through the system. Level 1, Index Condition, is the most efficient because only matching index entries are read; achieve it with equality or range predicates on indexed key columns, placing the most selective column leftmost in composite indexes. Level 2, Storage Filter, applies the predicate inside the storage layer before transfer; add non-key filter columns to the INCLUDE clause to enable it. Level 3, Query Processor Filter, is the least efficient because all unfiltered data has already been transferred; resolving it may require a new index, a restructured query, or a generated column. The optimization goal is to move predicates from Level 3 toward Level 1 to reduce network data movement, latency, and DPU consumption.

“ A Practical Query Optimization Workflow

The recommended workflow starts with plain EXPLAIN rather than EXPLAIN ANALYZE, which avoids executing expensive queries and shows the optimizer's intended plan. Check whether the most selective predicates appear as Index Cond, count the Storage Scan nodes as a proxy for round trips, and look for query-processor Filter lines preceding Storage Scans, since these are primary optimization targets. Next, adjust indexes, such as adding INCLUDE columns or a better-ordered composite key, to move predicates down the filter model. Re-run EXPLAIN to confirm the new plan, and use EXPLAIN ANALYZE only once you are satisfied that the plan is sound, to measure actual execution time and DPU impact. Iterate until the query reaches an Index Only Scan with predicates at Level 1 or Level 2.

“ Best Practices and Key Takeaways

Aurora DSQL EXPLAIN plans look familiar to PostgreSQL users but reveal distinct optimization levers. Choose primary keys that match your dominant access pattern, since the primary key is a full covering index by default. Design secondary indexes with INCLUDE columns for frequently projected fields, balancing query speed against storage cost. Push predicates toward the index condition layer to minimize data crossing the network between storage and compute. Use plain EXPLAIN for diagnosis, validate changes by re-running EXPLAIN, and reserve EXPLAIN ANALYZE for measuring final performance. By applying the three-layer filter model and reading plans through the lens of Aurora DSQL's disaggregated architecture, you can reduce both latency and DPU costs.

 Original link: https://aws.amazon.com/blogs/database/improve-query-performance-with-explain-plans-in-amazon-aurora-dsql/

Comment(0)

user's avatar

      Related Tools