Joining foreign tables with local tables - Amazon Aurora
Services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the AWS European Sovereign Cloud Region, see the AWS European Sovereign Cloud User Guide.

Joining foreign tables with local tables

A local table is a regular Aurora PostgreSQL table or materialized view stored in Aurora storage. A local column is a column in a local table.

A query that joins a foreign table with a local table performs better with full pushdown than with table-scan pushdown. A local column with an unsupported data type or collation forces the query to fall back to table-scan pushdown.

Instead of changing the column definition, which rewrites the table, add a cast or COLLATE clause to the column in your query. To find the blocking columns, check "Unsupported Pushdown Expressions" in the EXPLAIN VERBOSE output. For more information, see Using EXPLAIN VERBOSE to see Pushdown SQL.

In the following example, heap_table has a character(1) column and the database default collation is en_US.UTF-8. The analytics engine supports neither, so only the foreign table scan is pushed down.

postgres=> \d heap_table
               Table "public.heap_table"
 Column |     Type     | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
 col1   | character(1) |           |          |

postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT col1 FROM heap_table WHERE EXISTS (SELECT * FROM ft_a);
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Result
   Output: heap_table.col1
   One-Time Filter: (InitPlan 1).col1
   InitPlan 1
     ->  Custom Scan on public.ft_a
           Pushdown SQL: SELECT id FROM (SELECT id::INTEGER AS id FROM system.main.read_parquet($aurora_analytics_parameter_1) AS ft_a) ft_a
           ->  READ_PARQUET
                 Table: ft_a
                 Projections: id
   ->  Seq Scan on public.heap_table
         Output: heap_table.col1
 Query Identifier: -5916225740472533963
 Unsupported Pushdown Expressions:
 1: Collation
    Description: default
 2: Type
    Description: character(1)

With col1::text COLLATE "C", the whole query is pushed down.

postgres=> EXPLAIN (COSTS OFF) SELECT col1::text COLLATE "C" FROM heap_table WHERE EXISTS (SELECT * FROM ft_a);
                  QUERY PLAN
----------------------------------------------
 Custom Scan
   ->  CROSS_PRODUCT
         ->  POSTGRES_SCAN
               Table: heap_table
         ->  FILTER
               Expression: (#0 = 1)
               ->  UNGROUPED_AGGREGATE
                     Aggregates: count_star()
                     ->  STREAMING_LIMIT
                           ->  READ_PARQUET
                                 Table: ft_a

The following table lists the casts and collations for local columns. Apply them wherever the column appears, including WHERE, GROUP BY, ORDER BY, and join conditions.

Local column type or collation Cast or collation to use Example
CHAR(n), VARCHAR(n) TEXT or VARCHAR without a length col::text
TIMESTAMP(p), TIMESTAMPTZ(p), TIME(p), TIMETZ(p), INTERVAL(p) with p less than 6 Same type without a precision, or with precision 6 col::timestamp
BIT(n), VARBIT(n) VARBIT without a length col::varbit
JSONB JSON col::json
libc collation, such as en_US or a libc database default COLLATE "C" or a predefined ICU collation such as "en-US-x-icu". When you also cast the column, put the collation after the cast. col::text COLLATE "C"

No cast enables full pushdown for NUMERIC columns without a precision and scale or with a precision greater than 38, or for types such as MONEY, INET, ENUM, and XML. For the types that support pushdown, see aurora_analytics_available_features().

Note

COLLATE "C" compares and sorts strings by byte value. ORDER BY, min(), max(), and range comparisons can return different results than under the original collation.