blocks|key|1312211|text|您可能希望考虑重构该查询，而不是添加任意长的ids列表...如果in确实遵循示例中的模式，则可以使用范围：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1312212|SELECT+*+FROM+user+WHERE+id+>=+minValue+AND+id+<=+maxValue;|code-block|syntax|javascript|1312213|另一种选择是添加一个内部select：|1312214|SELECT+*+
FROM+user+
WHERE+id+IN+(
++++SELECT+userId
++++FROM+ForumThreads+ft
++++WHERE+ft.id+=+X
);|1312215|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You might want to consider refactoring that query instead of adding an arbitrarily long list of ids... You could use a range if the ids indeed follow the pattern in your example:

<pre><code>SELECT * FROM user WHERE id &gt;= minValue AND id &lt;= maxValue;
</code></pre>

Another option is to add an inner select:

<pre><code>SELECT * 
FROM user 
WHERE id IN (
 SELECT userId
 FROM ForumThreads ft
 WHERE ft.id = X
);
</code></pre>

blocks|key|1312242|text|根据找到的源代码，here,+starting+at+line+850,+PostgreSQL没有显式限制参数的数量。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1312243|下面是870行的代码注释：|1312244|/*
+*+We+try+to+generate+a+ScalarArrayOpExpr+from+IN/NOT+IN,+but+this+is+only
+*+possible+if+the+inputs+are+all+scalars+(no+RowExprs)+and+there+is+a
+*+suitable+array+type+available.++If+not,+we+fall+back+to+a+boolean
+*+condition+tree+with+multiple+copies+of+the+lefthand+expression.
+*+Also,+any+IN-list+items+that+contain+Vars+are+handled+as+separate
+*+boolean+conditions,+because+that+gives+the+planner+more+scope+for
+*+optimization+on+such+clauses.
+*
+*+First+step:+transform+all+the+inputs,+and+detect+whether+any+are
+*+RowExprs+or+contain+Vars.
+*/|code-block|syntax|javascript|1312245|entityMap|0|LINK|mutability|MUTABLE|url|http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_expr.c?rev=1.226.2.3;only_with_tag=REL8_3_7;ln=1^0|9|R|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

According to the source code located <a href="http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_expr.c?rev=1.226.2.3;only_with_tag=REL8_3_7;ln=1" rel="noreferrer">here, starting at line 850,</a> PostgreSQL doesn't explicitly limit the number of arguments.

The following is a code comment from line 870:

<pre><code>/*
 * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
 * possible if the inputs are all scalars (no RowExprs) and there is a
 * suitable array type available. If not, we fall back to a boolean
 * condition tree with multiple copies of the lefthand expression.
 * Also, any IN-list items that contain Vars are handled as separate
 * boolean conditions, because that gives the planner more scope for
 * optimization on such clauses.
 *
 * First step: transform all the inputs, and detect whether any are
 * RowExprs or contain Vars.
 */
</code></pre>

blocks|key|1312303|text|传递给IN子句的元素数量没有限制。如果有更多的元素，它会将其视为数组，然后对于数据库中的每次扫描，它将检查它是否包含在数组中。这种方法的可扩展性不是很好。尝试使用INNER+JOIN+with+temp+table，而不使用IN子句。有关更多信息，请参阅http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/。很好地使用内连接尺度作为查询优化器，可以利用散列连接和其他优化。而with+IN子句则无法让优化器优化查询。我注意到这一变化至少加速了2倍。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1312304|entityMap|0|LINK|mutability|MUTABLE|url|http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/^0|3J|22|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

There is no limit to the number of elements that you are passing to IN clause. If there are more elements it will consider it as array and then for each scan in the database it will check if it is contained in the array or not. This approach is not so scalable. Instead of using IN clause try using INNER JOIN with temp table. Refer <a href="http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/" rel="noreferrer">http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/</a> for more info. Using INNER JOIN scales well as query optimizer can make use of hash join and other optimization. Whereas with IN clause there is no way for the optimizer to optimize the query. I have noticed speedup of at least 2x with this change.

blocks|key|1312327|text|如果您有如下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1312328|SELECT+*+FROM+user+WHERE+id+IN+(1,+2,+3,+4+--+and+thousands+of+another+keys)|code-block|syntax|javascript|1312329|如果重写查询，您可以提高性能，如下所示：|1312330|SELECT+*+FROM+user+WHERE+id+=+ANY(VALUES+(1),+(2),+(3),+(4)+--+and+thousands+of+another+keys)|1312331|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you have query like:

<pre><code>SELECT * FROM user WHERE id IN (1, 2, 3, 4 -- and thousands of another keys)
</code></pre>

you may increase performace if rewrite your query like:

<pre><code>SELECT * FROM user WHERE id = ANY(VALUES (1), (2), (3), (4) -- and thousands of another keys)
</code></pre>

blocks|key|1527456|text|explain+select+*+from+test+where+id+in+(values+(1),+(2));|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1527457|查询计划|unstyled|1527458|+Seq+Scan+on+test++(cost=0.00..1.38+rows=2+width=208)
+++Filter:+(id+=+ANY+('{1,2}'::bigint[]))|1527459|但如果尝试第二个查询：|1527460|explain+select+*+from+test+where+id+=+any+(values+(1),+(2));|1527461|1527462|Hash+Semi+Join++(cost=0.05..1.45+rows=2+width=208)
+++++++Hash+Cond:+(test.id+=+"*VALUES*".column1)
+++++++->++Seq+Scan+on+test++(cost=0.00..1.30+rows=30+width=208)
+++++++->++Hash++(cost=0.03..0.03+rows=2+width=4)
+++++++++++++->++Values+Scan+on+"*VALUES*"++(cost=0.00..0.03+rows=2+width=4)|1527463|我们可以看到postgres构建了临时表并与其连接|1527464|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|W|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|X|8|@]|9|@]|A|$B|C]]|$1|M|3|E|5|F|7|Y|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Z|8|@]|9|@]|A|$B|C]]|$1|P|3|Q|5|F|7|10|8|@]|9|@]|A|$]]|$1|R|3|-4|5|F|7|11|8|@]|9|@]|A|$]]]|S|$]]

<pre><code>explain select * from test where id in (values (1), (2));
</code></pre>

<h2>QUERY PLAN</h2>

<pre><code> Seq Scan on test (cost=0.00..1.38 rows=2 width=208)
 Filter: (id = ANY ('{1,2}'::bigint[]))
</code></pre>

But if try 2nd query:

<pre><code>explain select * from test where id = any (values (1), (2));
</code></pre>

<h2>QUERY PLAN</h2>

<pre><code>Hash Semi Join (cost=0.05..1.45 rows=2 width=208)
 Hash Cond: (test.id = "*VALUES*".column1)
 -&gt; Seq Scan on test (cost=0.00..1.30 rows=30 width=208)
 -&gt; Hash (cost=0.03..0.03 rows=2 width=4)
 -&gt; Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4)
</code></pre>

We can see that postgres build temp table and join with it

blocks|key|1527499|text|这并不是对当前问题的真正答案，但它也可能对其他人有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1527500|至少我可以断定，使用Posgresql+driver+9.1，可以传递给PostgreSQL后端的值(=Short.MAX_VALUE)有32767个的技术限制。|1527501|这是一个测试"delete+from+x+where+id+in+(...+100k+values...)“使用postgresql+jdbc驱动程序：|1527502|Caused+by:+java.io.IOException:+Tried+to+send+an+out-of-range+integer+as+a+2-byte+value:+100000
++++at+org.postgresql.core.PGStream.SendInteger2(PGStream.java:201)|code-block|syntax|javascript|1527503|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This is not really an answer to the present question, however it might help others too.

At least I can tell there is a technical limit of 32767 values (=Short.MAX_VALUE) passable to the PostgreSQL backend, using Posgresql's JDBC driver 9.1.

This is a test of "delete from x where id in (... 100k values...)" with the postgresql jdbc driver:

<pre><code>Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 100000
 at org.postgresql.core.PGStream.SendInteger2(PGStream.java:201)
</code></pre>

blocks|key|1527542|text|我刚试过了。答案是以2字节值表示的->超出范围的整数:+32768|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1527543|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Just tried it. the answer is ->
out-of-range integer as a 2-byte value: 32768

In Postgres, you can specify an IN clause, like this:

<pre><code>SELECT * FROM user WHERE id IN (1000, 1001, 1002)
</code></pre>

Does anyone know what's the maximum number of parameters you can pass into IN?

PostgreSQL - max number of parameters in "IN" clause?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

在Postgres中，您可以指定In子句，如下所示：SELECT * FROM user WHERE id IN (1000, 1001, 1002)有人知道可以传递给IN的最大参数数是多少吗？

问PostgreSQL - " in“子句中参数的最大数目？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL - " in“子句中参数的最大数目？EN