PostgreSQL- “IN”절의 최대 매개 변수 수? user WHERE id IN

Postgres에서 다음과 같이 IN 절을 지정할 수 있습니다.

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

누구든지 IN에 전달할 수있는 최대 매개 변수 수를 알고 있습니까?

답변

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

답변

이것은 현재의 질문에 대한 답은 아니지만 다른 사람들에게도 도움이 될 수 있습니다.

적어도 Posgresql의 JDBC 드라이버 9.1을 사용하여 PostgreSQL 백엔드에 전달할 수있는 기술적 인 제한 32767 값 (= Short.MAX_VALUE)이 있음을 알 수 있습니다.

이것은 postgresql jdbc 드라이버를 사용하여 “(여기서 ID를 (… 100k 값 …) 인 x에서 삭제”) 테스트입니다.

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)

답변

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가 임시 테이블을 빌드하고 조인하는 것을 볼 수 있습니다.

답변

IN 절에 전달하는 요소 수에는 제한이 없습니다. 더 많은 요소가 있으면 배열로 간주 한 다음 데이터베이스의 각 스캔에 대해 배열에 포함되어 있는지 확인합니다. 이 접근 방식은 그렇게 확장 가능하지 않습니다. IN 절을 사용하는 대신 임시 테이블과 함께 INNER JOIN을 사용하십시오. 자세한 내용은 http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ 을 참조 하십시오 . INNER JOIN 스케일과 쿼리 최적화 프로그램을 사용하면 해시 조인 및 기타 최적화를 사용할 수 있습니다. IN 절을 사용하면 옵티마이 저가 쿼리를 최적화 할 수있는 방법이 없습니다. 이 변경으로 최소 2 배의 속도가 향상되었습니다.

답변

Oracle DB에 대해 더 많은 경험이있는 사람도이 한계에 대해 걱정했습니다. 나는 목록에 ~ 10,000 매개 변수가있는 쿼리에 대한 성능 테스트를 수행 했으며 실제로 모든 소수를 쿼리 매개 변수로 나열IN 하여 첫 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-hackers 메일 링리스트 의이 오래된 스레드 는 그러한 쿼리를 계획하는 데 여전히 무시할 수없는 비용이 있음을 나타내므로 소금 한 알을 내 말로 가져 가십시오.

답변

다음과 같은 쿼리가있는 경우 :

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)

답변

그냥 해봐 대답은 2 바이트 값으로-> 범위를 벗어난 정수입니다.