NOT IN query performance issue with large data

user_15294629 0
Viewed 83

i was trying to get the id and the number from table with condition of number isn't in the id.

select id,number from tmp_t where number not in (select id from tmp_t)

Have tried the query and it's taking soooo looonggg... like almost 40 minutes and i got disconnected from server. So what should i do? the data is around 500K rows..

So i wanted to show "here you go the id and the number, which the number didn't exist in the id." Because i tried to insert the number, but the number is a FK and depending on the ID, so i wanted to know the id and the number, that's why i'm using not in.

Maybe someone know? Btw im using Postgresql-13

query-optimization postgresql
2 Answers

Typically NOT EXISTS is faster (and doesn't suffer from surprises if NULL values are involved):

select t1.id, t1.number 
from tmp_t t1
where not exists (select *
                  from tmp_t t2
                  where t2.id = t1.number)
user_3303150

You can write it with NOT EXISTS instead, although these queries will have different results if any value of id is NULL (in which case, NOT IN probably yields not the answer you want, so NOT EXISTS is better from that perspective as well.)

select id,number from tmp_t where not exists 
    (select 1 from tmp_t a where a.id=tmp_t.number);

But your formulation is also efficient as long as work_mem is large enough.

user_17212390
Related