PostgreSQL - "IN" 절의 최대 매개 변수 수?

PostgreSQL - "IN" 절의 최대 매개 변수 수?

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

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

입력할 수 있는 최대 매개 변수 수를 아는 사람이 있습니까?

여기 위치한 소스 코드에 따르면, 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.
 */

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

적어도 32767개의 값에 대한 기술적 한계가 있다는 것은 알 수 있습니다(=단축).MAX_VALUE)을(를) Postgre에 전달할 수 있습니다.Posgresql의 JDBC 드라이버 9.1을 사용하는 SQL 백엔드.

이것은 "delete from x where id in (...)"에 대한 테스트입니다.100k 값...postgresql 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)

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)

우리는 포스트그레스가 온도 테이블을 만들고 그것과 합류하는 것을 볼 수 있습니다.

Oracle DB에 대한 경험이 많은 사람으로서 이러한 한계에 대해서도 우려했습니다.10,000개의 매개 변수가 있는 쿼리에 대한 성능 테스트를 수행했습니다.IN-list는 모든 소수를 쿼리 매개 변수로 나열하여 처음 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 메일링 목록에 있는 이 (꽤 오래된) 스레드는 그러한 쿼리를 계획하는 데 여전히 무시할 수 없는 비용이 있다는 것을 나타내므로, 내 말을 곧이곧대로 믿으십시오.

IN 절에 전달하는 요소의 수에는 제한이 없습니다.요소가 더 있으면 배열로 간주한 다음 데이터베이스의 각 검색에 대해 배열에 포함되어 있는지 여부를 확인합니다.이 접근 방식은 확장성이 높지 않습니다.IN 절을 사용하는 대신 INNER JOIN을 temp 테이블과 함께 사용해 보십시오.자세한 내용은 http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ 을 참조하십시오.쿼리 최적화 도구와 함께 INER JOIN 척도를 사용하면 해시 조인 및 기타 최적화를 사용할 수 있습니다.IN 절을 사용하면 최적화 도구가 쿼리를 최적화할 수 없습니다.이 변경 사항으로 인해 최소 2배 이상 속도가 빨라지는 것을 알게 되었습니다.

방금 해봤습니다. 정답은 -> 2바이트 값으로 범위 외 정수입니다: 32768

임의로 긴 ID 목록을 추가하는 대신 해당 쿼리의 리팩터링을 고려하는 것이 좋습니다.ID가 예제의 패턴을 따르는 경우 범위를 사용할 수 있습니다.

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

다른 옵션은 내부 선택 항목을 추가하는 것입니다.

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

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

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)

언급URL : https://stackoverflow.com/questions/1009706/postgresql-max-number-of-parameters-in-in-clause