Bigquery“不在”比较性能太慢

NOT IN 和 NOT EXISTS 现在表现得更慢了。

我创建了一个bug来跟踪这个性能问题,因为它应该只是暂时的( https://issuetracker.google.com/issues/116839201 ).

SELECT tags, COUNT(*) c, ANY_VALUE(b.value)
FROM `bigquery-public-data.stackoverflow.posts_questions` a
LEFT JOIN (SELECT x.value FROM UNNEST((
  SELECT APPROX_TOP_COUNT(tags, 10000) 
  FROM `bigquery-public-data.stackoverflow.posts_questions` 
)) x ) b
ON a.tags=b.value
WHERE b.value IS NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000

12 seconds, fh-bigquery:US.bquijob_3c0fdf82_1661f6f3dd1

SELECT tags, COUNT(*) c
FROM `bigquery-public-data.stackoverflow.posts_questions` 
WHERE tags NOT IN(SELECT x.value FROM UNNEST((
  SELECT APPROX_TOP_COUNT(tags, 10000) 
  FROM `bigquery-public-data.stackoverflow.posts_questions` 
)) x)
GROUP BY 1
ORDER BY 2 DESC, 1
LIMIT 1000

> 400 seconds, fh-bigquery:US.bquijob_766cc8ab_1661f7023bb

SELECT tags, COUNT(*) c
FROM `bigquery-public-data.stackoverflow.posts_questions` 
WHERE NOT EXISTS(SELECT x.value FROM UNNEST((
  SELECT APPROX_TOP_COUNT(tags, 10000) 
  FROM `bigquery-public-data.stackoverflow.posts_questions` 
)) x WHERE tags=value)
GROUP BY 1
ORDER BY 2 DESC, 1
LIMIT 1000

> 400 seconds, fh-bigquery:US.bquijob_59a9d1e6_1661f59db40

一般来说: 不存在 应该比 ,因为它在空值下表现更好。