Postgres LATERAL joins: the query that replaced 4 Python loops
I was reviewing a slow API endpoint last month — 1.2 seconds for what should have been a simple data fetch. When I traced it, I found four nested Python loops making individual database queries: for each user, fetch their recent orders; for each order, fetch its items; for each item, fetch its current price. Classic N+1, four levels deep. One LATERAL join replaced all of it. Response time: 85ms.
What LATERAL actually does
A regular SQL join cannot reference columns from the current row being processed. LATERAL changes that. A LATERAL subquery runs once for each row in the preceding FROM clause, and can reference that row's columns as if they were variables.
Think of it as SQL's version of a for-loop: "for each row in table A, run this subquery using that row's values".
The Python before
Here is the code I was replacing. The task: for each active user, get their last 3 orders, with the top item (by revenue) from each order.
def get_user_order_summary(db: Session) -> list[dict]:
# Query 1: fetch active users
users = db.execute(
"SELECT id, name, email FROM users WHERE is_active = true LIMIT 100"
).fetchall()
result = []
for user in users:
# Query 2: fetch last 3 orders per user (N queries)
orders = db.execute(
"""
SELECT id, created_at, total_cents
FROM orders
WHERE user_id = :uid
ORDER BY created_at DESC
LIMIT 3
""",
{"uid": user.id},
).fetchall()
user_data = {"id": user.id, "name": user.name, "orders": []}
for order in orders:
# Query 3: fetch items for this order (N*3 queries)
items = db.execute(
"""
SELECT product_id, quantity, unit_price_cents
FROM order_items
WHERE order_id = :oid
""",
{"oid": order.id},
).fetchall()
# Python loop to find top item by revenue
top_item = max(
items,
key=lambda i: i.quantity * i.unit_price_cents,
default=None,
)
user_data["orders"].append({
"id": order.id,
"total_cents": order.total_cents,
"top_item_product_id": top_item.product_id if top_item else None,
})
result.append(user_data)
return result
For 100 users with 3 orders each: 1 + 100 + 300 = 401 queries. Each round-trip to Postgres is ~3ms. That is ~1.2 seconds of pure database overhead, ignoring actual query execution time.
The LATERAL version
SELECT
u.id AS user_id,
u.name,
u.email,
recent_orders.order_id,
recent_orders.created_at,
recent_orders.total_cents,
top_item.product_id AS top_product_id,
top_item.revenue AS top_item_revenue
FROM users u
-- LATERAL: for each user, run this subquery
LEFT JOIN LATERAL (
SELECT
o.id AS order_id,
o.created_at,
o.total_cents
FROM orders o
WHERE o.user_id = u.id -- references the outer row
ORDER BY o.created_at DESC
LIMIT 3
) recent_orders ON true
-- LATERAL: for each (user, order) row, find the top item
LEFT JOIN LATERAL (
SELECT
oi.product_id,
oi.quantity * oi.unit_price_cents AS revenue
FROM order_items oi
WHERE oi.order_id = recent_orders.order_id
ORDER BY revenue DESC
LIMIT 1
) top_item ON true
WHERE u.is_active = true
ORDER BY u.id, recent_orders.created_at DESC;
One query. The two LATERAL joins replace both Python for-loops and the inner MAX computation.
The ON true after each lateral join is intentional — LATERAL joins do not need a join condition because the correlation is inside the subquery.
EXPLAIN ANALYZE numbers
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ... -- the lateral query above
-- Output (abbreviated):
-- Nested Loop Left Join (cost=0.85..2841.23 rows=300 width=72)
-- (actual time=0.142..72.841 rows=291 loops=1)
-- -> Seq Scan on users (actual time=0.021..1.203 rows=100 loops=1)
-- Filter: is_active
-- -> Limit (actual time=0.183..0.412 rows=3 loops=100)
-- -> Index Scan on orders (actual time=0.042..0.089 rows=3 loops=100)
-- Index Cond: (user_id = u.id)
-- -> Limit (actual time=0.054..0.054 rows=1 loops=291)
-- -> Sort (actual time=0.048..0.049 rows=1 loops=291)
--
-- Planning Time: 0.843 ms
-- Execution Time: 73.2 ms
73ms vs ~1,200ms. The Nested Loop in the EXPLAIN output is Postgres doing the same work our Python loops were doing — but inside the database engine, with no network round-trips, and using index scans instead of full table scans.
The indexes that make it fast
The LATERAL query relies on two indexes. Without them, Postgres would do sequential scans on every iteration:
-- For the orders LATERAL — user_id + created_at for the ORDER BY + LIMIT
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
-- For the order_items LATERAL — order_id for the WHERE clause
CREATE INDEX idx_order_items_order_id
ON order_items (order_id);
With these indexes, each LATERAL iteration is an index scan of O(log n + k) where k is the LIMIT. Without them, it degrades to sequential scans and LATERAL becomes slower than the Python loops.
Three more places LATERAL shines
Top-N per group — the classic use case. Fastest 5 products per category, latest 10 events per sensor, most recent message per conversation thread.
Running calculations per row — compute a value that depends on the current row's columns, like "how many orders has this user placed in the 30 days before this specific order was created".
Unnesting arrays with context — when you UNNEST an array and need the parent row's columns alongside each element, LATERAL makes it clean.
-- Top-5 products per category
SELECT c.name AS category, top5.product_name, top5.revenue
FROM categories c
LEFT JOIN LATERAL (
SELECT p.name AS product_name, SUM(oi.quantity * oi.unit_price_cents) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
WHERE p.category_id = c.id
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 5
) top5 ON true;
When not to use LATERAL
LATERAL is not a silver bullet. If the outer table is large and the lateral subquery cannot use an index, you will get a sequential scan per row — much worse than a regular join. Always run EXPLAIN ANALYZE before and after and verify index usage.
For simple one-to-many joins without a LIMIT, a regular JOIN with a window function (ROW_NUMBER() OVER) is often more readable and equally fast.
The moment it clicked
LATERAL joins clicked for me when I stopped thinking about them as SQL syntax and started thinking about them as correlated subqueries that can appear in the FROM clause. If you have ever written a subquery in a WHERE clause that references the outer query — WHERE x IN (SELECT ... WHERE outer_table.id = ...) — LATERAL is that, but in FROM, with full row access and LIMIT support.
Once you see that pattern, the Python loops become SQL problems and the SQL problems become one query.