Как в PostgreSQL выбрать значения значения которые есть в массиве, но нет в БД?

Question

[beduin01]

В БД есть таблица с
ID: 1 2 3 4 5

У меня есть массив 1 2 3 4 5 6 7. Как мне сделать запрос, который бы вернул мне цифры 6 и 7 (именно их нет в БД)?

Сама БД с ID огромная. Программно вытаскивать не вариант. Там миллионы ID-шников.

Answer 1

[Melkij]

Всё просто: надо развернуть массив в набор строк и проверить на not exists

select i from unnest(array[1,2,3,4]) as i 
where not exists (select 1 from tablename where id = i);

Comments

[Алексей Черемисин]

Я бы не назвал этот запрос оптимальным, если у нас мульен этих ID. Такие вещи лучше делать программно.

[Melkij]

Алексей Черемисин, где именно их миллион? В табличке? Фигня вопрос. И миллиард - аналогично. А у вас на приложении уже памяти не один гигабайт исчезло.

А если у вас лям чисел в начальном массиве - то вопрос идёт с разбирательств, а откуда у вас такой массив взялся.

[Алексей Черемисин]

Melkij, у вас получился просто цикл с подзапросом, другими словами, вместо того, чтобы просто последовательно одним запросом выбрать все ID и сравнить их с заданным массивом, вы на каждый элемент делаете отдельный, пусть и короткий, запрос. Ну и данные будут гоняться туда и обратно каждый раз.

А большой массив получить легко, например, у меня есть набор файлов на диске (пусть это будут изображения товаров) и мне его нужно сопоставить с записями в базе, после этого почистить файлы. Итого, в базе 900M товаров, к каждому товару по 4-5 картинки - вот вам и миллиард.

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

[Melkij]

Алексей Черемисин, у меня получился запрос. Будет база делать цикл с подзапросом или как-то иначе его выполнять - есть варианты. Например, мой тестовый 9.6 никаких подзапросов не делает. Весь запрос выполняется как один простой left join.

Ну и данные будут гоняться туда и обратно каждый раз.

Смотря что именно считать данными. Потому что какая-то часть в любом случае будет передавать по сети. Или вы передаёте свой массив или читаете всю таблицу. Да хоть даже данными можно результат запроса назвать.

Да и программно можно массивы подгружать кучками

Надеюсь только, что речь не о limit offset.

[Алексей Черемисин]

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

[Melkij]

Алексей Черемисин, ну позиция так позиция. Не буду тогда больше ничего писать. Попечалюсь только вновь, что таких панически боящихся СУБД людей почему-то много - как работал 7 лет разработчиком, так и после смены деятельности на DBA не стал лучше понимать этот странный феномен боязни субд. Зато явно без работы не останусь, хоть и с постоянным фейспалмом от результирующих чудес такой "позиции".

[Алексей Черемисин]

Melkij, как бы сказать, 7 лет, это треть от моего стажа, большинство из которого прошло под знаком работы с базами, вплоть до помощи в портациях этих баз на разные платформы и архитектуры. Это не боязнь, а горький опыт ошибок и разрчарований от неправильных подходов к их использованию. И здесь разговаривать и дискутировать можно бесконечно. Но поверьте, не просите от базы лишнего, и она будет ласкова и послушна, как котенок.
Конкретно по данному запросу, представьте, что вы купили оракл, или сайбейс/mssql, или еще какую подобную хреновину, за пару сотен убитых енотов. И вот в какой-то момент вы видите, что подобные запросы составляют 80-90% всей нагрузки. Приходите к инвестору и просите в качестве развития еще один сервер с базой... А вот еще один вычислительный сервер вам обойдется в 10к!!!!
Ну и в качестве и заказчика и менеджера, я бы такой код не пропустил. Аргументация таже самая, база дорогой ресурс, даже если сама база бесплатная и нефиг его использовать как калькулятор. Мы не можем за 5 минут поставить новый сервер и кластеризовать базу. Я уж молчу про хранимки и триггеры, и как они блокируют все и вся прибездумном использовании. Использование любой хранимки и триггера мне нужно так обосновать, чтобы и я и остальная команда была полностью уверена во всех аспектах его надежности. Я уж не говорю про дополнительные ресурсы на их отладку, поддержку и сопровождение.
Да и похоже, за эти 7 лет вы не работали в сколь нибудь большой команде разработчиков...

[Melkij]

Алексей Черемисин, использовать базу в качестве калькулятора предлагаете здесь как раз вы - вычитывать всю таблицу вместо простого index scan.
Вы могли бы такими "аргументами" попытаться убедить рядового разработчика, но не пытайтесь таким образом в чём-то убедить специализирующегося на этой СУБД DBA, так вы лишь доказали, что не умеете и потому боитесь использовать свой postgresql.

[Алексей Черемисин]

Вот даже говорить ничего не буду, удачи!

explain (analyze) select i from unnest(array[1,2,3,4,5]) as i where exists (select 1 from v_vars where var_order = i);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=3.69..5.88 rows=75 width=4) (actual time=0.091..0.094 rows=5 loops=1)
   Hash Cond: ((i.i)::numeric = v_vars.var_order)
   ->  Function Scan on unnest i  (cost=0.00..1.00 rows=100 width=4) (actual time=0.009..0.009 rows=5 loops=1)
   ->  Hash  (cost=2.75..2.75 rows=75 width=5) (actual time=0.052..0.052 rows=75 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 11kB
         ->  Seq Scan on v_vars  (cost=0.00..2.75 rows=75 width=5) (actual time=0.011..0.026 rows=75 loops=1)
 Planning time: 0.566 ms
 Execution time: 0.135 ms
(8 строк)

И у меня.

explain (analyze) select var_order from v_vars where var_order in (1,2,3,4,5);
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on v_vars  (cost=0.00..3.22 rows=5 width=5) (actual time=0.020..0.067 rows=5 loops=1)
   Filter: (var_order = ANY ('{1,2,3,4,5}'::numeric[]))
   Rows Removed by Filter: 70
 Planning time: 0.194 ms
 Execution time: 0.086 ms
(5 строк)

Answer 2

[Алексей Черемисин]

Проще всего, и дешевле по ресурсам, это сделать не на стороне базы, а программно.
Например, на питоне это займет всего одну строку

>>> set([1,2,3,4,5,6,7]) - set([1,2,3,4,5])
set([6, 7])
>>>

PS. вытащить все ID можно одним запросом select ID from mytable

PPS. Сравнение на миллионе ID - заняло 166 милисекунды

MacBook-Pro:~ leah$ python -m timeit 'set(range(1,1000022)) - set(range(1,1000000))'
10 loops, best of 3: 166 msec per loop
MacBook-Pro:~ leah$

Comments

[beduin01]

А выборка из БД сколько займет...

[Melkij]

а поясните, кстати, что именно ваш пример делает столько времени?

time python -m timeit 'set(range(1,1000022)) - set(range(1,1000000))'
10 loops, best of 3: 98.4 msec per loop

real 0m4.002s
user 0m3.528s
sys 0m0.468s

Как заявленные в результате 10 loops по 0,1сек = 1с превращаются в 4 секунды реального времени?

[Алексей Черемисин]

Модуль timeit запускает строку несколько раз, и выбирает лучший результат, по умолчанию запускает 10 раз.

Вот без этого модуля на голом интерпретаторе

MacBook-Pro:~ leah$ time python -c 'set(range(1,1000022)) - set(range(1,1000000))'

real	0m0.306s
user	0m0.186s
sys	0m0.092s
MacBook-Pro:~ leah$

PS. На питоне я не настаиваю, тоже самое можно на любом языке программирования, где-то 1-2 строки, где-то 20.

[Алексей Черемисин]

beduin01, Если у нас есть ограниченный набор ID
то одним запросом их вытащить примерно так

SELECT  ID FROM    table WHERE   ID IN (1,2,3,4,5,6,7)