Original URL：https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-federated-query-best-practices-and-performance-considerations/

Amazon Redshift Federated Query 旨在帮助用户使用Amazon Redshift提供的分析功能直接查询存储在Amazon Aurora PostgreSQL与Amazon RDS for PostgreSQL数据库内的数据。关于设置环境以实现联邦查询的更多详细信息，请参阅通过AWS CloudFormation加速Amazon Redshift Rederated Query的应用。

Federated Query可实现实时数据集成并简化ETL处理流程。现在，大家可以直接在Amazon Redshift中连接实时数据源，并借此提供实时报告与分析结果。以前，我们需要先将数据从PostgreSQL数据库提取至Amazon Simple Storage Service（Amazon S3），而后使用COPY将其加载至Amazon Redshift，或者使用Amazon Redshift Spectrum对Amazon S3进行直接查询。关于Federated Query优势的更多详细信息，请参阅使用Amazon Redshift Federated Query建立起更简单的ETL与实时数据查询解决方案。

本文将探讨十种最佳实践，帮助大家在拥有较大的联邦数据集时、利用联邦查询检索大规模数据时、或者有众多Redshift用户访问联邦数据集时，充分发挥Federated Query的优势。当然，这里提到的方法在Federated Query的常规使用场景下并非必需。本文主要面向希望深度挖掘Federated Query潜力的高级用户。

以下最佳实践主要分为两大类：第一类为面向Amazon Redshift集群的建议；第二类则为面向Aurora PostgreSQL与Amazon RDS for PostgreSQL环境的建议。

本文提及的示例代码来自CloudDataWarehouseBenchmark GitHub repo（基于TPC-H与TPC-DS）。

适用于Amazon Redshift的最佳实践

在使用联邦查询访问Aurora或者Amazon RDS for PostgreSQL实例时，以下最佳实践适用于我们的Amazon Redshift集群。

1. 在各个用例中使用单独的外部schema

考虑使用单独的远程PostgreSQL用户，为每个特定的Amazon Redshift用例创建单独的Amazon Redshift外部schema。通过这一实践，大家可以对能够访问外部数据库的用户及组进行额外控制。例如，您可能希望建立起一套面向ETL使用方式的外部schema，并为其设置一个具有广泛访问权限的相关PostgreSQL用户；而在另一套独立schema中，则配备一个专门负责即席报告与分析的相关PostgreSQL用户，仅允许其访问特定几种资源。

以下示例代码将为ETL与即席报告创建两种外部schema。每个schema将使用不同的SECRET_ARN 以包含PostgreSQL数据库中各单独用户的凭证。

SQL

-- ETL usage - broad access
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etl
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-etl-secret-187Asd'
;
-- Ad-Hoc usage - limited access
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_adhoc
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-adhoc-secret-187Asd'
;

2. 使用查询超时来限制总运行时间

考虑为有权访问您外部schema的用户或组设置超时。用户查询可能会在无意间尝试从外部关系当中检索大量行，且持续运行较长时间，这将严重占用Amazon Redshift与PostgreSQL中的开放资源。

要限制用户查询的总运行时间，您可以为所有用户查询设置一项 [statement_timeout](https://docs.aws.amazon.com/redshift/latest/dg/r_statement_timeout.html)。以下代码示例，为将ETL用户设置2小时的超时周期：

SQL

-- Set ETL user timeout to 2 hours
ALTER USER etl_user SET statement_timeout TO 7200000;

如果有众多用户都可以访问您的外部schema，那么为每位用户单独定义 [statement_timeout](https://docs.aws.amazon.com/redshift/latest/dg/r_statement_timeout.html)显然不太实际。相反，大家可以使用query_execution_time指标在WLM配置当中添加查询监控规则。以下截屏所示为一项自动WLM配置，它为adhoc组内的用户提供一个临时报告队列，且规则会取消一切运行时间超过1800秒（30分钟）的查询。

3. 保证Amazon Redshift查询计划的高效执行

查看整体查询计划与联邦查询的查询指标，以保证Amazon Redshift能够高效处理这些查询。关于查询计划的更多详细信息，请参阅评估查询计划。

查看Amazon Redshift查询解释计划

我们可以在SQL查询中添加EXPLAIN前缀，并在SQL客户端内运行的方式检索解释计划。以下示例代码为演示查询所对应的解释输出：

SQL

<< REDSHIFT >> QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=6396670427721.37..6396670427721.37 rows=1 width=32)
   ->  XN Hash Join DS_BCAST_INNER  (cost=499986.50..6396670410690.30 rows=6812425 width=32)
         Hash Cond: ("outer".l_partkey = ("inner".p_partkey)::bigint)
         ->  XN Seq Scan on lineitem  (cost=0.00..2997629.29 rows=199841953 width=40)
               Filter: ((l_shipdate < '1994-03-01'::date) AND (l_shipdate >= '1994-02-01'::date))
         ->  XN Hash  (cost=449987.85..449987.85 rows=19999460 width=4)
               ->  XN PG Query Scan part  (cost=0.00..449987.85 rows=19999460 width=4)
                     ->  Remote PG Seq Scan apg_tpch_100g.part  (cost=0.00..249993.25 rows=19999460 width=4)
                           Filter: ((p_type)::text ~~ 'PROMO%'::text)

操作符XN PG Query Scan表示，对于这部分查询，Amazon Redshift将针对联邦PostgreSQL数据库运行查询。在本文中，我们将其称为“联邦子查询”。当查询中涉及多个联邦数据源时，Amazon Redshift将对各个源运行一项联邦子查询。Amazon Redshift会从集群中随机选择节点以运行各项联邦子查询。

在XN PG Query Scan行的下方，我们可以看到Remote PG Seq Scan，其后是带有Filter:元素的行。这两行定义了Amazon Redshift如何访问外部数据，以及在联邦子查询中使用的谓词。可以看到，该联邦子查询将针对联邦表apg_tpch.part运行。

通过rows=19999460，我们还可以看到Amazon Redshift预计该查询最多可以从PostgreSQL处返回2000万行。这一估计结论，来自向PostgreSQL询问后得到的表统计信息。

Joins

由于每项联邦子查询都通过集群中的单一节点运行，因此Amazon Redshift必须选择一项join分发策略，用以将从联邦子查询返回的各行发送至集群中的其余位置，从而完成查询中的joins操作。这里使用的广播或分发策略，将在解释计划中进行定义。以DS_BCAST开头的操作符将把副本的完整副本广播至所有节点；而以DS_DIST开头的操作符则将数据中的一部分发送至集群中的各个节点。

一般来讲，对较小的结果进行广播、对较大的结果进行分发往往执行效率更高。当计划程序能够比较准确地估计联邦子查询将要返回的行数时，即可选择正确的join分发策略。然而，如果计算程序的估算结果不准确，则可能选择广播过大的结果，从而降低查询速度。

Join Order

Joins应使用较小的结果作为内部关系。当我们的查询joins两个表（或者两项联邦子查询）时，Amazon Redshift必须选择如何更好地执行join。查询计划程序可能无法按照查询中声明的顺序执行join；相反，它只能使用待join关系的相关信息为各种可能的执行计划建立成本估算。估算完成后，它会选择其中预期成本最低的计划（包括join order）。

当我们使用hash join（最常见的join）时，Amazon Redshift会使用内部表（或结果）构造一个哈希表，并将其与外部表进行逐行比较。这里最好是使用最小结果作为内部值，以确保哈希表可以容纳在内存当中。如果计划程序的估算无法反映查询中各个步骤所对应的实际结果大小，则所选的join order有可能不是真正的最佳选择。

提高查询效率

下面来看提高执行效率的高级建议。关于更多详细信息，请参阅分析查询计划。

检查查询各部分的对应计划。如果您的查询中包含多个joins或者涉及子查询，则可以针对各项join或子查询审查解释计划，借此判断能否对查询做出进一步简化。例如，如果您使用多个joins，不妨使用单一join以简化查询本体，并观察Amazon Redshift如何自主规划这项join操作。
检查外部joins的顺序并使用内部join。计划程序有时候无法对外部join进行重新排序。如果能够将外部join转换为内部join，也许计划程序能够找出效率更高的执行方式。
在join当中引用最大Amazon Redshift表的分发键。当join引用分发键时，Amazon Redshift可以并行完成各个节点上的join，而无需在集群中移动Redshift表中的各行数据。
将联邦子查询的结果插入表中。当数据来自本地临时或永久表时，Amazon Redshift提供的统计信息最为准确。只在极少数情况下，最有效的作法才是先将联邦数据存储在临时表中，而后将其join至您的Amazon Redshift数据。

4. 确保将谓词下推至远程查询

Amazon Redshift的查询优化器能够高效将谓词条件下推至PostgreSQL中运行的联邦子查询当中。因此，请检查重要或者需要长期运行的联邦查询所对应的查询计划，判断Amazon Redshift是否将所有适用的谓词都应用于各项子查询。

考虑以下示例查询，其中谓词处于CASE语句之内，而联邦关系则处于CTE子查询内：

SQL

WITH cte 
AS (SELECT p_type, l_extendedprice, l_discount, l_quantity 
    FROM public.lineitem
    JOIN apg_tpch.part --<< PostgreSQL table
         ON l_partkey = p_partkey
    WHERE l_shipdate >= DATE '1994-02-01'
      AND l_shipdate < (DATE '1994-02-01' + INTERVAL '1 month')
)
SELECT CASE WHEN p_type LIKE 'PROMO%' --<< PostgreSQL filter predicate pt1
            THEN TRUE ELSE FALSE END AS is_promo
     , AVG( ( l_extendedprice * l_discount) / l_quantity ) AS avg_promo_disc_val
FROM cte
WHERE is_promo IS TRUE --<< PostgreSQL filter predicate pt2
GROUP BY 1;

通过将过滤器下推至远程关系中，Amazon Redshift仍可有效优化联邦子查询，具体请参阅以下计划：

SQL

<< REDSHIFT >> QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 XN HashAggregate  (cost=17596843176454.83..17596843176456.83 rows=200 width=87)
   ->  XN Hash Join DS_BCAST_INNER  (cost=500000.00..17596843142391.79 rows=6812609 width=87)
         Hash Cond: ("outer".l_partkey = ("inner".p_partkey)::bigint)
         ->  XN Seq Scan on lineitem  (cost=0.00..2997629.29 rows=199841953 width=40)
               Filter: ((l_shipdate < '1994-03-01'::date) AND (l_shipdate >= '1994-02-01'::date))
         ->  XN Hash  (cost=450000.00..450000.00 rows=20000000 width=59)-- Federated subquery >>
               ->  XN PG Query Scan part  (cost=0.00..450000.00 rows=20000000 width=59)
                     ->  Remote PG Seq Scan apg_tpch.part  (cost=0.00..250000.00 rows=20000000 width=59)
                           Filter: (CASE WHEN ((p_type)::text ~~ 'PROMO%'::text) THEN true ELSE false END IS TRUE)-- << Federated subquery

如果Redshift无法根据需求完成谓词下推，或者查询返回的数据量仍然过大，请考虑以下两条最佳实践中关于视图实体化与表同步的建议。要轻松重写查询以有效完成过滤器下推，请参考最后一条最佳实践中关于持久存储高查询频率数据的建议。

5. 使用物化视图以缓存高访问频率数据

Amazon Redshift现在支持创建物化视图，用于从外部schema中引用联邦表。

缓存经常运行的查询

大家可以考虑使用物化视图在Amazon Redshift集群中缓存那些运行频率较高的查询。如果很多用户会定期运行相同的联邦查询，且每次执行都需要对远程内容进行重新检索，那么通过物化视图，这些联邦查询可以从您的Amazon Redshift集群中检索结果，而无需从远程数据库中获取相同的数据。接下来，大家可以指定时间段对物化视图进行刷新，具体间隔取决于远程数据的变更率与重要性。

以下示例代码，演示了查询使用联邦源表对物化视图进行创建、查询与刷新的具体方法：

SQL

-- Create the materialized view
CREATE MATERIALIZED VIEW mv_store_quantities_by_quarter AS
SELECT ss_store_sk
     , d_quarter_name
     , COUNT(ss_quantity) AS count_quantity
     , AVG(ss_quantity) AS avg_quantity
FROM public.store_sales
JOIN apg_tpcds.date_dim --<< federated table
    ON d_date_sk = ss_sold_date_sk
GROUP BY ss_store_sk
ORDER BY ss_store_sk
;
--Query the materialized view
SELECT * 
FROM mv_store_quanties_by_quarter
WHERE d_quarter_name = '1998Q1'
;
--Refresh the materialized view
REFRESH MATERIALIZED VIEW mv_store_quanties_by_quarter
;

缓存查询频率较高的表

大家也可以考虑使用物化视图缓存那些查询频率较高的表。当多项不同查询使用同一联邦表时，通常有必要将该联邦表创建为物化视图，以供其他查询执行引用。

以下示例代码，演示了如何在单一联邦源表上创建并查询物化视图：

SQL

-- Create the materialized view
CREATE MATERIALIZED VIEW mv_apg_part AS
SELECT * FROM apg_tpch_100g.part
;
--Query the materialized view
SELECT SUM(l_extendedprice * (1 - l_discount)) AS discounted_price
FROM public.lineitem, mv_apg_part
WHERE l_partkey = p_partkey
  AND l_shipdate BETWEEN '1997-03-01' AND '1997-04-01'
;

截至本文撰稿时，大家还无法在物化视图中引用其他物化视图。换言之，缓存表内使用的其他视图，必须为常规视图。

在缓存与刷新时间/频率间取得平衡

物化视图最适合匹配那些运行速度比更新周期更快的查询。例如，如果物化视图每小时刷新一次，则其运行时长应该在几分钟以内；如果物化视图每天刷新一次，则运行时长应该在一小时以内。截至本文撰稿时，引用外部表的物化视图还无法实时增量式刷新。当我们运行[REFRESH MATERIALIZED VIEW](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html)并重新创建完整结果时，物化视图将完全刷新。

使用物化视图限制远程访问

另外，还应该考虑使用物化视图控制可以直接对远程数据库发出查询的用户数量。我们可以仅向刷新物化视图的用户授权外部schema访问权限，而其他Amazon Redshift用户则仅具备对物化视图的访问权限。

在对包含敏感信息的远程生产数据库进行查询时，以这种方式限制访问范围属于一项数据安全层面的最佳实践。

6. 将大型远程表与本地副本进行同步

考虑在Amazon Redshift永久表中为远程表保留一份副本。如果您的远程表很大，而完全刷新物化视图又相当费时，那么使用同步过程保持本地副本更新往往更为有效。

同步新添加的远程数据

当大型远程表中仅添加了新的行——而未发生任何更新或删除时，则可以通过定期将远程表内的新行插入副本实现对Amazon Redshift副本的同步。我们可以使用GitHub上的示例存储过程[sp_sync_get_new_rows](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/StoredProcedures/sp_sync_get_new_rows.sql)自动实现这一同步操作。

此示例存储过程要求源表中包含一个自动递增的标识列以作为其主键。示例存储过程会在我们的Amazon Redshift表中找到当前最大值，检索联邦表中具有更高ID值的所有行，并将其插入到Amazon Redshift表当中。

以下示例代码，演示了从联邦源表到Amazon Redshift目标表的同步方法：

SQL

CREATE TABLE public.pg_source ( 
      pk_col   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , data_col VARCHAR(20));
INSERT INTO public.pg_tbl (data_col)
VALUES ('aardvark'),('aardvarks'),('aardwolf'),('aardwolves')
;

在您的Amazon Redshift集群中创建一个包含两行的目标表：

SQL

CREATE TABLE public.rs_target (
      pk_col   BIGINT PRIMARY KEY
    , data_col VARCHAR(20));
INSERT INTO public.rs_tbl
VALUES (1,'aardvark'), (2,'aardvarks')
;

调用Amazon Redshift存储过程以实现表同步：

SQL

CALL sp_sync_get_new_rows(SYSDATE,'apg_tpch.pg_source','public.rs_target','pk_col','public.sp_logs',0);
-- INFO:  SUCCESS - 2 new rows inserted into `target_table`.

SELECT * FROM public.rs_tbl;
--  pk_col |  data_col  
-- --------+------------
--       1 | aardvark  
--       2 | aardvarks  
--       4 | aardwolves
--       3 | aardwolf

合并远程数据变更

在对远程表进行行更新或插入之后，大家可以定期将远程表中的变更行与新行合并到副本中来，借此完成Amazon Redshift副本同步。这种方法特别适合对表内变更做出明确标记的情况，这样我们可以轻松对新行或变更过的行进行检索。大家可以使用GitHub上的示例存储过程[sp_sync_merge_changes](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/StoredProcedures/sp_sync_merge_changes.sql)。

此示例存储过程要求源表中包含一个date/time列，该列用于指示上一次各行的修改时间。它使用此列查找需要同步的变更，更新发生变更的行或在Amazon Redshift副本中插入新行。该存储过程还要求表中包含已声明的主键，它会使用主键以识别需要在本地数据副本中更新的行。

以下示例代码，演示了如何根据联邦源表对Amazon Redshift目标表进行刷新。首先，我们在Amazon Redshift集群中创建一个包含两行的示例表：

SQL

CREATE TABLE public.rs_tbl ( 
      pk_col   INTEGER PRIMARY KEY
    , data_col VARCHAR(20)
    , last_mod TIMESTAMP);
INSERT INTO public.rs_tbl 
VALUES (1,'aardvark', SYSDATE), (2,'aardvarks', SYSDATE);

SELECT * FROM public.rs_tbl;
--  pk_col |  data_col  |     last_mod
-- --------+------------+---------------------
--       1 | aardvark   | 2020-04-01 18:01:02
--       2 | aardvarks  | 2020-04-01 18:01:02

在我们的PostgreSQL数据库中创建一个包含四行的源表：

SQL

CREATE TABLE public.pg_tbl (` `
      pk_col   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , data_col VARCHAR(20)
    , last_mod TIMESTAMP);
INSERT INTO public.pg_tbl (data_col, last_mod)
VALUES ('aardvark', NOW()),('aardvarks', NOW()),('aardwolf', NOW()),('aardwolves', NOW());

调用Amazon Redshift存储过程以实现表同步：

SQL

CALL sp_sync_merge_changes(SYSDATE,'apg_tpch.pg_tbl','public.rs_tbl','last_mod','public.sp_logs',0);
-- INFO:  SUCCESS - 4 rows synced.

SELECT * FROM public.rs_tbl;
--  pk_col |  data_col  |      last_mod
-- --------+------------+---------------------
--       1 | aardvark   | 2020-04-01 18:09:56
--       2 | aardvarks  | 2020-04-01 18:09:56
--       4 | aardwolves | 2020-04-01 18:09:56
--       3 | aardwolf   | 2020-04-01 18:09:56

适用于Aurora或Amazon RDS的最佳实践

以下几项最佳实践适用于配合Aurora或者Amazon RDS for PostgreSQL实例使用的Amazon Redshift联邦查询。

7. 使用只读副本尽可能减少对Aurora或RDS的影响

Aurora以及Amazon RDS都允许我们为PostgreSQL实例配置一个或多个只读副本。截至本文撰稿时，Federated Query还不允许对联邦数据库进行写入，因此大家应将这些只读端点指定为外部schema的目标。这也能保证Amazon Redshift发出的联邦子查询不致过度影响主数据库实例的性能表现，使主数据库实例专注于运行大批量、小型、快速写入事务。

关于只读副本的更多详细信息，请参阅向数据库集群内添加Aurora副本以及在Amazon RDS中使用PostgreSQL只读副本。

以下示例代码，使用一个只读端点创建出外部schema。大家可以看到端点URI配置中的-ro命名，表示对象为只读副本：

SQL

--In Amazon Redshift: 
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etl
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-etl-secret-187Asd';

8. 对于各个用例，请使用特定且受限的PostgreSQL用户

正如第一项最佳实践中关于单独外部schema的表述，请考虑为每项联邦查询用例创建单独的PostgreSQL用户。通过多个用户，我们可以保证仅为特定用例授权必要的访问权限。不同用户需要对应不同的 SECRET_ARN（用于容纳访问凭证）以供Amazon Redshift外部schema使用。具体请参见以下代码：

SQL

-- Create an ETL user who will have broad access
CREATE USER redshift_etl WITH PASSWORD '<<example>>';
-- Create an Ad-Hoc user who will have limited access
CREATE USER redshift_adhoc WITH PASSWORD '<<example>>';

使用用户超时以限制查询运行时间

考虑在PostgreSQL用户上设置一条statement_timeout。用户查询可能会在不自觉中尝试对外部关系进行多达数百万行的高强度检索，且长时间保持运行，这将严重占用Amazon Redshift与PostgreSQL中的开放资源。为了避免这种情况，请根据不同用户的预期作用为其指定不同的超时值。以下示例代码，将为ETL用户与即席报告用户设置超时：

SQL

-- Set ETL user timeout to 1 hour
ALTER USER redshift_etl SET statement_timeout TO 3600000;
-- Set Ad-Hoc user timeout to 15 minutes
ALTER USER redshift_adhoc SET statement_timeout TO 900000;

9. 确保PostgreSQL表已恰当使用索引

考虑添加或者修改PostgreSQL索引，以确保Amazon Redshift联邦查询得以高效运行。Amazon Redshift会使用常规SQL查询从远程PostgreSQL数据库中检索数据。在使用索引的情况下，查询速度通常会更快，这一点在仅查询表内一小部分数据时体现得尤其明显。

考虑以下指向lineitem表的Amazon Redshift联邦查询代码示例：

SQL

SELECT AVG( ( l_extendedprice * l_discount) / l_quantity ) AS avg_disc_val
FROM apg_tpch.lineitem
WHERE l_shipdate >= DATE '1994-02-01'
  AND l_shipdate < (DATE '1994-02-01' + INTERVAL '1 day');

Amazon Redshift将重写上述代码，以供下列联邦子查询在PostgreSQL中运行：

SQL

SELECT pg_catalog."numeric"(l_discount)
     , pg_catalog."numeric"(l_extendedprice)
     , pg_catalog."numeric"(l_quantity) 
FROM public.lineitem  
WHERE (l_shipdate < '1994-02-02'::date) 
  AND (l_shipdate >= '1994-02-01'::date);

如果没有索引，我们将从PostgreSQL处得到以下计划：

SQL

<< POSTGRESQL >> QUERY PLAN [No Index]
--------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..16223550.40 rows=232856 width=17)
   Workers Planned: 2
   ->  Parallel Seq Scan on lineitem  (cost=0.00..16199264.80 rows=97023 width=17)
         Filter: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))

大家可以添加以下索引，涵盖本次查询所需要的全部数据：

SQL

CREATE INDEX lineitem_ix_covering 
ON public.lineitem (l_shipdate, l_extendedprice, l_discount, l_quantity);

有了新的索引，我们将看到以下计划：

SQL

<< POSTGRESQL >> QUERY PLAN [With Covering Index]
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lineitem  (cost=7007.35..839080.74 rows=232856 width=17)
   Recheck Cond: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))
   ->  Bitmap Index Scan on lineitem_ix_covering  (cost=0.00..6949.13 rows=232856 width=0)
         Index Cond: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))

在修订后的计划中，最大成本为839080，而原始成本为16223550——削减至后者的十九分之一。成本的降低，代表着使用索引时的查询速度更快，但我们还需要通过测试进一步确认这一点。

大家需要谨慎对待索引。受篇幅所限，本文暂不讨论在PostgreSQL中添加索引需要考虑的权衡因素、各类特定PostgreSQL索引类型以及索引使用技巧等问题。

10. 使用远程视图替换含过滤条件的joins

大部分分析查询会使用joins以限制查询所返回的行。例如，我们可以将calender_quarter='2019Q4'等谓词应用于date_dim表，而后join至大型事实表。其中date_dim上的过滤器能够将事实表返回的行数降低一个数量级。但截至本文撰稿时，Amazon Redshift还无法将含过滤条件的join下推至联邦关系当中。

考虑以下示例查询，其在两个联邦表间具有一项join：

SQL

SELECT ss_store_sk
,COUNT(ss_quantity) AS count_quantity
,AVG(ss_quantity) AS avg_quantity
FROM apg_tpcds.store_sales
JOIN apg_tpcds.date_dim
  ON d_date_sk = ss_sold_date_sk
WHERE d_quarter_name = '1998Q1'
GROUP BY ss_store_sk
ORDER BY ss_store_sk
LIMIT 100;

当我们在Amazon Redshift中EXPLAIN此查询时，将看到以下计划：

SQL

<< REDSHIFT >> QUERY PLAN [Original]
----------------------------------------------------------------------------------------------------------------------------------
<< snip >>
  ->  XN PG Query Scan store_sales  (cost=0.00..576019.84 rows=28800992 width=12)
        ->  Remote PG Seq Scan store_sales  (cost=0.00..288009.92 rows=28800992 width=12)
  ->  XN Hash  (cost=1643.60..1643.60 rows=73049 width=4)
        ->  XN PG Query Scan date_dim  (cost=0.00..1643.60 rows=73049 width=4)
              ->  Remote PG Seq Scan date_dim  (cost=0.00..913.11 rows=73049 width=4)
                    Filter: (d_quarter_name = '1998Q1'::bpchar)

查询计划显示 date_dim已被过滤，但store_sales并不具备过滤器。这意味着Amazon Redshift从store_sales检索所有行，而后才使用join对各行进行过滤。因为store_sales是个巨大的表，所以整个过程可能耗费很长时间，这在定期运行此项查询的场景下会带来更严重的问题。

为了解决问题，我们可以在PostgreSQL中创建以下视图以封装此join：

SQL

CREATE VIEW vw_store_sales_quarter
AS SELECT ss.*, dd.d_quarter_name ss_quarter_name 
FROM store_sales   ss 
JOIN date_dim      dd 
    ON ss.ss_sold_date_sk = dd.d_date_sk;

Rewrite the Amazon Redshift query to use the view as follows:

SQL

SELECT ss_store_sk
      ,COUNT(ss_quantity) AS count_quantity
      ,AVG(ss_quantity) AS avg_quantity
FROM apg_tpcds_10g.vw_store_sales_date
WHERE ss_quarter_name = '1998Q1'
GROUP BY ss_store_sk
ORDER BY ss_store_sk
LIMIT 100;

在Amazon Redshift EXPLAIN这条重新编写的查询之后，我们会看到以下计划：

SQL

<< REDSHIFT >> QUERY PLAN [Remote View]
----------------------------------------------------------------------------------------------------------------------------------
<< snip >>
  ->  XN HashAggregate  (cost=30.00..31.00 rows=200 width=8)
        ->  XN PG Query Scan vw_store_sales_date  (cost=0.00..22.50 rows=1000 width=8)
              ->  Remote PG Seq Scan vw_store_sales_date  (cost=0.00..12.50 rows=1000 width=8)
                    Filter: (ss_quarter_name = '1998Q1'::bpchar)

Amazon Redshift现在将过滤器下推到我们的视图当中。其中的含过滤条件的join可正确应用于PostgreSQL，且返回至Amazon Redshift的行数也更少。大家可能会注意到， Remote PG Seq Scan现在显示rows=1000；这是在PostgreSQL无法提供表统计信息时，查询优化器所使用的默认值。

总结

本文回顾了能够帮助大家尽可能提升Amazon Redshift联邦查询性能的十项最佳实践。当然，每项实践都对应特定用例，请在具体采用之前认真评估您的当前场景是否与之匹配。

AWS将继续增强并改进Amazon Redshift Federated Query，也欢迎大家提供反馈意见。如果您有任何疑问或建议，请在评论中与我们交流。如果您需要进一步帮助以优化Amazon Redshift集群，请联系您的AWS客户服务团队。

这里要特别鸣谢AWS同事Sriram Krishnamurthy、Entong Shen、Niranjan Kamat、Vuk Ercegovac以及Ippokratis Pandis为本文编撰提供的帮助与支持。

作者介绍：

Joe Harris

Joe Harris 是 AWS 的高级 Redshift 数据库工程师，专注于 Redshift 性能问题研究他从事各种平台数据分析和数据仓库构建工作已有二十年之久。在加入 AWS 之前，自 2013 年 Redshift 发布时，他就一直是 Redshift 客户，同时也是 Redshift 论坛中的杰出贡献者。

本文转载自亚马逊AWS官方博客。

原文链接：

Amazon Redshift Federated Query 最佳实践

创作场景

Amazon Redshift Federated Query 最佳实践