Stream方式的Hint

功能描述

在并行的执行计划中，指定stream的使用方法，可以为broadcast或者redistribute，表示对数据进行广播或者重分布。

语法格式

1

[no] broadcast|redistribute|local_roundrobin( [@queryblock] table_list)

参数说明

• @queryblock请参见指定Hint所处于的查询块Queryblock，可省略，表示在当前查询块生效。
• broadcast、redistribute和local_roundrobin表示数据分布方法。
  • no表示hint的stream方式不使用。
  • table_list为进行stream操作的单表或多表，多个表之间使用空格分隔。例如：broadcast(t1)，broadcast(t1 t2)。

示例

create table stream_t1(a int, b int);
insert into stream_t1 values(generate_series(1, 1000000), generate_series(1, 1000000));
analyze stream_t1;
create table stream_t2(a int, b int);
insert into stream_t2 values(generate_series(1, 10000), generate_series(1, 10000));
analyze stream_t2;
set query_dop = 4;
explain (costs off) select/*+ broadcast(stream_t1)*/ * from stream_t1 join stream_t2 on (stream_t1.a = stream_t2.a);
                          QUERY PLAN
--------------------------------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)
   ->  Hash Join
         Hash Cond: (stream_t1.a = stream_t2.a)
         ->  Streaming(type: BROADCAST dop: 4/4)
               ->  Seq Scan on stream_t1
         ->  Hash
               ->  Streaming(type: LOCAL ROUNDROBIN dop: 4/1)
                     ->  Seq Scan on stream_t2
(8 rows)
-- 指定stream_t2进行broadcast的执行计划
explain (costs off) select/*+ broadcast(stream_t2)*/ * from stream_t1 join stream_t2 on (stream_t1.a = stream_t2.a);
                      QUERY PLAN
-------------------------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)
   ->  Hash Join
         Hash Cond: (stream_t1.a = stream_t2.a)
         ->  Seq Scan on stream_t1
         ->  Hash
               ->  Streaming(type: BROADCAST dop: 4/1)
                     ->  Seq Scan on stream_t2
(7 rows)
-- 表示使用将stream_t2的数据进行broadcast之后再和stream_t1进行join。样例开启了4并发，此时broadcast将广播一张表至其他线程进行并行hash join，由于stream_t2表大小比stream_t1表小，广播t2可以带来更低的性能开销。

-- 指定stream_t2进行local_roundrobin的执行计划
SET explain_perf_mode=pretty; --打开explain pretty选项，可以看到更详尽计划
EXPLAIN (costs off) SELECT/*+ local_roundrobin(stream_t2)*/ * FROM stream_t1 JOIN stream_t2 ON (stream_t1.a = stream_t2.a);
 id |                        operation                        
----+---------------------------------------------------------
  1 | ->  Streaming(type: LOCAL GATHER dop: 1/4)
  2 |    ->  Hash Join (3,5)
  3 |       ->  Streaming(type: BROADCAST dop: 4/4)
  4 |          ->  Seq Scan on stream_t1
  5 |       ->  Hash
  6 |          ->  Streaming(type: LOCAL ROUNDROBIN dop: 4/1)
  7 |             ->  Seq Scan on stream_t2
(7 rows)

 Predicate Information (identified by plan id)  
------------------------------------------------
   2 --Hash Join (3,5)
         Hash Cond: (stream_t1.a = stream_t2.a)
(2 rows)

可以看到stream_t2表使用了local_roundrobin的数据分布方式。

local_roundrobin hint只有在表扫描并行度为1的时候才会生效，建议和scandop hint一起使用。

只有在生成并行执行计划的时候，stream hint才会生效。