-- Using LATERAL without proper indexing

SELECT c.name, o.order_date

FROM customers c,

LATERAL (

SELECT order_date FROM orders

WHERE customer_id = c.customer_id -- No index on customer_id

ORDER BY order_date DESC LIMIT 3

) o;

-- Index the join column in LATERAL subquery

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

SELECT c.name, o.order_date

FROM customers c,

LATERAL (

SELECT order_date FROM orders

WHERE customer_id = c.customer_id

ORDER BY order_date DESC LIMIT 3

) o;

LATERAL executes subquery for each outer row. Index columns used in WHERE and ORDER BY.

LATERAL without proper indexing causes full table scans per row