postgresql 关于Postgres LATERAL JOIN的 predicate

to94eoyn  于 11个月前  发布在  PostgreSQL
关注(0)|答案(3)|浏览(205)

Postgres LATERAL JOINON predicate 如何工作?
让我澄清一下问题,我已经阅读了官方文档和一些关于这种JOIN的文章。
据我所知,它是一个foreach循环,里面有一个相关的子查询

  • 它迭代表A的所有记录,从而允许引用相关子查询B中的“当前”行的列并将B的结果集连接到A的“当前”行--如果B查询返回1行,则只有一对,并且如果B查询返回N行,则存在N对与A的“当前”行重复。与通常的JOIN中的行为相同。

但是为什么需要ON predicate 呢?
对我来说,在通常的JOIN中,我们使用ON,因为我们需要过滤掉2个表的carnival乘积,而LATERAL JOIN的情况并非如此,它直接产生结果对。
换句话说,在我的开发经验中,我只见过CROSS JOIN LATERALLEFT JOIN LATERAL () ON TRUE(尽管后者看起来很笨拙),但有一天,一位同事向我展示了

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
    FROM route_to_shipment rts
    where rts.route_id = r.route_id
    GROUP BY rts.route_id
) rts using (route_id)

字符串
这让我大吃一惊。为什么是using (route_id)?我们已经在子查询中有where rts.route_id = r.route_id了!
也许我对LATERAL连接的机制理解错了?

plicqrtu

plicqrtu1#

Postgres LATERAL JOIN的ON predicate 是如何工作的?
和常规的非LATERALJOIN一样,它独立于子查询中发生的事情。
但为什么需要ON predicate ?
事实上,子查询包括什么 * 可以 * 以与连接条件相同的方式有效地使用,并没有改变JOIN外部的语法要求。
为什么要使用(route_id)?我们已经在子查询中有了where rts.route_id = r.route_id!
无论你是在where的子查询内部,join的外部,还是在where的外部,或者这三个点的任何组合中应用条件,它都不会改变结果。在这种情况下,一个有用的区别是,join允许PostgreSQL更好地推理查询并优化它:将using (route_id)更改为on true将删除该优点。
看起来这并不重要,理想情况下,它不应该。不幸的是,Planner/Optimizer仍然必须以某种方式理解任何查询的声明性含义。显式的join条件清楚地传达了依赖性,并允许它检查连接表之间的关系,以应用适当的优化技术。lateral join (...)subquery on true仅意味着要对子查询进行评估,每一行和on true混淆了 * 它如何 * 依赖于那一行-这是作为横向子查询的内部逻辑而保持不变的,而lateral仅仅是allows它使用外部引用,而没有与规划器进行更多的通信,所以它是left unoptimised
理想情况下,规划器应该查看内部并看到where,但它没有(至少在PostgreSQL 16.1中)。如果您将where移出外部查询old-implicit-join-style,它就会doesn't这样做,尽管这确实有助于它在其他方面加快速度。
当你使用run your friend's query时,它只会让join中的lateral变得更明显,而且实际上不需要join。你没有从子查询中请求任何东西,所以它只提供route的匹配,这对于计算匹配行的组合可能很有用,但是因为你也在那里聚合,它最多只能提供一个匹配。最后,结果是它对查询没有任何贡献,可以有效地缩短为:

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT distinct rts.route_id
    FROM route_to_shipment rts
) rts using (route_id)
group by 1

字符串
但是,由于left joinleft join会忽略不匹配的route_to_shipment.route_id,而left join会获取不匹配的route.route_id,无论它们是否存在于子查询中,因此可以完全删除:

SELECT r.acceptance_status, count(*)
FROM route r group by 1;


对于所有三种形式的查询,您都需要get the exact same plan,在所有情况下都非常简短和快速:

QUERY PLAN
Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.590..0.591 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.583..0.585 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        Batches: 1  Memory Usage: 40kB
        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.007..0.158 rows=2000 loops=1)
              Output: r.route_id, r.acceptance_status
Planning Time: 0.056 ms
Execution Time: 0.608 ms


如果你使用left lateral join...on true,你就假装你想为每一行计算子查询,不管是什么,你混淆了依赖关系,所以规划器确实这样做了:

QUERY PLAN
Incremental Sort  (cost=475713.66..475735.06 rows=200 width=40) (actual time=3076.386..3076.388 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Presorted Key: r.acceptance_status
  Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
  ->  GroupAggregate  (cost=475713.59..475726.06 rows=200 width=40) (actual time=3076.153..3076.348 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        ->  Sort  (cost=475713.59..475717.08 rows=1397 width=32) (actual time=3076.035..3076.135 rows=2000 loops=1)
              Output: r.acceptance_status
              Sort Key: r.acceptance_status
              Sort Method: quicksort  Memory: 86kB
              ->  Nested Loop Left Join  (cost=0.00..475640.60 rows=1397 width=32) (actual time=34.858..3074.645 rows=2000 loops=1)
                    Output: r.acceptance_status
                    ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=36) (actual time=0.013..0.387 rows=2000 loops=1)
                          Output: r.route_id, r.acceptance_status
                    ->  GroupAggregate  (cost=0.00..340.44 rows=1 width=36) (actual time=1.536..1.536 rows=1 loops=2000)
                          Output: rts.route_id, NULL::integer[]
                          ->  Seq Scan on public.route_to_shipment rts  (cost=0.00..340.43 rows=101 width=4) (actual time=0.166..1.532 rows=10 loops=2000)
                                Output: rts.shipment_id, rts.route_id
                                Filter: (rts.route_id = r.route_id)
                                Rows Removed by Filter: 19990
Planning Time: 0.134 ms
JIT:
  Functions: 11
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.896 ms, Inlining 0.000 ms, Optimization 1.520 ms, Emission 31.319 ms, Total 33.735 ms
Execution Time: 3279.075 ms


Complete demo at db<>fiddle:字符串

flvlnr44

flvlnr442#

这个问题的一个变体已经在https://dba.stackexchange.com/questions/301884/do-postgresql-lateral-joins-require-or-allow-an-on-clause上得到了回答。
简而言之,ON子句是CROSS JOINNATURAL JOIN之外的语法要求(后者是一个构思不良的想法,应该从SQL中删除)。对于LEFT JOIN LATERAL,使用ON TRUE而不是USING,以避免对子查询的选择列表的不必要依赖。

pxy2qtax

pxy2qtax3#

你对横向连接如何工作的理解基本上是正确的,你对强制性on子句的困惑也是正确的。
一个例子是set returning functions:你不能在函数中放置where子句;如果你需要一个呢?

select c.id, c.validfrom, c.validupto, c.cancelledon, years.year
from contracts as c
join lateral generate_series(c.validfrom, c.validupto) as years(year)
  on c.cancelledon is null or years.year < c.cancelledon

字符串
https://dbfiddle.uk/Yr2Fn4gP
另一个虚构的例子:从两个相关客户的最近10次购买中找到共同的物品:

select referrals.person1, referrals.person2, p1.product as both_bought
from referrals
join lateral (
    select product
    from purchases
    where customer = referrals.person1
    order by date desc
    limit 10
) as p1 on true
join lateral (
    select product
    from purchases
    where customer = referrals.person2
    order by date desc
    limit 10
) as p2 on p1.product = p2.product


第一个on子句用于满足语法要求,第二个子句使用来自两个横向连接的引用。

也就是说,可以将上面的on子句编写为最外层的where子句。使用onwhere之间的选择取决于您正在使用的RDBMS(该子句是允许的,可选的或不允许的)和您的个人偏好。

相关问题