CODEWITHSUNDEEP
sqlpostgresqljoinpolymorphic-associations
Mark Hardy
Mark Hardy

Reputation: 75

Joining on multiple possible tables

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?

Upvotes: 2

Views: 96

Answers (2)

Joseph B
Joseph B

Reputation: 5669

You could add indexes on the columns that are used in the query:

activities.target_id
foo.activitable_id
bar.activitable_id    
baz.activitable_id

If there is any filter condition that you can apply on any of the tables, then the performance would be better also, because you would be operating on a smaller subset of data. For example,

SELECT *
FROM activities
LEFT OUTER JOIN (SELECT * FROM foo WHERE name like 'A%') foo ON activities.target_id = foo.activitable_id --Filter applied to limit rows using the name
LEFT OUTER JOIN (SELECT * FROM bar WHERE id < 10) bar ON activities.target_id = bar.activitable_id --Filter applied to limit rows using id
LEFT OUTER JOIN (SELECT * FROM baz WHERE activitable_id = 1) baz ON activities.target_id = baz.activitable_id --Filter applied to limit rows using activitable_id
WHERE activities.owner_id = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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.

Upvotes: 1

Related Questions