BigQuery中Broadcast Join的小表大小限制是多少？

Great question! I’ve run into this confusion too since BigQuery’s official docs are intentionally vague about the exact size threshold for Broadcast Joins, unlike some other SQL engines like Hive that have more explicit limits. Let’s break down what we know from real-world experience and BigQuery’s optimizer behavior:

• The old 8MB figure you referenced is outdated. That was a threshold from earlier versions of BigQuery, but the query optimizer has evolved significantly since then.
• BigQuery doesn’t publish a fixed, universal size limit because it uses a cost-based optimizer that makes dynamic decisions based on multiple factors:
  • The compressed size of the smaller table (this is critical—raw uncompressed size isn’t the metric used)
  • Available compute resources allocated for the query
  • Table structure details like partitioning, clustering, and data types
  • Overall complexity of the query
• In practical terms, most users observe that tables with a compressed size between 10MB and 1GB are prime candidates for Broadcast Join optimization. Beyond this range, the overhead of distributing the entire small table to every worker node typically becomes less efficient than using a Shuffle Join, so the optimizer will default to the latter.
• If you want to explicitly test or enforce a Broadcast Join (to override the optimizer’s choice), you can use the /*+ BROADCAST(small_table) */ query hint. Here’s an example:

  SELECT *
  FROM large_dataset.large_table
  /*+ BROADCAST(small_dataset.small_table) */
  JOIN small_dataset.small_table 
  ON large_table.id = small_table.id
  

  Keep in mind that if the table is excessively large, BigQuery will automatically fall back to a Shuffle Join to prevent performance issues or failures.
• To verify whether a Broadcast Join was actually executed for your query, check the execution plan in the BigQuery console. Look for the "Join" step in the plan—it will clearly label the join type as "Broadcast Join" if that’s what was used.

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