Redshift执行计划出现未关联表间连接的原因及解决方法

问题背景 #

在Redshift运行包含多INNER JOIN、LEFT JOIN的复杂查询时，耗时数小时仍无结果。排查发现执行计划中出现两个未在查询中直接关联的表（分别约100万条、1.5万条记录）仅通过分布键（distributed key）连接，结果接近笛卡尔积，监控显示这个“非预期”连接生成了数十亿条记录的内部工作表。

补充信息：使用S3存储的PARQUET格式外部表；给其中一个表添加过滤条件后，执行计划改变，该意外连接消失。同时监控发现，实际仅有数千条记录的table1，在处理步骤中被扫描了数百万行。

优化器执行该意外连接的原因 #

• 统计信息严重失真：Redshift优化器依赖表的统计信息（数据量、分布、过滤后行数等）选择执行策略。S3外部表如果未及时更新统计信息，优化器会做出错误预估——比如执行计划中table1的预估行数是5000万，远大于实际的数千条，这会让优化器误判连接收益，选择提前用分布键连接，最终导致笛卡尔积。
• 谓词下推失效与连接顺序误判：复杂多JOIN场景下，优化器可能无法正确识别谓词的依赖关系，本该延后执行的连接被提前触发。当没有过滤条件时，优化器误以为通过分布键提前连接能减少节点间数据传输，却忽略了这两个表在业务上没有关联，仅靠分布键连接就是无意义的笛卡尔积。
• 外部表元数据不足：S3外部表的分区信息、元数据如果不完整，优化器无法准确评估过滤后的数据集大小，进而做出错误的连接决策。

预防与优化方法 #

• 强制更新统计信息：对涉及的所有外部表执行ANALYZE schemaname.table_name;，确保优化器拿到准确的统计数据，修正行数预估偏差。
• 用查询提示指定连接顺序：使用/*+ LEADING(table_small table_filtered) */这类hint，强制优化器先执行数据量小、有过滤条件的连接，避免无意义的提前连接。
• 添加明确过滤条件：正如你发现的，给其中一个表加过滤条件后执行计划恢复正常——过滤大幅减少数据量，优化器会重新评估连接策略，放弃低效的分布键连接。
• 调整分布键设计：如果多个表分布键相同但业务无关联，考虑将小表改为ALL分布（复制到所有节点），或调整部分表的分布键，避免优化器误判连接收益。
• 拆分复杂查询：把大查询拆成多个CTE或子查询，分步计算中间结果，让优化器更容易识别每个步骤的数据量和逻辑，减少错误决策。

执行计划片段 #

->  XN Hash Join DS_BCAST_INNER  (cost=740000221.79..1073625281.79 rows=50000000 width=70)
  Hash Cond: ("outer".distfield = "inner".distfield)
  Remarks: Derives subplan 3
  ->  XN Partition Loop  (cost=0.00..251000060.00 rows=50000000 width=44)
        ->  XN Seq Scan PartitionInfo of schemaname.table1  (cost=0.00..60.00 rows=1 width=8)
              Filter: (((((filterfieldview)::text = 'v1'::text) AND (distfield = 26)) AND (subplan 3: ($7 = distfield)) AND (subplan 4: ($9 = distfield)) AND (subplan 5: ($13 = distfield)) AND (subplan 6: ($14 = distfield)) AND (subplan 7: ($16 = distfield)) AND (subplan 8: ($19 = distfield)) AND (subplan 9: (distfield = $22)) AND (subplan 10: (distfield = $25)) AND (subplan 11: ($28 = distfield)) AND (subplan 12: ($31 = distfield)) AND (subplan 13: ($34 = distfield)) AND (subplan 14: ($36 = distfield)) AND (subplan 15: (distfield = $40)) AND (subplan 16: ($46 = distfield)) AND (subplan 17: (distfield = $50)) AND (subplan 18: ($52 = distfield)))
        ->  XN S3 Query Scan table1  (cost=0.00..125500000.00 rows=50000000 width=36)
              ->  S3 Seq Scan schemaname.table1 location:"s3://s3location/TABLE1" format:PARQUET  (cost=0.00..125000000.00 rows=50000000 width=36)
                    Filter: ((condition1)::text = 'A'::text)
  ->  XN Hash  (cost=740000221.29..740000221.29 rows=200 width=26)
        ->  XN Subquery Scan volt_dt_11  (cost=740000219.29..740000221.29 rows=200 width=26)
              ->  XN HashAggregate  (cost=740000219.29..740000219.29 rows=200 width=26)
                    ->  XN Hash Join DS_DIST_BOTH  (cost=650000097.51..740000217.56 rows=347 width=26)
                          Outer Dist Key: "inner".derived_col1
                          Inner Dist Key: ref_darwin_lookup.derived_col1
                          Hash Cond: (("outer".derived_col1 = "inner".derived_col1) AND ("outer".distfield = "inner".distfield))
                          ->  XN Partition Loop  (cost=300000006.25..300000101.25 rows=1000 width=26)
                                ->  XN Seq Scan PartitionInfo of schemaname.table2  (cost=0.00..75.00 rows=1 width=8)
                                      Filter: ((((filterfieldview)::text = 'v1'::text) AND (distfield = 26)))
                                ->  XN S3 Query Scan table2  (cost=150000003.13..150000013.13 rows=1000 width=18)
                                      ->  S3 HashAggregate  (cost=150000003.13..150000003.13 rows=1000 width=18)
                                            ->  S3 Seq Scan schemaname.table2 location:"s3://s3location/TABLE2" format:PARQUET  (cost=0.00..150000000.00 rows=1251 width=18)
                                                  Filter: (((final_value)::text = 'T'::text) AND ((condition1)::text = 'A'::text) AND (condition2 IS NULL))
                          ->  XN Hash  (cost=350000086.26..350000086.26 rows=1000 width=26)
                                ->  XN Partition Loop  (cost=350000006.26..350000086.26 rows=1000 width=26)
                                      ->  XN Seq Scan PartitionInfo of schemaname.ref_darwin_lookup  (cost=0.00..60.00 rows=1 width=8)
                                            Filter: ((((filterfieldview)::text = 'v1'::text) AND (distfield = 26)))
                                      ->  XN S3 Query Scan ref_darwin_lookup  (cost=175000003.13..175000013.13 rows=1000 width=18)
                                            ->  S3 HashAggregate  (cost=175000003.13..175000003.13 rows=1000 width=18)
                                                  ->  S3 Seq Scan schemaname.ref_darwin_lookup location:"s3://s3location/LOOKUP/" format:PARQUET  (cost=0.00..175000000.00 rows=1251 width=18)
                                                        Filter: (((condition1)::text = 'TYPE2'::text) AND ((condition2)::text = 'Numeric'::text) AND ((condition3)::text = 'A'::text))
->  XN Hash  (cost=300000095.02..300000095.02 rows=1251 width=44)
  ->  XN Partition Loop  (cost=0.00..300000095.02 rows=1251 width=44)
        ->  XN Seq Scan PartitionInfo of schemaname.table3 volt_dt_8  (cost=0.00..70.00 rows=1 width=8)
              Filter: ((((filterfieldview)::text = 'v1'::text) AND (distfield = 31)))
        ->  XN S3 Query Scan volt_dt_8  (cost=0.00..150000012.51 rows=1251 width=36)
              ->  S3 Seq Scan schemaname.table3 volt_dt_8 location:"s3://s3location/TABLE3" format:PARQUET  (cost=0.00..150000000.00 rows=1251 width=36)
                    Filter: (((condition3)::text = 'T'::text) AND ((condition1)::text = 'A'::text) AND (condition2 IS NULL))

内容的提问来源于stack exchange，提问作者Teuh1975