PostgreSQL – numero massimo di parametri nella clausola “IN”?

In Postgres, puoi specificare una clausola IN, come questa:

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

Qualcuno sa qual è il numero massimo di parametri che puoi passare in IN?

Secondo il codice sorgente che si trova qui, a partire dalla riga 850, PostgreSQL non limita esplicitamente il numero di argomenti.

Di seguito è riportato un commento al codice dalla riga 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)); 

QUERY PLAN

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

Ma se provi la seconda query:

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

QUERY PLAN

 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) 

Possiamo vedere che postgres crea una tabella temporanea e si unisce a essa

Questa non è davvero una risposta alla domanda attuale, tuttavia potrebbe aiutare anche gli altri.

Almeno posso dire che esiste un limite tecnico di 32767 valori (= Short.MAX_VALUE) passabile al backend PostgreSQL, utilizzando il driver JDBC di Posgresql 9.1.

Questo è un test di “delete from x where id in (… 100k values ​​…)” con il driver 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) 

Non c’è limite al numero di elementi che stai passando alla clausola IN. Se ci sono più elementi lo considererà come array e quindi per ogni scansione nel database controllerà se è contenuto nell’array o meno. Questo approccio non è così scalabile. Invece di usare la clausola IN provare a usare INNER JOIN con la tabella temporanea. Fare riferimento a http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ per ulteriori informazioni. L’utilizzo di INNER JOIN si adatta bene, poiché Query Optimizer può utilizzare hash join e altre ottimizzazioni. Mentre con la clausola IN non è ansible per l’ottimizzatore ottimizzare la query. Ho notato un aumento di almeno 2 volte con questo cambiamento.

Come qualcuno più esperto di Oracle DB, ero preoccupato anche per questo limite. Ho eseguito un test delle prestazioni per una query con parametri ~ 10’000 in un elenco IN , recuperando numeri primi fino a 100.000 da una tabella con i primi 100.000 numeri interi elencando effettivamente tutti i numeri primi come parametri di query .

I miei risultati indicano che non è necessario preoccuparsi di sovraccaricare l’ottimizzatore del piano di query o ottenere piani senza utilizzo dell’indice , poiché trasformsrà la query in use = ANY({...}::integer[]) dove può sfruttare gli indici come previsto :

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

Potresti prendere in considerazione la possibilità di ridefinire quella query invece di aggiungere un elenco arbitrariamente lungo di id … Potresti usare un intervallo se gli ID seguono effettivamente lo schema nel tuo esempio:

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

Un'altra opzione è aggiungere una selezione interna:

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

Se hai una query come:

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

puoi aumentare la performance se riscrivi la tua query come:

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