Does WHERE Clause Order Affect BigQuery Performance?

Does the order of expressions in a WHERE clause matter for performance?

So an interesting point found in a Google Cloud blog post was the fact that this expression order matters, with BigQuery assuming that the user has provided the best order of expressions in the WHERE clause, so it would not reorder expressions.

The recommendation given is to put the most selective expression first - basically, the one that narrows down the result set the most.

For testing it out, I've picked a ~200 million rows table (the publicly available google_trends.international_top_terms) .

The two scenarios to be tested were the ones presented in the same blog post - an exact match on a string and a wildcard lookup, then switching their order in the WHERE clause.

Now, the blog post is already more than 2 years old and some things might have changed, given I wasn't able to replicate the results very well.

The results have shown almost no difference between the two approaches (across several attempts), but another reason might be the table is just not big enough for me to see a difference.

In any case, I'll keep this in mind next time I'm working with a very big table and check it out again.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Enjoyed this? Here are some related articles you might find useful:

Does order of expressions in the WHERE clause matter?

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog