Optimizing correlated subqueries in Aurora PostgreSQL

A correlated subquery references table columns from the outer query. It is evaluated once for every row returned by the outer query. In the following example, the subquery references a column from table ot. This table is not included in the subquery’s FROM clause, but it is referenced in the outer query’s FROM clause. If table ot has 1 million rows, the subquery needs to be evaluated 1 million times.

SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

Improving Aurora PostgreSQL query performance using subquery transformation

Enabling transformation in the subquery

To enable the transformation of correlated subqueries into equivalent outer joins, set the apg_enable_correlated_scalar_transform parameter to ON. The default value of this parameter is OFF.

You can modify the cluster or instance parameter group to set the parameters. To learn more, see Parameter groups for Amazon Aurora.

Alternatively, you can configure the setting for just the current session by the following command:

SET apg_enable_correlated_scalar_transform TO ON;

Verifying the transformation

Use the EXPLAIN command to verify if the correlated subquery has been transformed into an outer join in the query plan.

When the transformation is enabled, the applicable correlated subquery part will be transformed into outer join. For example:

postgres=> CREATE TABLE ot (a INT, b INT);
CREATE TABLE
postgres=> CREATE TABLE it (a INT, b INT);
CREATE TABLE

postgres=> SET apg_enable_correlated_scalar_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);

                         QUERY PLAN
--------------------------------------------------------------
 Hash Join
   Hash Cond: (ot.a = apg_scalar_subquery.scalar_output)
   Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg)
   ->  Seq Scan on ot
   ->  Hash
         ->  Subquery Scan on apg_scalar_subquery
               ->  HashAggregate
                     Group Key: it.a
                     ->  Seq Scan on it                                        

The same query is not transformed when the GUC parameter is turned OFF. The plan will not have outer join but subplan instead.

postgres=> SET apg_enable_correlated_scalar_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a);
                QUERY PLAN
----------------------------------------
 Seq Scan on ot
   Filter: ((b)::numeric < (SubPlan 1))
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on it
                 Filter: (a = ot.a)  

Limitations

Using subquery cache to improve Aurora PostgreSQL query performance

Aurora PostgreSQL supports subquery cache to store the results of correlated subqueries. This feature skips repeated correlated subquery executions when subquery results are already in the cache.

Understanding subquery cache

PostgreSQL’s Memoize node is the key part of subquery cache. The Memoize node maintains a hash table in local cache to map from input parameter values to query result rows. The memory limit for the hash table is the product of work_mem and hash_mem_multiplier. To learn more, see Resource Consumption.

During query execution, subquery cache uses Cache Hit Rate (CHR) to estimate whether the cache is improving query performance and to decide at query runtime whether to continue using the cache. CHR is the ratio of the number of cache hits to the total number of requests. For example, if a correlated subquery needs to be executed 100 times, and 70 of those execution results can be retrieved from the cache, the CHR is 0.7.

For every apg_subquery_cache_check_interval number of cache misses, the benefit of subquery cache is evaluated by checking whether the CHR is larger than apg_subquery_cache_hit_rate_threshold. If not, the cache will be deleted from memory, and the query execution will return to the original, uncached subquery re-execution.

Parameters that control subquery cache behavior

The following table lists the parameters that control the behavior of the subquery cache.

You can modify the cluster or instance parameter group to set the parameters. To learn more, see Parameter groups for Amazon Aurora.

Alternatively, you can configure the setting for just the current session by the following command:

SET apg_enable_subquery_cache TO ON;

Turning on subquery cache in Aurora PostgreSQL

When subquery cache is enabled, Aurora PostgreSQL applies cache to save subquery results. The query plan will then have a Memoize node under SubPlan.

For example, the following command sequence shows the estimated query execution plan of a simple correlated subquery without subquery cache.

postgres=> SET apg_enable_subquery_cache TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Seq Scan on it
           Filter: (a = ot.a)

After turning on apg_enable_subquery_cache, the query plan will contain a Memoize node under the SubPlan node, indicating that the subquery is planning to use cache.

postgres=> SET apg_enable_subquery_cache TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);

             QUERY PLAN
------------------------------------
 Seq Scan on ot
   Filter: (b < (SubPlan 1))
   SubPlan 1
     ->  Memoize
           Cache Key: ot.a
           Cache Mode: binary
           ->  Seq Scan on it
                 Filter: (a = ot.a)

The actual query execution plan contains more details of the subquery cache, including cache hits and cache misses. The following output shows the actual query execution plan of the above example query after inserting some values to the tables.

postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a);
            QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on ot (actual rows=2 loops=1)
   Filter: (b < (SubPlan 1))
   Rows Removed by Filter: 8
   SubPlan 1
     ->  Memoize (actual rows=0 loops=10)
           Cache Key: ot.a
           Cache Mode: binary
           Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 1kB
           ->  Seq Scan on it (actual rows=0 loops=6)
                 Filter: (a = ot.a)
                 Rows Removed by Filter: 4   

The total cache hit number is 4, and the total cache miss number is 6. If the total number of hits and misses is less than the number of loops in the Memoize node, it means that the CHR evaluation did not pass and the cache was cleaned up and abandoned at some point. The subquery execution then returned back to the original uncached re-execution.

Limitations

Subquery cache does not support certain patterns of correlated subqueries. Those types of queries will be run without cache, even if subquery cache is turned on:

Improving Aurora PostgreSQL query performance using subquery window transformation

Aurora PostgreSQL can accelerate correlated aggregate subqueries by transforming them into equivalent window functions. This optimization applies to scalar correlated subqueries in a WHERE clause that compare an outer expression against an aggregate, such as the following query pattern:

SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

The transformation is a plan-level rewrite that changes only the execution plan, not the query results.

When to use subquery window transformation

Consider enabling this transformation if you have queries that match the self-correlation pattern: an outer row compared against an aggregate computed over the same table with a correlation condition.

This transformation is complementary to the existing correlated subquery transformation, which rewrites correlated subqueries as outer joins. The subquery window transformation rewrites them as window functions instead. The two transformations cover different patterns:

You can enable both transformations at the same time. When a query qualifies for both, the subquery window transformation is applied; when it qualifies for only one, that transformation is applied.

Enabling subquery window transformation

To enable the transformation of correlated aggregate subqueries into window functions, set the apg_enable_subquery_to_window_transform parameter to ON. The default value of this parameter is OFF.

You can modify the cluster or instance parameter group to set the parameters. To learn more, see Parameter groups for Amazon Aurora.

Alternatively, you can configure the setting for just the current session by the following command:

SET apg_enable_subquery_to_window_transform TO ON;

To disable the transformation, set the parameter to OFF using the same methods. Changes take effect without an instance reboot; session-level SET is immediate, and parameter-group changes apply to new connections.

Verifying the transformation

Use the EXPLAIN command to verify if the correlated subquery has been transformed into a window function in the query plan.

When the transformation is enabled, the query plan contains a WindowAgg node instead of a SubPlan node. For example:

postgres=> SET apg_enable_subquery_to_window_transform TO ON;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                      QUERY PLAN
------------------------------------------------------
 Subquery Scan on _sw
   Filter: (_sw.amount < (0.2 * _sw._w_agg_result))
   ->  WindowAgg
         Window: w1 AS (PARTITION BY o.product_id)
         ->  Sort
               Sort Key: o.product_id
               ->  Hash Join
                     Hash Cond: (o.product_id = p.id)
                     ->  Seq Scan on orders o
                     ->  Hash
                           ->  Seq Scan on products p

The same query is not transformed when the GUC parameter is turned OFF. The plan will not have a WindowAgg node but a SubPlan instead.

postgres=> SET apg_enable_subquery_to_window_transform TO OFF;
SET
postgres=> EXPLAIN (COSTS FALSE)
SELECT o.id, o.amount
FROM orders o, products p
WHERE p.id = o.product_id
  AND o.amount < (
    SELECT 0.2 * avg(o2.amount)
    FROM orders o2
    WHERE o2.product_id = p.id
  );

                 QUERY PLAN
---------------------------------------------
 Hash Join
   Hash Cond: (o.product_id = p.id)
   Join Filter: (o.amount < (SubPlan 1))
   ->  Seq Scan on orders o
   ->  Hash
         ->  Seq Scan on products p
   SubPlan 1
     ->  Aggregate
           ->  Seq Scan on orders o2
                 Filter: (product_id = p.id)

SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'true', false);
SELECT set_config('babelfishpg_tsql.apg_enable_subquery_to_window_transform', 'false', false);

SET BABELFISH_SHOWPLAN_ALL ON
-- run your query
SET BABELFISH_SHOWPLAN_ALL OFF

Limitations

If a query doesn't meet these conditions, it runs with its original plan; no error is raised.