Redshift带条件左连接场景下如何设置distkey？

Hey there, let's dig into why you're hitting that frustrating DS_BCAST_INNER step and slow query times even with id set as the distkey. Here's what's likely going on, and how to fix it:

Common Causes #

• Outdated statistics: Redshift's query optimizer relies heavily on up-to-date table statistics to choose the best join strategy. If stats for tables b or c are stale, it might misjudge the size of the filtered data (from b.attribute_id=3 and c.attribute_id=4) and opt for a broadcast join when a more efficient local hash join is possible.
• Mismatched distkeys: Even if a uses id as its distkey, if tables b and c don't also have id as their distkey, Redshift can't match rows locally across nodes. This forces it to broadcast the inner tables to all nodes, which gets slow with large datasets.
• Misestimated filter selectivity: If the optimizer thinks the attribute_id filters return a tiny subset of data, it'll pick broadcasting—but if the actual filtered dataset is much larger, this becomes a bottleneck.

Fixes to Try #

• Update table statistics first: Run these commands to refresh stats so the optimizer has accurate data to work with:

  ANALYZE a;
  ANALYZE b;
  ANALYZE c;
  

  This is the most common fix for misplanned joins, especially if you've recently loaded or updated data in these tables.

• Align distkeys for joined tables: If b and c are frequently joined with a on id, change their distkey to id too. This ensures rows with the same id live on the same node, letting Redshift perform joins locally without broadcasting.

• Pre-filter with subqueries/CTEs: Explicitly pre-filter tables b and c to help the optimizer see the actual size of the data it's joining:

  SELECT a.col1, a.col2, b_filtered.col3
  FROM a
  LEFT JOIN (
      SELECT id, col3 FROM b WHERE attribute_id = 3
  ) b_filtered ON a.id = b_filtered.id
  LEFT JOIN (
      SELECT id, colX FROM c WHERE attribute_id = 4
  ) c_filtered ON a.id = c_filtered.id;
  

  This makes it clearer to the optimizer that the inner datasets might be large enough to skip broadcasting.

• Use query hints (last resort): If the optimizer still insists on broadcasting despite updated stats and aligned distkeys, you can force a hash join with hints:

  SELECT /*+ HASHJOIN(b) HASHJOIN(c) */
  a.col1, a.col2, b.col3
  FROM a
  LEFT JOIN b ON (a.id = b.id AND b.attribute_id = 3)
  LEFT JOIN c ON (a.id = c.id AND c.attribute_id = 4);
  

  Or explicitly disable broadcasting:

  SELECT /*+ NO_BROADCAST(b) NO_BROADCAST(c) */
  a.col1, a.col2, b.col3
  FROM a
  LEFT JOIN b ON (a.id = b.id AND b.attribute_id = 3)
  LEFT JOIN c ON (a.id = c.id AND c.attribute_id = 4);
  

  Just keep in mind hints can become ineffective as your data changes, so prefer fixing the root causes first.

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