Currently having 2 tables

1. Users -> 1 million records
2. Requests -> 10 millions records

A User has many Requests. I'm fetching all the users alongside with their last created Request with by something like the following query:

SELECT *
FROM users AS u
INNER JOIN requests AS r
ON u.id = r.user_id
WHERE r.id IN (
    SELECT MAX(r.id)
    FROM users u
    INNER JOIN requests r ON r.user_id = u.id
    GROUP BY u.id
);

which does work, but with very very poor performance (> 7 sec). I understand the reason why and i'm trying to find a solution for that, even if i have to modify the schema.

Note: Requests table consists of Boolean columns, and I'm not quite sure if indexing will help here.

CodePudding user response：

distinct on () is one way to do this:

select distinct on (u.id) *
from users u
  join requests r on u.id = r.user_id
order by u.id, r.id desc;

Another option is a lateral join:

select *
from users u
  join lateral ( 
    select * 
    from requests r1 
    where u.id = r1.user_id
    order by id desc
    limit 1
  ) r on true