Вопрос по postgresql – PostgreSQL - максимальное количество параметров в предложении «IN»?

118

В Postgres вы можете указать предложение IN, например:

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

Кто-нибудь знает, какое максимальное количество параметров вы можете передать в IN?

Ваш Ответ

7   ответов
35

однако это может помочь и другим.

По крайней мере, я могу сказать, что существует технический предел в 32767 значений (= Short.MAX_VALUE), передаваемых бэкэнду PostgreSQL с использованием драйвера JDBC 9g Posgresql.

Это тест & quot; удалить из x, где id in (... 100k values ...) & quot; с драйвером 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)
ОП спрашивал об ограничении механизма БД, но в поисках ограничения JDBC я пришел сюда и именно этого я и ждал. Таким образом, это ограничение, однако, довольно высокое.
2

бавления произвольно длинного списка идентификаторов ... Вы можете использовать диапазон, если идентификаторы действительно следуют шаблону в вашем примере:

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

Другой вариант - добавить внутренний выбор:

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

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[]))

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)

Мы видим, что postgres создает временную таблицу и присоединяется к ней

Но то, что я слышал, что postgres-9.3 +, похоже, одинаково эффективен.datadoghq.com/blog/…
1

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)
В любом случае, @KiranJonnalagadda, это увеличивает производительность (возможно, незначительно), если не требуется никакой внутренней работы.
PostgreSQL & APOS; sEXPLAIN говорит, что это внутренне переписывает мойIN (...) какANY ('{...}'::integer[]).
16

передаваемых в предложение IN, не ограничено. Если имеется больше элементов, он будет рассматривать его как массив, а затем для каждого сканирования в базе данных будет проверять, содержится ли он в массиве или нет. Этот подход не так масштабируем. Вместо использования предложения IN попробуйте использовать INNER JOIN с временной таблицей. обращатьсяhttp://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ для получения дополнительной информации. Использование весов INNER JOIN также позволяет оптимизатору запросов использовать хеш-соединение и другие способы оптимизации. В то время как с предложением IN оптимизатор не может оптимизировать запрос. Я заметил ускорение по крайней мере в 2 раза с этим изменением.

Ссылка, на которую вы ссылаетесь, не говорит, о какой СУБД она говорит. Хотя я могу подтвердить, что в Oracle DB использование временных таблиц значительно повышает производительность по сравнению с комбинированием запросов.OR а такжеIN пункты из-за больших накладных расходов при разборе и планировании таких запросов, я не смог подтвердить проблему с Postgres 9.5, см.this answer.
66

здесь, начиная с строки 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.
 */
8

я тоже был обеспокоен этим ограничением. Я выполнил тест производительности для запроса с ~ 10 000 параметров вINсписок, выбирающий простые числа до 100 000 из таблицы с первыми 100 000 целыми числамиby actually listing all the prime numbers as query parameters.

Мои результаты показывают, чтоyou need not worry about overloading the query plan optimizer or getting plans without index usage, поскольку он преобразует запрос для использования= 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);

Похожие вопросы