“ IN”子句中的参数个数？

在 Postgres，您可以指定一个 IN 子句，如下所示:

SELECT * FROM user WHERE id IN (1000, 1001, 1002)

有人知道可以传递给 IN 的参数的最大数目是多少吗？

146862 次浏览
小开

您可能需要考虑重构该查询，而不是添加任意长的 id 列表... ... 如果 id 确实遵循您的示例中的模式，您可以使用一个范围:

SELECT * FROM user WHERE id >= minValue AND id <= maxValue;

另一个选项是添加一个内部 select:

SELECT *
FROM user
WHERE id IN (
SELECT userId
FROM ForumThreads ft
WHERE ft.id = X
);
小开

根据位于 这里，从850行开始,的源代码 PostgreSQL 没有明确限制参数的数量。

以下是来自第870行的代码注释:

/*
* 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.
*/
小开

传递给 IN 子句的元素数没有限制。如果有更多的元素，它会将其视为数组，然后对于数据库中的每次扫描，它会检查它是否包含在数组中。这种方法没有那么大的可伸缩性。不要使用 IN 子句，尝试在临时表中使用 INNERJOIN。更多信息请参考 http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/。使用 INNERJOIN 扩展和查询优化器可以利用哈希连接和其他优化。而在 IN 子句中，优化器无法优化查询。我已经注意到这个变化至少加速了2倍。

小开

如果你有以下疑问:

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

如果像下面这样重写查询，可能会提高性能:

SELECT * FROM user WHERE id = ANY(VALUES (1), (2), (3), (4) -- and thousands of another keys)
小开 
explain select * from test where id in (values (1), (2));

查询计划

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

但如果尝试第二个查询:

explain select * from test where id = any (values (1), (2));

查询计划

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)

我们可以看到 postgres 构建临时表并与其连接

小开

这实际上不是对目前问题的回答，但它也可能对其他人有所帮助。

至少我可以告诉大家，通过使用 Postgreql 的 JDBC 驱动程序9.1，可以传递到 PostgreSQL 后端的32767个值(= Short.MAX _ VALUE)在技术上是有限制的。

这是“ delete from x where id in (... 100k value...)”的测试，使用 postgreql jdbc 驱动程序:

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)
小开

作为对 Oracle DB 更有经验的人，我也担心这个限制。我在 IN-list 中对具有约10’000个参数的查询执行了一次性能测试，从一个具有前100’000个整数 通过实际列出所有的素数作为查询参数的表中获取最多100’000个素数。

我的结果表明，您不必担心查询计划优化器的过载或在没有使用索引的情况下获得计划，因为它将转换查询使用 = ANY({...}::integer[])，它可以利用索引，如预期的:

-- prepare statement, runs instantaneous:
PREPARE hugeplan (integer, integer, integer, ...) AS
SELECT *
FROM primes
WHERE n IN ($1, $2, $3, ..., $9592);


-- fetch the prime numbers:
EXECUTE hugeplan(2, 3, 5, ..., 99991);


-- EXPLAIN ANALYZE output for the EXECUTE:
"Index Scan using n_idx on primes  (cost=0.42..9750.77 rows=9592 width=5) (actual time=0.024..15.268 rows=9592 loops=1)"
"  Index Cond: (n = ANY ('{2,3,5,7, (...)"
"Execution time: 16.063 ms"


-- setup, should you care:
CREATE TABLE public.primes
(
n integer NOT NULL,
prime boolean,
CONSTRAINT n_idx PRIMARY KEY (n)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.primes
OWNER TO postgres;


INSERT INTO public.primes
SELECT generate_series(1,100000);

但是，这个(相当老的) Pgsql-hacker 邮件列表上的线程表明，在规划这样的查询时仍然有不可忽视的成本，因此请不要轻信我的话。

小开

刚试过，答案是-> 超出范围的整数作为2字节值: 32768


提交答案