Which approach will be faster?

Question

I have to select list of users and for each of them I have to return info that he is followed or not (by current/requesting user). For now I query like this:

SELECT 
   "User"."name", 
   EXISTS(
      SELECT * FROM "Followings" f WHERE f."FollowedId" = "User"."id" AND f."FollowerId" = 123
   )::boolean AS "isFollowed"
FROM "Users" AS "User" 
ORDER BY "User"."createdAt" 
LIMIT 15

Note: 123 is id of user that retrieves list of user.

Is id good idea to make such nested SELECT? What is performance of such query?

My second idea is just to make "Users" LEFT JOIN "Followings" - if following is joined then user is followed. Which of the approaches will be better?

In most of the cases you should go with JOINS source: http://stackoverflow.com/questions/2577174/join-vs-sub-query — Luca B., Aug 21 '14 at 16:36
That is a MySQL question (and answer). This is PostgreSQL. In my experience the performance of subqueries depends a lot on the ability of the engine to optimize them. Firebird is great on subqueries. But I don't know PostgreSQL enough to answer on this one. — Frazz, Aug 21 '14 at 16:41
No need to discuss speed here, the queries are different, so the results will be different. The `EXISTS` version wil always yield one tuple per user, the `LEFT JOIN` will produce *at least* one tuple per user (assuming an 1:N relation) — joop, Aug 21 '14 at 16:52
@LucaB. This is not one of those cases and your linked answer is not a good fit. — Erwin Brandstetter, Aug 21 '14 at 17:18

Erwin Brandstetter · Accepted Answer · 2014-08-22T15:30:43.817

Yes, your query is about the optimum. I just simplified and removed the redundant cast:

SELECT name
     , EXISTS (
          SELECT 1 FROM "Followings" f
          WHERE  f."FollowedId" = u."id"
          AND    f."FollowerId" = 123
          ) AS "isFollowed"
FROM  "Users" u
ORDER  BY "createdAt" 
LIMIT  15;

The cast to ::boolean was redundant, since EXISTS already returns a boolean.

The alternative form with LEFT JOIN would perform about the same or slower, but be more complicated and also depend on ("FollowedId", "FollowerId") to be unique, or you can get multiple rows, which might mess with your LIMIT, so you would need to add GROUP BY. Assuming unique pairs:

SELECT u.name
     ,(f."FollowedId" IS NOT NULL) AS "isFollowed"
FROM  "Users" u
LEFT  JOIN "Followings" f ON f."FollowedId" = u."id"
                         AND f."FollowerId" = 123
ORDER  BY u."createdAt" 
LIMIT  15;

Which approach will be faster?

1 Answers1