Name: How I can speed up a lateral join (or avoid it at all)
Rating: 4.1 (3808 reviews)
Author: pcmagas

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

Dimitrios Desyllas - Mar 30 '23 -

- Dev Community

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)
);

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

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