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

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

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

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

114 голосов | спросил 18 J0000006Europe/Moscow 2009, 01:48:13

7 ответов


0

Согласно исходному коду, расположенному здесь, начиная со строки 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.
 */
ответил Jordan S. Jones 18 J0000006Europe/Moscow 2009, 04:19:58
0
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 создает временную таблицу и присоединяется к ней

ответил hacker13ua 11 MarpmWed, 11 Mar 2015 22:21:31 +03002015-03-11T22:21:31+03:0010 2015, 22:21:31
0

На самом деле это не ответ на настоящий вопрос, однако он может помочь и другим.

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

Это тест "delete from x, где id in (... 100k values ​​...)" с драйвером 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)
ответил nimai 15 FebruaryEurope/MoscowbWed, 15 Feb 2017 16:54:14 +0300000000pmWed, 15 Feb 2017 16:54:14 +030017 2017, 16:54:14
0

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

ответил Prasanth Jayachandran 26 +04002012-10-26T10:55:55+04:00312012bEurope/MoscowFri, 26 Oct 2012 10:55:55 +0400 2012, 10:55:55
0

Как человек с большим опытом работы с БД Oracle, я тоже был обеспокоен этим ограничением. Я выполнил тест производительности для запроса с ~ 10 000 параметров в списке IN, извлекая простые числа до 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);
ответил blubb 9 SatEurope/Moscow2017-12-09T17:50:30+03:00Europe/Moscow12bEurope/MoscowSat, 09 Dec 2017 17:50:30 +0300 2017, 17:50:30
0

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

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

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

SELECT * 
FROM user 
WHERE id IN (
    SELECT userId
    FROM ForumThreads ft
    WHERE ft.id = X
);
ответил PatrikAkerstrand 18 J0000006Europe/Moscow 2009, 01:57:57
0

Если у вас есть запрос типа:

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)
ответил hacker13ua 27 +03002014-10-27T16:29:02+03:00312014bEurope/MoscowMon, 27 Oct 2014 16:29:02 +0300 2014, 16:29:02

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132