PostgreSQL LATERAL Joins

Tip: Basic LATERAL Join

SELECT p.title, c.body
FROM posts p
LEFT JOIN LATERAL (
    SELECT body FROM comments WHERE post_id = p.id ORDER BY created_at DESC LIMIT 1
) c ON true;

Gets the latest comment per post.

Gotcha: LATERAL Runs Per Row

The subquery executes once per row from the left table. Can be slow for large tables.

Tip: Top-N Per Group

SELECT * FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders WHERE user_id = u.id ORDER BY total DESC LIMIT 3
) o ON true;

Gets top 3 orders per user.

Gotcha: ON true Required

LATERAL joins need an ON clause. Use ON true when the correlation is in the subquery.

Tip: LATERAL with Functions

SELECT * FROM generate_series(1, 10) AS n
LEFT JOIN LATERAL (SELECT n * n AS square) AS sq ON true;

Gotcha: Index Usage

LATERAL subqueries can use indexes on the correlated columns. Ensure proper indexing.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

LATERAL joins in PostgreSQL allow subqueries in the FROM clause to reference columns from preceding FROM items. This enables powerful patterns like 'top N per group' and 'first matching row per category'. I've replaced complex window function queries with cleaner LATERAL joins that are easier to understand and maintain. The performance of LATERAL joins is generally excellent when the inner query uses an index.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)