BigQuery技术疑问：临时表查询耗时为何远超全量数据集聚合？

Let’s walk through the most likely reasons your aggregation query runs so much slower on a temporary table compared to your original dataset.transactions table—especially given your context (5M rows, 10s on the full table vs. 200s+ on the temp table):

• Your Temp Table Lacks Partitioning/Clustering
  Chances are your original transactions table is partitioned (e.g., by date or year) or clustered (e.g., by salesrep_id). When you run that aggregation query on the full table, BigQuery uses these optimizations to skip scanning irrelevant data: for the MAX(date) subquery, it might only look at the most recent partitions, or use clustering to quickly locate the latest record per salesrep without scanning every row.

  Temporary tables don’t inherit these settings automatically. They’re stored as unpartitioned, unclustered by default, so every time the subquery runs for a salesrep_id, it has to scan the entire temp table. That’s a ton of extra data processing, which adds up fast.

• The Optimizer Doesn’t Have Good Stats for the Temp Table
  BigQuery automatically collects detailed stats (like row counts, value distributions, and data sizes) for permanent tables. The query optimizer uses this info to pick the fastest execution plan—like choosing a hash join over a slow nested loop, or knowing exactly how much data to shuffle for the GROUP BY.

  Temp tables often have incomplete or no stats, especially if you create and use them in the same session. Without this context, the optimizer might pick a suboptimal plan: maybe it does a full cross join instead of rewriting the correlated subquery into something faster, or allocates too few resources for sorting and grouping.

• Temp Table Data Isn’t Optimized for Analytics
  Permanent tables in BigQuery are stored in a columnar format that’s sorted, compressed, and optimized for analytical queries. When you create a temp table (say, via CREATE TEMP TABLE AS SELECT ...), the data is written in a less efficient format—no sorting, weaker compression, and no organized layout.

  For your query, which groups by salesrep_id and finds the latest date, this unoptimized layout means BigQuery has to do way more work to sort and group records. Shuffling data across workers takes longer, and CPU usage spikes compared to the neatly organized original table.

• Temp Tables Miss Out on Advanced Optimizations
  BigQuery’s optimizer has special tricks for permanent tables—like rewriting correlated subqueries into faster joins, or leveraging materialized views if you have them. Temp tables are treated as ephemeral, short-lived objects, so they don’t get these optimizations.

  Take your query’s correlated subquery to find the latest date per salesrep. On the permanent table, the optimizer might rewrite this to use a window function (like ROW_NUMBER() OVER (PARTITION BY salesrep_id ORDER BY date DESC)) which is way faster. But on the temp table, it might stick with the original subquery pattern, which runs slow for large datasets.

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