The Practical Developer

Postgres LATERAL JOINs: The Subquery Superpower You Are Not Using

June 19, 2026 by The Practical Developer

You are still writing correlated subqueries that iterate row by row, or joining aggregated subqueries that scan the same table twice. Postgres LATERAL JOINs solve both problems in a single pass: the top-N-per-group pattern, the nearest-neighbor search, and the set-returning-function explode, all with an index-friendly execution plan.

database

postgres

sql

performance

Close-up of circuit board traces and electronic components, representing the interconnected subquery patterns that LATERAL joins enable

The query was supposed to show the last three orders per customer on the account dashboard. Twelve lines of SQL, a window function with PARTITION BY, and a wrapper query to filter row_number <= 3. It worked. The staging data had 200 customers and 4,000 orders, and the page loaded in 30 milliseconds.

Then the marketing team turned on email campaigns. Six months later the customers table had 40,000 rows and the orders table had 600,000 rows. The same query now read the entire orders table (all 600,000 rows), assigned row numbers in a WindowAgg, and then discarded 597,000 of them in a Filter. The execution plan showed 1.8 million tuples read across two nodes. The page took four seconds.

The window function was not wrong. The window function was scanning every row of the table because the planner could not know in advance which rows mattered. To get the top N per group without a full scan, you need a query construct that evaluates a subquery once per group and stops early. Postgres has exactly that. It is the LATERAL join.

LATERAL is a join modifier that lets the right-hand side reference columns from the left-hand side. This sounds dry and academic. In practice it is the difference between a sequential scan over every row in a table and three index lookups per customer. It is the difference between fetching the nearest 10 geolocated points via a full sort and fetching them via a GiST index order-by. And it is the only ergonomic way to call a set-returning function like generate_series or jsonb_to_recordset once per row of the left table.

This post is the three patterns that make LATERAL indispensable: top-N-per-group, nearest-neighbor with ORDER BY + LIMIT, and set-returning function expansion. Each includes the execution plan analysis that shows you exactly why LATERAL wins.

What LATERAL actually does

A standard JOIN evaluates both sides independently and then combines the results. A LATERAL join evaluates the right side once for every row produced by the left side. The right subquery can reference columns from the left side as though they were parameters. This is exactly the semantics of a correlated subquery, but expressed as a join that the planner understands in terms of loops and index scans.

The syntax comes in two flavors:

-- As a subquery in the FROM clause
SELECT *
FROM customers c
JOIN LATERAL (
  SELECT amount, ordered_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 3
) o ON true;

-- Shorthand without the LATERAL keyword
-- (Postgres infers LATERAL from the subquery reference)
SELECT *
FROM customers c,
LATERAL (
  SELECT amount, ordered_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 3
) o;

The second form drops the JOIN keyword entirely (a comma-separated FROM clause). Many developers find this confusing, so prefer the explicit JOIN LATERAL in shared codebases.

The critical detail is the ON true. LATERAL subqueries always produce rows for every left-side row that matches. If the subquery returns no rows (e.g., a customer with zero orders), the join produces a row with NULLs for the right-side columns. Use LEFT JOIN LATERAL if you want to keep the left-side row even when the subquery is empty.

Pattern 1: Top-N per group (without scanning everything)

This is the most common use case and the one that saves the most database resources. Here is the table setup:

CREATE TABLE customers (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id uuid NOT NULL REFERENCES customers(id),
  amount numeric(10,2) NOT NULL,
  ordered_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_ordered
ON orders (customer_id, ordered_at DESC);

The index is critical. The LATERAL subquery does WHERE customer_id = $1 ORDER BY ordered_at DESC LIMIT 3. Without an index on (customer_id, ordered_at DESC), every subquery execution triggers a sequential scan of the entire orders table. With the index, each execution does exactly one index scan: seek to the customer’s first row and read three leaf entries. The index is B-tree, so the ordered-at sort is satisfied directly from the index order.

The query:

SELECT c.name, o.amount, o.ordered_at
FROM customers c
JOIN LATERAL (
  SELECT amount, ordered_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 3
) o ON true;

The execution plan difference

The window-function approach produces something like:

WindowAgg  (rows=600000)
  ->  Sort  (rows=600000)
       Sort Key: customer_id, ordered_at DESC
       ->  Seq Scan on orders  (rows=600000)

Then a Filter wrapper discards most rows. The planner cannot push the LIMIT into the window function, so it scans everything.

The LATERAL approach produces:

Nested Loop  (rows=120000)
  ->  Seq Scan on customers  (rows=40000)
  ->  Limit  (rows=3)
       ->  Index Only Scan Backward using idx_orders_customer_ordered
            Index Cond: (customer_id = customers.id)
            (rows=3 actual)

The limit acts as a stop-loss operator. Once Postgres has read three rows from the index satisfying customer_id = c.id, the scan stops. If a customer has 300 orders, the subquery reads 3 rows. If a customer has zero orders, the index probe returns nothing and the subquery produces one NULL row (or zero rows with a LEFT JOIN). The total row count is at most 3 * number_of_customers instead of number_of_orders.

Variations

Top N per group with a tie-breaking column:

SELECT c.name, o.amount, o.ordered_at, o.id
FROM customers c
JOIN LATERAL (
  SELECT amount, ordered_at, id
  FROM orders
  WHERE customer_id = c.id
  ORDER BY ordered_at DESC, id DESC
  LIMIT 3
) o ON true;

DISTINCT ON can only give you exactly one row per group. LATERAL gives you any N. If you need the latest 5 status updates per device or the top 3 scoring players per team, LATERAL is the tool.

Pattern 2: Nearest-neighbor with spatial data

Geospatial queries often need “find the 10 closest coffee shops to this location.” A naive approach computes the distance for every row and orders by distance. If the table has 50,000 coffee shops, that is 50,000 distance calculations and a full sort.

With a GiST or SP-GiST index on a geography column, Postgres can answer the nearest-neighbor query with an index scan that stops after 10 rows. LATERAL lets you do this for every location in a set, not just one.

CREATE TABLE delivery_zones (
  id uuid PRIMARY KEY,
  zone_name text NOT NULL,
  location geography(Point, 4326) NOT NULL
);

CREATE INDEX idx_delivery_zones_location
ON delivery_zones USING gist (location);

CREATE TABLE drivers (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  current_location geography(Point, 4326) NOT NULL
);

For each driver, find the three closest delivery zones:

SELECT d.name, z.zone_name, z.distance_meters
FROM drivers d
JOIN LATERAL (
  SELECT z.zone_name,
         z.location::geography <-> d.current_location::geography AS distance_meters
  FROM delivery_zones z
  ORDER BY z.location <-> d.current_location
  LIMIT 3
) z ON true
ORDER BY d.name, z.distance_meters;

The <-> operator is the distance operator, and GiST indexes support index-order nearest-neighbor scans with it. The execution plan shows an Index Scan inside the LATERAL subquery that reads exactly three index entries. No sequential scan. No full sort. The total work is 3 * number_of_drivers index probes.

This pattern works for any GiST-indexable data type: points, lines, polygons, and even full-text search vectors (though tsvector nearest-neighbor is less common).

Pattern 3: Expanding set-returning functions per row

Postgres has many set-returning functions: generate_series, unnest, jsonb_to_recordset, json_each, regexp_matches. When you need to call one of these for every row of a table, a FROM clause with a comma forces a cross join that repeats the function call once per row, but the syntax is awkward and the intent is unclear. LATERAL makes it explicit.

Generate time buckets per user:

SELECT u.id, bucket
FROM users u
CROSS JOIN LATERAL generate_series(
  u.created_at,
  u.created_at + interval '30 days',
  interval '1 day'
) AS bucket
WHERE u.trial_period = true;

This generates 31 rows per trial user, one for each day of the trial. Without LATERAL, you would have to move the generate_series call into a subquery or use a cross join that evaluates it once globally and then filters with a WHERE clause that cannot reference u.created_at directly.

Unnest a JSON array per row:

CREATE TABLE audit_logs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Each payload contains a "tags" array
-- Find all distinct tags used in the last 24 hours
SELECT DISTINCT tag
FROM audit_logs l
CROSS JOIN LATERAL jsonb_to_recordset(
  CASE WHEN jsonb_typeof(l.payload -> 'tags') = 'array'
       THEN l.payload -> 'tags'
       ELSE '[]'::jsonb
  END
) AS t(tag text)
WHERE l.created_at >= now() - interval '24 hours';

The CROSS JOIN LATERAL ensures the jsonb_to_recordset call receives the correct payload for each row. Without LATERAL, jsonb_to_recordset can only operate on a literal or a correlated subquery, which is harder to read and often forces an extra nesting level.

Split a CSV column per row (anti-pattern warning):

CROSS JOIN LATERAL regexp_split_to_table(tags, ',') AS tag;

Use this only for one-off migrations. Storing CSV in a text column is almost always wrong compared to a normalized table or a JSONB array, but when you inherit a legacy schema this pattern gets the job done without a WHILE loop.

Performance traps and how to dodge them

Trap 1: Missing index on the correlated columns

A LATERAL subquery with WHERE right_column = left_column without an index on right_column degrades to a sequential scan of the right table per row of the left table. For 1,000 customers scanning a 600,000-row orders table, that is 600 million rows scanned.

The fix: Always check the EXPLAIN ANALYZE output. If you see Seq Scan inside the LATERAL subquery, you are missing the index. The ideal index for top-N queries is a compound B-tree on (right_column, sort_column DESC).

Trap 2: Too many left-side rows

LATERAL evaluates the subquery once per left-side row. If the left side is a 5-million-row table and the subquery does a full-index scan even with the index, the query runs 5 million index probes. In some cases a batch approach with a window function may be faster.

The fix: Put a LIMIT on the left side if the use case allows it (e.g., “only the last 100 active customers”). Or partition the work into batches with OFFSET if this is a background job.

Trap 3: LEFT JOIN LATERAL vs plain LATERAL

A plain JOIN LATERAL drops the left-side row if the subquery returns zero rows. If a customer has no orders, that customer disappears from the result set. This is correct for the top-N query, but wrong for reports that need a row per customer even when the count is zero.

The fix: Use LEFT JOIN LATERAL ... ON true. The ON true is not optional with LEFT JOIN LATERAL. Without it, Postgres rejects the syntax because LATERAL requires a join condition.

SELECT c.name, COALESCE(o.order_count, 0) AS recent_orders
FROM customers c
LEFT JOIN LATERAL (
  SELECT count(*) AS order_count
  FROM orders
  WHERE customer_id = c.id
    AND ordered_at >= now() - interval '30 days'
) o ON true;

Trap 4: Nested LATERAL joins

You can nest LATERAL references across multiple levels (the third subquery references the first table), but each nesting level adds another loop. Three levels deep on large tables produces O(n^3) work. If you need three levels of nesting, reconsider the query design or extract the data into a temporary table first.

When to use a window function instead

LATERAL is not always the right answer. Use a window function when:

  • You need to compute an aggregate over all rows in the partition (e.g., SUM(amount) OVER (PARTITION BY customer_id)). LATERAL cannot do this efficiently without scanning every row for every group.
  • The number of rows per group is small and the number of groups is large, but you do not have an index on the join column. A window function that sorts once is cheaper than N sequential scans.
  • You are computing a running total or a moving average, which requires access to rows before and after the current row. LATERAL cannot reference sibling rows in the same partition.

The decision rule is: if you need exactly N rows per group and you have an index that satisfies the sort order, use LATERAL. If you need aggregate values computed over the entire partition, use window functions. If you need both (top 3 orders per customer plus a running total of all orders), combine them: use LATERAL for the top-N and a window function for the running total, applied to the outer query result.

The LATERAL cheat sheet

PatternSyntaxIndex needed
Top N per groupJOIN LATERAL (SELECT ... WHERE fk = pk ORDER BY sort DESC LIMIT N)(fk, sort DESC)
Nearest neighborJOIN LATERAL (SELECT ... ORDER BY col <-> ref LIMIT N)GiST index on col
Expand set-returning fnCROSS JOIN LATERAL fn(left.col)None (function call)
Aggregate within group (not top-N)LEFT JOIN LATERAL (SELECT count(*) FROM right WHERE fk = pk)(fk)

The production checklist

  1. Index the foreign key first. No index = no LATERAL. Always add a compound index that matches both the join condition and the sort order.
  2. Use EXPLAIN (ANALYZE, BUFFERS) before and after. Look for the actual time inside the LATERAL subquery. Each invocation should take microseconds, not milliseconds.
  3. Test with production-sized data. A LATERAL query that performs beautifully on 1,000 rows can collapse under 500,000 rows if the index selectivity is poor.
  4. Add a LIMIT in the subquery. Forgetting the limit is the most common mistake. Without it, the subquery returns all matching rows per group, which is the same scan cost as a window function but with N times the loop overhead.
  5. Consider LEFT JOIN LATERAL for outer-join semantics. If the left-side row must survive even when the subquery returns nothing, use LEFT JOIN LATERAL ... ON true.

The takeaway

LATERAL joins are not a niche SQL feature. They are the standard solution for top-N-per-group, nearest-neighbor, and set-returning-function expansion in Postgres. The key insight is that a LATERAL subquery is evaluated once per row of the left table, which means the database can stop reading the right table as soon as it has what it needs. An index seek that reads three rows and stops is always faster than a full sequential scan that reads 600,000 rows and then discards 597,000 of them.

Next time you write ROW_NUMBER() OVER (PARTITION BY ...) and then wrap it in an outer query to filter row_number <= N, pause and ask yourself whether a LATERAL join with an index and a LIMIT would do the same job in a fraction of the reads. The answer will surprise you more often than it should.

Add a LATERAL join to your standard SQL vocabulary alongside window functions and recursive CTEs. It is the third pillar of practical Postgres querying, and it is the one most teams discover last.

A note from Yojji

Getting the right query plan for a top-N-per-group problem often means knowing when a window function is the wrong tool and a LATERAL join with a carefully placed compound index is the right one. That kind of database-level intuition is exactly what turns a 4-second dashboard into a 40-millisecond one.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their 50+ senior engineers work extensively with Postgres, the JavaScript ecosystem, and cloud platforms (AWS, Azure, GCP), building data-intensive systems that stay fast under production load.

Keep Reading