PostgreSQL: performance optimization When Using LATERAL JOIN with Large Datasets
I've been banging my head against this for hours. I'm working with important performance optimization when using `LATERAL JOIN` in PostgreSQL 13.3 with large datasets. I have a main table `orders` with millions of rows and a related table `order_items` that can have multiple entries for each order. My intention is to fetch the most expensive item for each order using the following query: ```sql SELECT o.order_id, item.* FROM orders o JOIN LATERAL ( SELECT * FROM order_items oi WHERE oi.order_id = o.order_id ORDER BY oi.price DESC LIMIT 1 ) item ON true; ``` While this seems straightforward, the execution time often exceeds several seconds, which is unacceptable for my application's needs. I've tried creating indexes on the `order_items.order_id` and `order_items.price` fields, but the performance hasn't improved significantly. When I analyze the query plan using `EXPLAIN ANALYZE`, I notice that it’s performing a sequential scan on `order_items`, which is not what I expected since I have the indexes in place. Could there be an scenario with how `LATERAL` is being processed, or is there a more efficient way to achieve the same result without incurring such high overhead? Any advice or alternative approaches would be greatly appreciated. I'm working on a application that needs to handle this. What am I doing wrong?