Full Table Scan Despite Clustered Filtering with Small Join Table

Google Cloud Data Analytics
1

Hi all,

I have a large BigQuery table (~600GB) that is clustered on clustered_id, and a super heavy column (text based) called col_a

When I run the following query:

SELECT clustered_id, col_a FROM table WHERE clustered_id IN (111)

…it’s very efficient — only ~50MB scanned, as expected due to clustering.

However, when I run either of these:

-- table_B has only ~20 rows

SELECT clustered_id, col_a FROM table WHERE clustered_id IN (SELECT id FROM table_B)

or

SELECT a.clustered_id, a.col_a FROM table a JOIN table_B b ON a.clustered_id = b.id

…it results in a full table scan — 571.36GB processed. That said, the queries still complete in 2-3 seconds:

  • Duration: 2 sec

  • Bytes processed: 571.36 GB

  • Bytes billed: 571.36 GB

  • Slot milliseconds: 1,020,720

I’m struggling to understand how it’s scanning 570GB that fast, and more importantly, how to reduce slot milliseconds and avoid the full scan.

From my research, it seems BigQuery needs to see a literal list of values at plan time to leverage clustering — otherwise, it won’t prune partitions. But in production, we often can’t hard-code a physical list.

Questions:

  1. Is there a way to hint or force BigQuery to treat the IN (SELECT ...) or JOIN as a list for clustering optimization?

  2. Any workaround to avoid full scan or reduce slot usage when using small join tables like this?

Thanks!

2

Hello @Aaron_Wu,

From BigQuery Best Practices:

The latter may be the real source of your issue because the documentation says:

If a filter expression compares a clustered column to another column (either a clustered column or a non-clustered column), the performance of the query is not optimized because block pruning cannot be applied.

Per my understanding, BigQuery cannot do Block Pruning if a filter is dynamic because it’s not possible to know/plan which blocks are going to be scanned. Using a constant filter expression seems to be the solution.

You may have to build the query dynamically. If I were in your situation, I would use python and probably jinja2 with a Cloud Run / Function.

I’m wondering if it can be done using BigQuery Procedural Language (SET, EXECUTE IMMEDIATE) even though it’s still dynamic. I have tried with mock data using clustering and it didn’t look like it was working but I never used BQ Procedural Language before so I may have missed something. As I said, I would have done that with python and maybe jinja2, especially if it was to avoid huge, avoidable costs.

1 Like
3

Hi Aaron_Wu,

Currently, there’s no direct way to hint or force BigQuery to treat a subquery or join as a literal list for clustering optimization. BigQuery’s optimizer needs a static list of values at query compilation time to effectively prune data based on clustering keys. When you use a subquery or a join, the list of values is dynamic and not known until execution, so the optimizer defaults to a full table scan.

The reason the full scan completes in a few seconds despite processing 571GB is due to BigQuery’s massively parallel architecture. BigQuery utilizes thousands of worker nodes simultaneously to process large datasets. When you run a query, the data is scanned in parallel across many shards. Even though a full table scan is performed, the work is distributed so widely that the total wall-clock time remains very low. The high slot milliseconds (over 1 million in your case) indicate that a significant amount of computational resources (slots) were used, even for a short duration.

Here are some workarounds that might help to avoid full scans and reduce slot usage:

  • Temporary Table: A common and effective strategy is to pre-process your join table (table_B) and use the results to build a list. This approach allows BigQuery to see a concrete list of values during the execution of the second query, which can trigger the clustering optimization. The CREATE TEMP TABLE statement materializes the result of the subquery, making it available for BigQuery’s planner.

  • Use a BigQuery Scripting Block: If you need to execute this as a single atomic operation, you can use a scripting block to first retrieve the IDs into a variable and then use that variable in the main query. This is particularly useful in environments where you can’t create temporary tables.

  • Use an Explicit ARRAY and UNNEST: For a small, known number of values, you can manually create an array. This is similar to the scripting block but doesn’t require a separate SET statement. It’s less dynamic but can be a quick solution for small, known lists of IDs.

Additionally, you may check the following documentations that will help in your use case:

1 Like