重写大IN子句的最有效方法是什么？ [英] What is the most performant way to rewrite a large IN clause?
问题描述
我使用go and gorm编写了一个API，该API在我们的数据库上运行计算并返回结果。
I wrote an API using go and gorm that runs calculations on our database and returns the results.
我刚刚达到了
I just hit the parameter limit for an IN condition when using an aggregate. Example query:
SELECT SUM(total_amount) from Table where user_id in(...70k parameters) group by user_id
我当前的一个边缘案例有> 65535个用户ID，因此我的Postgres客户端抛出错误：
One of my current edge cases has > 65535 user ids so my Postgres client is throwing an error:
got 66037 parameters but PostgreSQL only supports 65535 parameters
我不确定实现此目标的最佳方法是什么。在不影响我的典型用例的情况下，可以处理大量这种情况的参数。我是否会对ID进行分块并遍历存储在内存中的多个查询，直到获得所需的所有数据为止？使用 ANY（VALUES） ...
I'm not sure what the best way to approach this is. One that will handle the large amount of parameters for this edge case while not affecting my typical use case. Do I chunk the ids and iterate through multiple queries storing it in memory until I have all the data I need? Use ANY(VALUES)...
显然，从查询中我对Postgres的了解非常有限，因此任何帮助
Obviously from the query I have very limited knowledge of Postgres so any help would be incredibly appreciated.
推荐答案
您可以替换 user_id IN（值[，...]） ，其中之一：
You can replace user_id IN (value [, ...]) with one of:
user_id IN (subquery)
user_id = ANY (subquery)
user_id = ANY (array expression)
子查询和数组都没有相同的限制。最短的输入语法为：
Neither subqueries nor arrays exhibit the same limitation. The shortest input syntax would be:
user_id = ANY ('{1,2,3}'::int[]) -- make array type match type of user_id
详细信息和更多选项的类型：
Details and more options:
- How to use ANY instead of IN in a WHERE clause with Rails?
或者，您可以创建一个（临时）表 tmp_usr（user_id int），导入到其中，也许使用SQL COPY 或psql \copy 代替 INSERT 以获得最佳性能，同时非常大的集，然后加入到表中，例如：
Or you might create a (temporary) table tmp_usr(user_id int), import to it, maybe with SQL COPY or psql \copy instead of INSERT for best performance with very big sets, and then join to the table like:
SELECT SUM(total_amount)
FROM tbl
JOIN tmp_usr USING (user_id)
GROUP BY user_id;
BTW， GROUP BY user_id ，不包括< SELECT 列表中的code> user_id 看起来可疑。可能是一个简化的示例查询。
BTW, GROUP BY user_id without including user_id in the SELECT list looks suspicious. May be a simplified example query.
这篇关于重写大IN子句的最有效方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
