Speeding up multiple left outer join SQL query

Question

I would like some help speeding up my SQL query with multiple joins. What I am trying to retrieve is equivalent to all user fields with their latest corresponding page, item and club. The below query works okay, but it takes a few seconds. I have added indexes to all user_id foreign key fields as well as the User class and college fields that it is filtering by but it has not helped much.

I don't think I can do simple inner joins because I would like to get all users and inner joining across all the tables would eliminate some users with no pages for example. Each user has between zero and many pages, items and clubs.

SQL Tables

User
id
first_name
last_name
class
college

Page
id
user_id
created_at
page_text

Item
id
user_id
created_at
item_text

Club
id
user_id
created_at
club_text

Current working but slow query

SELECT u.id,u.first_name,u.last_name,p.page_text,i.item_text,c.club_text FROM user u LEFT OUTER JOIN LATERAL \
    (SELECT page_text FROM page p2 WHERE u.id = p2.user_id ORDER BY p2.created_at DESC LIMIT 1) p ON TRUE LEFT OUTER JOIN LATERAL \
    (SELECT item_text FROM item i2 WHERE u.id = i2.user_id ORDER BY i2.created_at DESC LIMIT 1) i ON TRUE LEFT OUTER JOIN LATERAL \
    (SELECT club_text FROM club c2 WHERE u.id = c2.user_id ORDER BY c2.created_at DESC LIMIT 1) c ON TRUE \
    WHERE u.class = 'Senior' AND u.college = 'University' ORDER BY u.first_name;

Answer 1

I think you can improve the query with appropriate indexes:

SELECT u.id, u.first_name ,u.last_name, p.page_text, i.item_text, c.club_text
FROM user u LEFT OUTER JOIN LATERAL
     (SELECT page_text
      FROM page p2
      WHERE u.id = p2.user_id
      ORDER BY p2.created_at DESC
      LIMIT 1
     ) p ONTRUE LEFT OUTER JOIN LATERAL
     (SELECT item_text
      FROM item i2
      WHERE u.id = i2.user_id
      ORDER BY i2.created_at DESC
      LIMIT 1
     ) i ON TRUE LEFT OUTER JOIN LATERAL
     (SELECT club_text
      FROM club c2
      WHERE u.id = c2.user_id
      ORDER BY c2.created_at DESC
      LIMIT 1
     ) c ON TRUE
WHERE u.class = 'Senior' AND u.college = 'University'
ORDER BY u.first_name;

The composite (multi-column) indexes you want are:

• user(class, college, first_name, id)
• page(user_id, created_at desc, page_text)
• item(user_id, created_at desc, item_text)
• club(user_id, created_at desc, club_text)