Here is a performance comparison of lateral join alternatives. UPDATE 2: There are a few more ways to write this query without using lateral joins. That’s a 10x difference! But of course this could all change depending on your actual use case. It doesn’t look like that makes much difference though.) If I run both queries with \timing on, I consistently get about 80ms for the first one and 8 for the second one. (Note that these plans both include an ORDER BY i.score DESC that wasn’t in the original queries above. So it looks like the LATERAL version will be faster. > Index Scan using idx_inspections_rest_and_date on inspections i2 (cost=0.29.59.56 rows=30 width=20) > Nested Loop Left Join (cost=.33 rows=1000 width=20) I get this plan for the LATERAL join: Sort (cost=23.66 rows=1000 width=20) > Index Only Scan using idx_inspections_rest_and_date on inspections i2 (cost=0.29.918.29 rows=30000 width=12) Join Filter: (i.inspected_at Seq Scan on inspections i (cost=.00 rows=30000 width=16) Hash Cond: (i.restaurant_id = i2.restaurant_id) Specifying conflict actions UPDATE RETURNING DELETE RETURNING. > Hash Right Join (cost=14.79 rows=20000 width=20) SELECT Locking clause Lateral joins INSERT and INSERT RETURNING. Now when I EXPLAIN my queries, I get this plan for the correlated sub-query: Sort (cost=52.56 rows=20000 width=20) ' ':: date - concat(m, ' MONTHS ')::intervalĬREATE INDEX idx_inspections_biz_and_date ON inspections (restaurant_id ASC, inspected_at DESC) SELECT s, ' Foo ' FROM generate_series( 1, 1000) s I can achieve that with a correlated sub-query: Then I’ll join to inspections and include any inspections than which no inspection is later (i.e. With no lateral joins, my thought process goes like this: Since we want one row per restaurant, I’m going to say FROM restaurants. This is the kind of thing that is easy to express in English, and is a completely reasonable request for a report, but is hard to implement. So each restaurant has a history of inspections, and each inspection can have zero or more violations.įirst, you want to show a list of restaurants with the date and score of their most recent inspection. It has three tables: restaurants -< inspections -< violations For example, if getproductnames () returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this: SELECT m.name FROM manufacturers m LEFT JOIN LATERAL getproductnames (m.id) pname ON true WHERE pname IS NULL But when I execute the. The original question is about an app that shows restaurant inspection results. Lateral joins can be incredibly useful when you need them, but it’s hard to grok their “shape” without a concrete example. A question came up on the pdxruby mailing list that is a great example for Postgres’s new LATERAL join feature. start_date ) as days_diffĬROSS JOIN LATERAL ( SELECT to_date (l. In the following example, start_timestamp and end_timestamp are being parsed to a date and the output of those calculations are then being used multiple times from the SELECT statement. Otherwise, you would have to recalculate values for each usage of them in the SELECT statement. Since you can reference columns from other records in the query, you can use LATERAL to calculate values and then reuse them in the main SELECT statement. This is a non-obvious use of LATERAL but one I use often. pl_percentageĬROSS JOIN LATERAL get_trade_pl (t. If you have a User-Defined function that needs to be run for each row of a query, you can use the LATERAL join to call it and return multiple values. Effectively, it behaves like a LEFT JOIN.Ģ. NOTE: I use CROSS JOIN LATERAL above which is eqivalent to LEFT JOIN LATERAL (.) a ON true (as shown in LATERAL examples elsewhere) but I find it more readable. amountĬROSS JOIN LATERAL ( SELECT id as order_id, date, amount Without a LATERAL join this type of query would be non-trivial. Examplesįor each customer, we will return the 2 most recent orders. It is similar to a correlated subquery in that it can reference values from another query but has the added advantages that it can return multiple values and be used in the FROM clause. In a gist, it allows you to perform a sub-query in the FROM clause and reference column values from other records in the query. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.) This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. The LATERAL key word can precede a sub-SELECT FROM item. So, what is a LATERAL join anyway? From the PostgreSQL documentation: The LATERAL join is an interesting and powerful join type that is a bit intimidating at first but when you take a closer look it is very useful in certain scenarios.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |