CODEWITHSUNDEEP
sqlpostgresql
Darcbar
Darcbar

Reputation: 888

postgresql/sql - Improve query where same sub select used for IN & NOT IN select

How could I improve this query. The problem is that the same sub select is used twice, first for an IN and then for a NOT IN: 

SELECT 
  "activities".* 
FROM "activities" 
WHERE (
  user_id IN (
    SELECT followed_id 
    FROM relationships 
    WHERE follower_id = 1 AND blocked = false) 
  AND 
  targeted_user_id NOT IN (
    SELECT followed_id 
    FROM relationships 
    WHERE follower_id = 1 AND blocked = false )
)

Upvotes: 0

Views: 56

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

In addition to indexes you could try rewriting the query as follows:

SELECT distinct a.*
  FROM activities a
  join relationships x
    on a.user_id = x.followed_id
  left join relationships r
    on a.targeted_user_id = r. followed_id
   and r.follower_id = 1
   and r.blocked = false
 where r.followed_id is null
   and x.follower_id = 1
   and x.blocked = false

If the inner join to relationships (x) above does not result in repeated rows of activities, you can get rid of the DISTINCT.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I would phrase the query using exists:

SELECT a.* 
FROM activities a
WHERE EXISTS (SELECT 1
              FROM relationships r
              WHERE r.followed_id = a.user_id AND
                    r.follower_id = 1 and
                    r.blocked = false
             ) AND
      NOT EXISTS (SELECT 1
                  FROM relationships r
                  WHERE r.followed_id = a.targeted_user_id AND
                        r.follower_id = 1 and
                        r.blocked = false
                 );

Then, I would create an index on relationships(followed_id, follower_id, blocked):

create index idx_relationships_3 on relationships(followed_id, follower_id, blocked);

From a performance perspective, the index should be much better than using a CTE (if you are really using Postgres, MySQL doesn't support CTEs).

Upvotes: 0

user1596371
user1596371

Reputation:

Using a common table expression will help:

WITH users_cte AS (
  SELECT followed_id
  FROM relationships
  WHERE follower_id = 1 AND blocked = false)
SELECT "activities.*"
FROM "activities"
WHERE user_id IN (SELECT followed_id FROM users_cte)
  AND targeted_user_id NOT IN (SELECT followed_id FROM users_cte)

Upvotes: 1

Related Questions