左连接横向和阵列聚合[英] Left Join Lateral and array aggregates

本文是小编为大家收集整理的关于左连接横向和阵列聚合的处理方法,想解了左连接横向和阵列聚合的问题怎么解决?左连接横向和阵列聚合问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我使用的是 Postgres 9.3.

我有两个表 T1 和 T2 以及它们之间的 n:m 关系 T1_T2_rel.现在我想创建一个视图,除了 T1 的列之外,还为 T1 中的每个记录提供一个列,该列包含一个数组,其中包含 T2 的所有相关记录的主键 ID.如果 T2 中没有相关条目,则该列的相应字段应包含空值.

我的模式的抽象版本如下所示:

CREATE TABLE T1 ( t1_id serial primary key, t1_data int );

CREATE TABLE T2 ( t2_id serial primary key );

CREATE TABLE T1_T2_rel (
  t1_id int references T1( t1_id )
, t2_id int references T2( t2_id )
);

相应的样本数据可以生成如下:

INSERT INTO T1 (t1_data)
SELECT cast(random()*100 as int) FROM generate_series(0,9) c(i);

INSERT INTO T2 (t2_id) SELECT nextval('T2_t2_id_seq') FROM generate_series(0,99);

INSERT INTO T1_T2_rel
SELECT cast(random()*10 as int) % 10 + 1 as t1_id
     , cast(random()*99+1 as int) as t2_id
FROM   generate_series(0,99);

到目前为止,我提出了以下查询:

SELECT T1.t1_id, T1.t1_data, agg
FROM T1
LEFT JOIN LATERAL (
   SELECT t1_id, array_agg(t2_id) as agg
   FROM T1_T2_rel
   WHERE t1_id=T1.t1_id
   GROUP BY t1_id
   ) as temp ON temp.t1_id=T1.t1_id;

这有效.但是,可以简化吗?

可以在此处找到相应的小提琴:sql-fiddle.不幸的是,sql-fiddle 不支持横向连接所需的 Postgres 9.3(尚).

[更新] 正如已经指出的那样,原则上使用子查询的简单 left join 就足够了.但是,如果我比较查询计划,Postgres 在使用 left join 时对聚合表采用顺序扫描,而在 left join lateral 的情况下使用索引扫描.

推荐答案

正如@Denis 已经评论过的:不需要 LATERAL.此外,您的子查询选择了错误的列.这有效:

SELECT t1.t1_id, t1.t1_data, t2_ids
FROM   t1
LEFT   JOIN (
    SELECT t1_id, array_agg(t2_id) AS t2_ids
    FROM   t1_t2_rel
    GROUP  BY 1
    ) sub USING (t1_id);

-SQL 小提琴.

性能和测试

关于您提到的随后的顺序扫描:如果您查询整个表,顺序扫描通常更快.取决于您运行的版本、您的硬件、您的设置和基数统计以及您的数据分布.试验像 WHERE t1.t1_id < 1000 或 WHERE t1.t1_id = 1000 这样的选择性 WHERE 子句,并结合 规划器设置以了解选择:

SET enable_seqscan = off;
SET enable_indexscan = off;

重置:

RESET enable_seqscan;
RESET enable_indexscan;

请注意,仅在您的本地会话中!这个相关的答案dba.SE 有更多说明.
当然,您的设置也可能关闭:

本文地址:https://www.itbaoku.cn/post/1764050.html