LATERAL LEFT JOINs in Nested Relations Cause Performance Drop Compared to Simple LEFT JOINs

[lxup]

Environment

• PostgreSQL version: supabase/postgres:15.6.1.146 (using Supabase’s hosted environment)
• PostgREST version: Whatever version Supabase uses as of March 2025 (I’m using Supabase, so I don’t have direct control over the exact PostgREST version; likely v10.x or v11.x based on Supabase’s updates)
• Operating system: Ubuntu (running via VPS Hetzner)

Description of issue

Expected Behavior

I expect PostgREST to handle complex joins on a view efficiently, ideally using standard LEFT JOINs or optimizing the query to avoid unnecessary overhead. When I run the following raw SQL query directly on my database, it executes in approximately 500 ms:

EXPLAIN ANALYZE
SELECT
  uf.*,
  row_to_json(m.*)::jsonb AS "media",
  row_to_json(u.*)::jsonb AS "user",
  row_to_json(ur.*)::jsonb AS "review"
FROM public.user_feed uf
LEFT JOIN media m ON m.media_id = uf.media_id
LEFT JOIN public.user u ON u.id = uf.user_id
LEFT JOIN public.user_review ur ON ur.id = uf.id
ORDER BY uf.updated_at DESC
LIMIT 20;

Actual Behavior

When I use Supabase (which relies on PostgREST) to query the same user_feed view with nested relations via the Supabase JS client, the performance degrades significantly to 4 seconds (4000 ms). Here’s the equivalent request translated into a PostgREST URL:

curl -X GET "https://api.mydomain.com/rest/v1/user_feed?select=*,media(title,avatar_url,extra_data,url,date),user(*),review:user_review(*)&offset=0&limit=20&order=updated_at.desc.nullslast" \
-H "apikey: MY_API_KEY" \
-H "Authorization: Bearer MY_JWT_TOKEN"

The EXPLAIN ANALYZEnoutput from this request (via Supabase’s .explain({ analyze: true })) shows that PostgREST uses LATERAL LEFT JOINs instead of simple LEFT JOINs for the nested relations (media, user, review:user_review). This causes a significant performance hit, as the LATERAL subqueries are executed for all rows of user_feed (e.g., 654 rows in my case) before the ORDER BY and LIMIT are applied, unlike the raw SQL which uses standard LEFT JOINs and performs much faster.

[wolfgangwalther]

PostgREST version: Whatever version Supabase uses as of March 2025 (I’m using Supabase, so I don’t have direct control over the exact PostgREST version; likely v10.x or v11.x based on Supabase’s updates)

I understand that you don't have control over that. But I have even less control and/or insight. I have never used Supabase, don't have any connection to it. So.. it would be good if you were to report the actually used PostgREST version.

The EXPLAIN ANALYZEnoutput from this request (via Supabase’s .explain({ analyze: true })) shows that PostgREST uses LATERAL LEFT JOINs instead of simple LEFT JOINs for the nested relations (media, user, review:user_review). This causes a significant performance hit, as the LATERAL subqueries are executed for all rows of user_feed (e.g., 654 rows in my case) before the ORDER BY and LIMIT are applied, unlike the raw SQL which uses standard LEFT JOINs and performs much faster.

Just because those are LATERAL doesn't mean they need to be slow. I have many requests like these that are still fast. Without seeing the relevant parts of the schema, knowing which indexes exist etc... - it's hard to see where the problem is.

But I note one difference between your two queries:

ORDER BY uf.updated_at DESC

and

&order=updated_at.desc.nullslast

It could very well be that you're not hitting the index because of the nullslast. I suggest you try the SQL query with NULLS LAST and the http request without it as well, to see whether that makes a difference.

[lxup]

ORDER BY uf.updated_at DESC
and

&order=updated_at.desc.nullslast

Hey @wolfgangwalther, thanks for the feedback! Sorry I can’t test this directly in PostgREST since I’m using Supabase’s hosted setup, which limits my control a bit (I think it’s v10.x or v11.x based on their March 2025 updates). I’ll try to pin down the exact version and update you.

I took your suggestion and removed the nullslast from order=updated_at.desc.nullslast. Damn, it makes a huge difference! The same test query with nullslast took about 1.5 seconds (1500 ms), but without it, it dropped to 125 ms. Seems like nullslast might be throwing off the index or something. So I guess Im gonna set a default value for the updated_at column. Thx !

[wolfgangwalther]

CREATE INDEX has NULLS FIRST and NULLS LAST options, too. You probably just need to create an index matching your query exactly.