理解PostgreSQL中的LATERAL

PostgreSQL中文社区2021-07-17

2724

作者 Hans-Jürgen Schönig 译者张文升

LATERAL是PostgreSQL和其他关系数据库(如Oracle、DB2和MS SQL)不太为⼈所知的特性之⼀。然⽽， LATERAL是⼀个⾮常有⽤的特性，看看使⽤它们可以实现什么。

深究 FROM

在我们深⼊研究LATERAL之前，有必要坐下来从更哲学的层⾯考虑 SQL 中的 SELECT 和 FROM ⼦句。下⾯是⼀个例⼦：

SELECT whatever FROM tab

基本上，我们可以将此语句视为⼀个循环。⽤伪代码编写此 SQL 语句类似于以下代码段：

for x in tab 
loop  
do whatever 
end loop

对于表中的每个条⽬，执⾏SELECT⼦句的操作。通常数据只是按原样返回。⼀个SELECT语句可以看作是⼀个循环。但是如果我们需要⼀个“嵌套”循环呢?这正是LATERAL的优势所在。

LATERAL joins : 创建示例数据

让我们想象⼀个简单的例⼦。假设我们有⼀系列的产品，我们也有客户的愿望清单。现在的⽬标是为每个愿望清单找到最好的3个产品。下⾯的SQL⽚段创建了⼀些示例数据:

CREATE TABLE t_product AS
SELECT id AS product_id,
id * 10 * random() AS price,
'product ' || id AS product
FROM generate_series(1, 1000) AS id;
CREATE TABLE t_wishlist
(
wishlist_id int,
username text,
desired_price numeric
);
INSERT INTO t_wishlist VALUES
(1, 'hans', '450'),
(2, 'joe', '60'),
(3, 'jane', '1500');

产品表中填充了1000个产品。价格是随机的，我们⽤了⼀个很有创意的名字来命名产品:

test=# SELECT * FROM t_product LIMIT 10;
product_id | price | product
------------+--------------------+------------
1 | 6.756567642432323 | product 1
2 | 5.284467408540081 | product 2
3 | 28.284196164210904 | product 3
4 | 13.543868035690423 | product 4
5 | 30.576923884383156 | product 5
6 | 26.572431211361902 | product 6
7 | 64.84599396020204 | product 7
8 | 21.550701384168747 | product 8
9 | 28.995584553969174 | product 9
10 | 17.31335004787411 | product 10
(10 rows)

接下来，我们有⼀个愿望清单。

如您所⻅，愿望清单属于⼀个⽤户，我们想要推荐的这三种产品都有⼀个期望的价格。

Running LATERAL joins After providing some sample data and loading it into our PostgreSQL database, we can approach the problem and try to come up with a solution.

Suppose we wanted to find the top three products for every wish, in pseudo-code:

运⾏ LATERAL joins

在提供⼀些示例数据并将其加载到PostgreSQL数据库之后，我们就可以着⼿解决这个问题并尝试找到解决⽅案。

假设我们想要在伪代码中找到每个愿望的前三名产品:

for x in wishlist
loop
for y in products order by price desc
loop
found++
if found <= 3
then
return row
else
jump to next wish
end
end loop
end loop

重要的是我们需要两个循环。⾸先，我们需要遍历愿望清单，然后查看已排序的产品列表，选择3个并继续下⼀个愿望清单。

让我们看看如何使⽤LATERAL-join来实现这⼀点:

SELECT *
FROM t_wishlist AS w,
LATERAL (SELECT *
FROM t_product AS p
WHERE p.price < w.desired_price
ORDER BY p.price DESC
LIMIT 3
) AS x
ORDER BY wishlist_id, price DESC;

我们将⼀步⼀步地完成它。您在FROM⼦句中⾸先看到的是t_wishlist表。LATERAL现在所能做的就是使⽤愿望清单中的条⽬来发挥它的魔⼒。因此，对于愿望清单中的每个条⽬，我们都选择了三个产品。为了弄清楚我们需要哪些产品，我们可以使⽤w.desired_price。换句话说：它就像⼀个“带参数的连接”。FROM ⼦句是我们伪代码中的“外部循环”，⽽LATERAL可以看作是“内部循环”。

结果集如下所示：

wishlist_id | username | desired_price | product_id | price
| product
-------------+----------+---------------+------------+--------------------
+-------------
1 | hans | 450 | 708 | 447.0511375753179
| product 708
1 | hans | 450 | 126 | 443.6560873146138
| product 126
1 | hans | 450 | 655 | 438.0566432022443
| product 655
2 | joe | 60 | 40 | 59.32252841190291
| product 40
2 | joe | 60 | 19 | 59.2142714048882
| product 19
2 | joe | 60 | 87 | 58.78014573804254
| product 87
3 | jane | 1500 | 687 | 1495.8794483743645
| product 687
3 | jane | 1500 | 297 | 1494.4586352980593
| product 297
3 | jane | 1500 | 520 | 1490.7849437550085
| product 520
(9 rows)

PostgreSQL为每个愿望清单返回3个条⽬，这正是我们想要的。这⾥的重要部分是，LIMIT⼦句位于 LATERAL的SELECT内部。因此，它限制了每个愿望清单的⾏数，⽽不是总⾏数。

PostgreSQL在优化LATERAL连接⽅⾯做得⾮常好。在我们的例⼦中，执⾏计划看起来⾮常简单:

test=# explain SELECT *
FROM t_wishlist AS w,
LATERAL (SELECT *
FROM t_product AS p
WHERE p.price < w.desired_price
ORDER BY p.price DESC
LIMIT 3
) AS x
ORDER BY wishlist_id, price DESC;
QUERY PLAN
--------------------------------------------------------------------------
-------------
Sort (cost=23428.53..23434.90 rows=2550 width=91)
Sort Key: w.wishlist_id, p.price DESC
-> Nested Loop (cost=27.30..23284.24 rows=2550 width=91)
-> Seq Scan on t_wishlist w (cost=0.00..18.50 rows=850 width=68)
-> Limit (cost=27.30..27.31 rows=3 width=23)
-> Sort (cost=27.30..28.14 rows=333 width=23)
Sort Key: p.price DESC
-> Seq Scan on t_product p (cost=0.00..23.00 rows=333
width=23)
Filter: (price < (w.desired_price)::double
precision)
(9 rows)