子查询返回多列

drop table if exists deppeoples;
drop table if exists departments;
drop type if exists deppeoples01;
/****************************************************************************************
  部门表
****************************************************************************************/
create table departments(
  objectid serial not null,          --唯一编号
  parentid integer not null,          --上级部门,指向本表的objectid,0表示最顶级部门
  name text not null,              --部门名称
  describe text,                --部门备注
  generate timestamptz default(now()) not null, --创建时间
  state integer default(2) not null,      --状态.0已无效,(1<<1)正常
  constraint pk_departments_objectid primary key(objectid) with (fillfactor=80)
) with (fillfactor=80);
create index idx_departments_parentid on departments(parentid) with (fillfactor=80);




/****************************************************************************************
  部门人员表
****************************************************************************************/
create table deppeoples(
  objectid bigserial not null,        --唯一编号
  depid integer not null,            --部门编号,外键(departments->objectid,级联删除)
  name text not null,              --姓名
  title integer not null,            --职务.来自字典,测试数据用1-10分别表示,数据越大职务越高
  tel text not null,              --联系电话
  sex integer not null,            --性别.来自字典,测试数据随机生成
  national integer not null,          --民族.来自字典
  mail text,                  --邮箱
  describe text,                --备注
  generate timestamptz default(now()) not null, --创建时间
  state integer default(2) not null,      --状态.0已无效,(1<<1)正常
  constraint pk_deppeoples_objectid primary key(objectid) with (fillfactor=80),
  constraint fk_deppeoples_contid foreign key(depid) references departments(objectid) on delete cascade
) with (fillfactor=80);
create index idx_deppeoples_depid on deppeoples(depid,title) with (fillfactor=80);
```

insert into departments(parentid,name) values(0,'xxxxxx集团公司');
insert into departments(parentid,name)
  select
    1 as parentid,
    (random()*(1546272000-1514736000)+1514736000)::bigint as name
  from generate_series(1,999);
```

do $$
  declare
    v_start bigint;
    v_end bigint;
  begin
    for i in 1..1000 loop
      v_start := (i-1)*1000 + 1;
      v_end := v_start + 999;




      insert into deppeoples(depid,name,title,tel,sex,national)
        select
          (random()*(100-1)+1)::integer as depid,
          (random()*(1546272000-1514736000)+1514736000)::bigint as name,
          (random()*(9-1)+1)::integer as title,
          (random()*(13999999999-13000000000)+13000000000)::bigint as tel,
          (random()*(3-1)+1)::integer as sex,
          (random()*(54-1)+1)::integer as national
        from generate_series(v_start,v_end);
        raise notice  '%,%', v_start,v_end;
    end loop;
  end;
$$;
```

insert into deppeoples(depid,name,title,tel,sex,national)
  select
    objectid as depid,
    (random()*(1546272000-1514736000)+1514736000)::bigint as name,
    10 as title,
    (random()*(13999999999-13000000000)+13000000000)::bigint as tel,
    (random()*(3-1)+1)::integer as sex,
    (random()*(54-1)+1)::integer as national
  from departments;
```

vacuum  freeze verbose analyze departments;
vacuum  freeze verbose analyze deppeoples;
```

--禁用并行
set max_parallel_workers_per_gather=0;


explain (analyze,verbose,costs,buffers,timing)
select
  t1.name as department,
  t2.name,t2.title,t2.tel
from departments as t1
left join deppeoples as t2 on t2.depid=t1.objectid
where t2.title=10;
```
执行5次,取最后一次,可以看到left join共扫描了3012页,用时3.296 ms
```bash
                                                                     QUERY PLAN                                                                      
---------------------------------------------------------------------------------------
--------------------------------------------------------------
 Nested Loop  (cost=0.42..7796.00 rows=1 width=38) (actual time=3.257..3.257 rows=0 loops=1)
   Output: t1.name, t2.name, t2.title, t2.tel
   Buffers: shared hit=3012
   ->  Seq Scan on public.departments t1  
   (cost=0.00..21.00 rows=1000 width=15) 
   (actual time=0.009..0.200 rows=1000 loops=1)
         Output: t1.objectid, t1.parentid, t1.name, t1.describe, t1.generate, t1.state
         Buffers: shared hit=11
   ->  Index Scan using idx_deppeoples_depid on public.deppeoples t2  
   (cost=0.42..7.76 rows=1 width=31) 
   (actual time=0.003..0.003 rows=0 loops=1000)
         Output: t2.objectid, t2.depid, t2.name, t2.title, t2.tel, t2.sex, t2."national", 
         t2.mail, t2.describe, t2.generate, t2.state
         Index Cond: ((t2.depid = t1.objectid) AND (t2.title = 10))
         Buffers: shared hit=3001
 Planning Time: 0.314 ms
 Execution Time: 3.296 ms
(12 rows)
```

--重点创建row类型,定义输入的列名称和类型
drop type if exists deppeoples01;
create type deppeoples01 as (name1 text,title1 integer,tel1 text);


explain (analyze,verbose,costs,buffers,timing)
with cte as(
  select
    t1.name as department,
    (select row(name,title,tel)::deppeoples01 as r from deppeoples as t2 where 
    t2.depid=t1.objectid and title=10 order by objectid desc limit 1)
  from departments as t1
)select department,(r).name1,(r).title1,(r).tel1 from cte;
```
执行5次,取最后一次,可以看到子查询共扫描了3012页,用时8.827 ms
```bash
                                                                                QUERY PLAN                                                                                 
--------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
 CTE Scan on cte  (cost=8481.00..8501.00 rows=1000 width=100) (actual time=0.042..8.584 rows=1000 loops=1)
   Output: cte.department, (cte.r).name1, (cte.r).title1, (cte.r).tel1
   Buffers: shared hit=3012
   CTE cte
     ->  Seq Scan on public.departments t1  (cost=0.00..8481.00 rows=1000 width=43) (actual time=0.036..7.363 rows=1000 loops=1)
           Output: t1.name, (SubPlan 1)
           Buffers: shared hit=3012
           SubPlan 1
             ->  Limit  (cost=8.46..8.46 rows=1 width=40) (actual time=0.006..0.006 rows=0 loops=1000)
                   Output: (ROW(t2.name, t2.title, t2.tel)::deppeoples01), t2.objectid
                   Buffers: shared hit=3001
                   ->  Sort  (cost=8.46..8.46 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=1000)
                         Output: (ROW(t2.name, t2.title, t2.tel)::deppeoples01), t2.objectid
                         Sort Key: t2.objectid DESC
                         Sort Method: quicksort  Memory: 25kB
                         Buffers: shared hit=3001
                         ->  Index Scan using idx_deppeoples_depid on public.deppeoples t2  
                         (cost=0.42..8.45 rows=1 width=40) 
                         (actual time=0.003..0.003 rows=0 loops=1000)
                               Output: ROW(t2.name, t2.title, t2.tel)::deppeoples01, t2.objectid
                               Index Cond: ((t2.depid = t1.objectid) AND (t2.title = 10))
                               Buffers: shared hit=3001
 Planning Time: 0.237 ms
 Execution Time: 8.827 ms
(22 rows)
```