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

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

i got query running already, problem when 4 tables big, gets quite slow.

how can optimize this?

select all_records.user_id, users.name, users.image ( select user_id comments commentable_id = #{object.id} , commentable_type = '#{object.class.to_s}' union select user_id hello helloable_id = #{object.id} , helloable_type = '#{object.class.to_s}' union select user_id foo fooable_id = #{object.id} , fooable_type = '#{object.class.to_s}' union select user_id bar barable_id = #{object.id} , barable_type = '#{object.class.to_s}' ) all_records inner bring together users on users.id = all_records.user_id grouping all_records.user_id, users.name, users.image limit 15

what query should unique users did on (4) tables (pardon alter of names of tables). limit 15 still works slow because think still reads 4 tables. doing right or there way optimize this?

for reference: using postgres , using rails executing in find_by_sql.

edit

local postgres: 9.0.5; heroku postgres: 9.1

taking question is: "get 15 arbitrary rows". should very fast.

select u.id, u.name, u.image ( select id ( select user_id id comments commentable_id = #{object.id} , commentable_type = '#{object.class.to_s}' union select user_id hello helloable_id = #{object.id} , helloable_type = '#{object.class.to_s}' union select user_id foo fooable_id = #{object.id} , fooable_type = '#{object.class.to_s}' union select user_id bar barable_id = #{object.id} , barable_type = '#{object.class.to_s}' ) grouping id limit 15 ) b bring together users u using (id)

if running postgresql 9.1 or later, simplify group id, assuming users.id primary key. take more radical approach.

i pull group by , limit 1 query level in hope enable faster index scans on base of operations tables. limit 15 , no order by sequential scans should not occur. postgres can read tuples top of index , stop limit reached. similar this closely related case: way seek multiple selects till result available? here postgres reads tuples index.

you might accomplish same effect using left bring together users instead of join (instead of subquery level), since join can potentially drop rows result , disables simpler query plan.

for perfect performance, have indexes

create index comments_mult_idx on comments (commentable_id, commentable_type, user_id)

on 4 tables. user_id has lastly column. here's why.

sql ruby-on-rails postgresql query-optimization