PostgreSQL - nombre maximal de paramètres dans la clause "IN" ?

Question

Dans Postgres, vous pouvez spécifier une clause IN, comme ceci :

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

Quelqu'un sait-il quel est le nombre maximum de paramètres que l'on peut passer dans IN ?

Answer 1

Ce n'est pas vraiment une réponse à la présente question, mais cela pourrait aider d'autres personnes.

Au moins je peux dire qu'il y a une limite technique de 32767 valeurs (=Short.MAX_VALUE) transmissibles au backend PostgreSQL, en utilisant le pilote JDBC 9.1 de Posgresql.

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)

Answer 2

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

Answer 3

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

PLAN DE REQUÊTE

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

Mais si vous essayez la 2ème requête :

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

PLAN DE REQUÊTE

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 une table temporaire et la joint avec elle.

Answer 4

En tant que personne plus expérimentée avec Oracle DB, j'étais également préoccupé par cette limite. J'ai effectué un test de performance pour une requête avec ~10'000 paramètres dans une base de données Oracle. IN -liste, récupération des nombres premiers jusqu'à 100'000 à partir d'un tableau contenant les 100'000 premiers entiers en listant réellement tous les nombres premiers comme paramètres de requête .

Mes résultats indiquent que vous ne devez pas craindre de surcharger l'optimiseur de plan de requête ou d'obtenir des plans sans utilisation d'index puisqu'il transformera la requête pour qu'elle utilise = ANY({...}::integer[]) où il peut exploiter les indices comme prévu :

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

Cependant, cette (plutôt vieille) fil de discussion sur la liste de diffusion pgsql-hackers indique qu'il y a toujours un coût non négligeable dans la planification de telles requêtes, donc prenez ma parole avec un grain de sel.

Answer 5

Il n'y a pas de limite au nombre d'éléments que vous passez à la clause IN. S'il y a plus d'éléments, il les considérera comme un tableau et ensuite, pour chaque analyse dans la base de données, il vérifiera s'ils sont contenus dans le tableau ou non. Cette approche n'est pas très évolutive. Au lieu d'utiliser la clause IN, essayez d'utiliser INNER JOIN avec la table temp. Voir http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ pour plus d'informations. L'utilisation d'échelles INNER JOIN ainsi que l'optimiseur de requêtes peuvent utiliser la jointure de 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.