Consider the following schema on this SQL Fiddle. As you can see it follows the supertable schema for modeling a polymorphic association. Take in mind that foo, bar, and baz tables are very different each other on the real implementation.
The problem I'm facing is that I need to retrieve all the activities for a given owner with its corresponding target, my first idea was to use multiple LEFT OUTER JOINS:
SELECT *
FROM activities
LEFT OUTER JOIN foo ON activities.target_id = foo.activitable_id
LEFT OUTER JOIN bar ON activities.target_id = bar.activitable_id
LEFT OUTER JOIN baz ON activities.target_id = baz.activitable_id
WHERE activities.owner_id = 1
This query is fine considering I'm only joining 3 tables with one row on each table, but when the schema grows up to 8 joins with 10k rows on each table there's a huge performance impact.
So my question is, there's any way to achieve what I want with a faster query?
There doesn't need to be a huge performance impact. You have defined your data structure with only one index per table, the one on the primary key. That means that the joins are less efficient than they could be.
Also, given your structure, I don't think you want duplicate activitable_ids in any of the tables. So, each table should be defined something like this:
CREATE TABLE foo (
id serial primary key,
name varchar(20),
activitable_id int UNIQUE,
FOREIGN KEY (activitable_id) REFERENCES activitables(id)
);
Declaring the activitable_id to be unique addresses both these problems, because it creates a unique index on the column that both enforces uniqueness and should improve performance.