PostgreSQL – nombre maximum de parameters dans la clause «IN»?

Selon le code source situé ici, à partir de la ligne 850, PostgreSQL ne limite pas explicitement le nombre d’arguments.

Ce qui suit est un commentaire de code de la ligne 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. */ 

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

PLAN DE LA DEMANDE

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

Mais si essayer 2ème requête:

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

PLAN DE LA DEMANDE

 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) 

Nous pouvons voir que postgres construit la table temporaire et s’y joint

Ce n’est pas vraiment une réponse à la question présente, mais cela pourrait aussi aider les autres.

Au moins, je peux dire qu’il existe une limite technique de 32767 valeurs (= Short.MAX_VALUE) pouvant être transmise au backend PostgreSQL, en utilisant le pilote JDBC de Posgresql 9.1.

Ceci est un test de “delete from x where id in (… 100k values ​​…)” avec le pilote jdbc postgresql:

 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) 

Le nombre d’éléments que vous passez à la clause IN est illimité. S’il y a plus d’éléments, il le considérera comme un tableau et ensuite, pour chaque parsing de la firebase database, il vérifiera s’il est contenu dans le tableau ou non. Cette approche n’est pas si évolutive. Au lieu d’utiliser la clause IN, essayez d’utiliser INNER JOIN avec la table temporaire. Reportez-vous à http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ pour plus d’informations. L’utilisation d’INNER JOIN évolue bien lorsque l’optimiseur de requêtes peut utiliser la jointure par hachage et d’autres optimisations. Alors qu’avec la clause IN, l’optimiseur n’a aucun moyen d’optimiser la requête. J’ai remarqué une accélération d’au moins 2x avec ce changement.

Comme quelqu’un de plus expérimenté avec Oracle DB, j’étais également préoccupé par cette limite. J’ai effectué un test de performance pour une requête avec environ 10 000 parameters dans une liste IN , récupérant des nombres premiers allant jusqu’à 100 000 à partir d’une table contenant les 100 000 premiers entiers en répertoriant tous les nombres premiers comme parameters de requête .

Mes résultats indiquent qu’il n’est pas nécessaire de surcharger l’optimiseur de plan de requête ou d’obtenir des plans sans utilisation d’index , car cela transformera la requête pour utiliser = ANY({...}::integer[]) où elle peut tirer parti des index :

 -- 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); 

Vous voudrez peut-être envisager de refactoriser cette requête au lieu d’append une liste d’id arbitrairement longue … Vous pouvez utiliser une plage si les identifiants suivent bien le modèle de votre exemple:

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

Une autre option consiste à append une sélection interne:

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

Si vous avez une requête comme:

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

vous pouvez augmenter la performance si réécrivez votre requête comme:

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