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)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay