Optimize query to get unique (user) records from different tables

Question

I got this query running perfectly already, but the problem is when my 4 tables get too big, it gets quite slow.

How can I optimize this?

SELECT 
    all_records.user_id,
    users.NAME,
    users.IMAGE
FROM (
    SELECT user_id FROM comments
    WHERE commentable_id   = #{object.id}
      AND commentable_type = '#{object.class.to_s}'
    UNION ALL
    SELECT user_id FROM hello
    WHERE helloable_id     = #{object.id}
      AND helloable_type   = '#{object.class.to_s}'
    UNION ALL
    SELECT user_id FROM foo
    WHERE fooable_id       = #{object.id}
      AND fooable_type     = '#{object.class.to_s}'
    UNION ALL
    SELECT user_id FROM bar
    WHERE barable_id       = #{object.id}
      AND barable_type     = '#{object.class.to_s}'
) AS all_records
INNER JOIN users ON users.id = all_records.user_id
GROUP BY
    all_records.user_id,
    users.NAME,
    users.IMAGE
LIMIT 15

What the query should do is get the unique users that did something on the (4) tables (pardon the change of names of the tables). Even with the LIMIT 15 it still works slow because I think it still reads all the 4 tables. Am I doing this right or is there some way to optimize this?

For reference: I am using postgres and using rails but executing it in find_by_sql.

EDIT

local postgres: 9.0.5; heroku postgres: 9.1

Answer 1

Taking your question as it is: "get 15 arbitrary rows". That should be very fast.

SELECT u.id, u.name, u.image
FROM  (
   SELECT id
   FROM  (
      SELECT user_id AS id
      FROM   comments
      WHERE  commentable_id   = #{object.id}
      AND    commentable_type = '#{object.class.to_s}'

      UNION ALL
      SELECT user_id
      FROM   hello
      WHERE  helloable_id   = #{object.id}
      AND    helloable_type = '#{object.class.to_s}'

      UNION ALL
      SELECT user_id
      FROM   foo
      WHERE  fooable_id     = #{object.id}
      AND    fooable_type   = '#{object.class.to_s}'

      UNION ALL
      SELECT user_id
      FROM   bar
      WHERE  barable_id     = #{object.id}
      AND    barable_type   = '#{object.class.to_s}'
      ) AS a
   GROUP  BY id
   LIMIT  15
   ) b
JOIN   users u USING (id)

• If you are running PostgreSQL 9.1 or later, you could simplify to GROUP BY id, assuming users.id is the primary key. But I take a more radical approach.

• I pull up the GROUP BY and LIMIT one query level in the hope to enable faster index scans on the base tables. With a LIMIT 15 and no ORDER BY sequential scans should not occur. Postgres can just read tuples from the top of the index and stop as soon as the limit is reached.
  Similar to this this closely related case: Way to try multiple SELECTs till a result is available?
  Only here Postgres reads tuples from the index.

• You might achieve the same effect by using LEFT JOIN users instead of JOIN (instead of my extra subquery level), since the JOIN can potentially drop rows from the result and disables a simpler query plan.

• For perfect performance, you have indexes like

  CREATE INDEX comments_mult_idx
  ON comments (commentable_id, commentable_type, user_id)
  

  on all 4 tables. user_id has to be the last column. Here's why.