DEV Community

Dimitrios Desyllas
Dimitrios Desyllas

Posted on

How I can speed up a lateral join (or avoid it at all)

In my case:
https://dba.stackexchange.com/q/325355/118215

I have a table:

create table "appointments"(
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    appointment_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    store_id INTEGER NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY(client_id) REFERENCES "client"(id),
    CONSTRAINT fk_store FOREIGN KEY(store_id) REFERENCES "store"(id)
);
Enter fullscreen mode Exit fullscreen mode

I want in it to find the most recent appointment in appointments for each user. An approach of mine is to use a lateral join:

select 
 * 
from 
 "client"
 left join lateral (
   select 
       * 
   from 
      appointments join store on  appointments.store_id = store.id
   where appointments.client_id = client.id 
  order by appointment_timestamp DESC
  limit 1
 ) as latest_appointment on true
Enter fullscreen mode Exit fullscreen mode

But seems kinda slow to me. Is there a way to speed it up?

Top comments (0)