CODEWITHSUNDEEP
sqlpostgresqlpostgresql-performance
Arya
Arya

Reputation: 8995

Query taking a very long time

I have the following query to return userids that are not in the message_log table

select * from likes where userid not in(select to_id from message_log)

I have an index on the userid column in the likes table and an index on the to_id column in the message_log table but the index are not being used according to EXPLAIN. Is something wrong with my query? My query has been running for 20 minutes and still no results.

Upvotes: 1

Views: 100

Answers (3)

Shubham Batra
Shubham Batra

Reputation: 2375

You can try this 

select * from likes lk where not exists (select 1 from message_log where to_id = lk.userid )

Upvotes: 2

StanislavL
StanislavL

Reputation: 57421

select * 
from likes 
     left join message_log ml on ml.to_id=likes.userid
where ml.to_id is null

Try the query with LEFT JOIN instead and leave the only userids without mesages

Upvotes: 2

Jakub Szumiato
Jakub Szumiato

Reputation: 1318

First thing you should consider is turning the sub query into join. Like this: select like.col1, like.col2 from likes like left join message_log mlog on like.userid = mlog.to_id where mlog.to_id is null Although optimizer will very likely do that for you.

Another thing you should try is to get rid of the asterisk from the select clause (as in my example) as it may impact indexes using by the optimizer.

Upvotes: 1

Related Questions