When does Amazon Redshift use AQUA to run queries? - Amazon Redshift

When does Amazon Redshift use AQUA to run queries?

For each query that scans a table, Amazon Redshift determines whether the scan operation is sent to AQUA or run locally on the Amazon Redshift cluster. Scan and aggregation operations are sent to AQUA when they contain at least one predicate that contains a LIKE or SIMILAR TO expression that AQUA supports. If a scan operation is sent to AQUA, then the entire operation runs on AQUA (not only the LIKE or SIMILAR TO processing).

For example, the following query includes the predicates str1 LIKE 'a%' and num1 > 10 that are sent to AQUA.

select num2 from tbl where str1 LIKE 'a%' and num1 > 10 GROUP BY num2;

In this case, AQUA scans the table tbl, filters by the predicates, groups the results by num2, and returns the results to Amazon Redshift.

In the following example, Amazon Redshift determines which portions of the query are better run in AQUA and which to run locally on the Amazon Redshift cluster. In this case, there is a LIKE predicate on the customer_name column that is used to filter the rows returned. The scan operation is sent to AQUA to run that part of the query. Amazon Redshift receives only the results of the scan, which Amazon Redshift then uses to perform a join locally and complete the query.

select c.customer_name, p.prod_name, sum(revenue) from orders o join customers c ON c.id = o.customer_id join products p ON p.id = o.product_id where c.customer_name LIKE ‘%Amazon%Web%’ order by sum(revenue);

For more information about LIKE and SIMILAR TO, see LIKE and SIMILAR TO in the Amazon Redshift Database Developer Guide.

Considerations for AQUA

The following types of SQL queries are currently not supported by AQUA. Queries that include them run locally on your Amazon Redshift cluster.

  • Queries that perform writes, such as, INSERT, UPDATE, DELETE, CREATE TABLE AS, COPY, and UNLOAD. For more information, see SQL Commands in the Amazon Redshift Database Developer Guide.

  • SELECT queries without a predicate, for example the following.

    select * from tbl1
  • Python user-defined functions (UDFs) in scans, including scans on complex views that are defined by Python UDFs.

  • Queries that use the following metacharacters in LIKE and SIMILAR TO predicates:

    • A nonconstant regular expression literal in a LIKE expression.

    • Either of two alternatives, for example the following.

      SIMILAR TO '%(cat|dog)%'
    • Multiple instances of single-character wildcards in VARCHAR columns, for example the following.

      SIMILAR TO 'ab.cd.ef'
    • Unicode escape sequence, hex-characters, and octal-characters in the match string, for example the following.

      SIMILAR TO '%ab\uc382'
      SIMILAR TO '%ab\U0000c382'
      SIMILAR TO '%ab\x88'
      SIMILAR TO '%ab\127'
    • Repetition metacharacters such as “*”, “+”, “?”, {m,n} applied on a pattern within () and on multibyte characters, for example the following.

      SIMILAR TO 'abc(def)*'
      SIMILAR TO 'abcʥ+'
    • Multibyte characters within a bracket expression [...], for example the following.

      SIMILAR TO 'abc[ʥde]'.
  • Queries where string functions, such as LOWER, UPPER, LEFT, RIGHT, are applied to the output of a LIKE or SIMILAR TO scan. For example, the following excerpt shows a LOWER function.

    LOWER(a) LIKE "%cat%"

    Instead, try to rewrite the query to use case-insensitive ILIKE without LOWER.

    ILIKE "%cat%"

How to determine if AQUA was used

The following SQL shows segments of query execution from SVL_QUERY_SUMMARY that ran on AQUA.

select * from svl_query_summary where label ~ 'Aqua' limit 100;

If you have superuser access, you can also join the STL_QUERY and STL_QUERYTEXT views to see the SQL statements that correspond to queries run by AQUA. The explain plan that is generated by the EXPLAIN statement doesn't show if a query used AQUA.