Prune before join vs prune after join vs prune during join

Viewed 30

What is the best practice when one needs to do a table join on a table subset? i.e. Consider the following queries:

WITH pruned AS (
SELECT id FROM foo WHERE timestamp >= TIMESTAMP('some timestamp')
)
SELECT bar.* FROM bar
INNER JOIN foo
ON bar.id = pruned.id
SELECT bar.* FROM bar
INNER JOIN foo
ON bar.id = foo.id
WHERE foo.timestamp >= TIMESTAMP('some timestamp')
SELECT bar.* FROM bar
INNER JOIN foo
ON foo.timestamp >= TIMESTAMP('some timestamp')
AND bar.id = foo.id

I'm guessing that last one is technically the most performant approach? Perhaps it depends on the tables' data but I'm guessing BigQuery stores the pruned table temporarily in the first approach so it's the worse one?

0 Answers
Related