Проблема производительности с действительно большим оператором IN (ids)

Question

У меня есть это заявление:

select qulified_name
from table
inner join references_table on references_table.id = table.ref_id
where references_table.type = 'x' and table.value in (... +110 000 ids)

что очень медленно. (Веб-приложение аварийно завершает работу и не получает результата. Я создаю свое утверждение с помощью rom-rb, и оно использует продолжение. Но это утверждение я получаю, когда смотрю на утверждение.

Если я переписываю такой вариант, производительность действительно хорошая по сравнению с первой версией:

select qulified_name
from table
inner join (select unnest(array['#{references_id.join("','")}']) id ) as tmp on tmp.id = businesspartner_references.value
inner join references_table on references_table.id = table.ref_id
where references_table.type = 'x'

Таким образом, я получаю результат через ~3 сек.

Может кто-нибудь объяснить мне, почему это так? Я не понимаю этого..

Answer 1

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

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

Базы данных очень хороши в оценке операций, ориентированных на множество, и могут найти лучший алгоритм соединения для ваших данных.