Stream Operation Hints

Description

A method of using stream in a parallel execution plan is specified. The value can be broadcast or redistribute, indicating that data is broadcast or redistributed.

Syntax

1

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

Parameters

• For details about @queryblock, see Hint for Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block.
• broadcast, redistribute, and local_roundrobin indicate data distribution methods.
  • no specifies that the specified hint will not be used for a stream operation.
  • table_list specifies one or more tables on which the stream operation is performed. Multiple tables are separated by spaces. For example, broadcast(t1) and broadcast(t1 t2).

Examples

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)
-- Specify an execution plan to broadcast stream_t2.
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)
-- The data of stream_t2 is broadcast and then joined with stream_t1. In this example, four concurrent requests are enabled, and a table is broadcast to other threads for parallel hash join. The size of table stream_t2 is smaller than that of table stream_t1. Therefore, broadcasting table t2 brings lower performance overhead.

-- Specify an execution plan where stream_t2 uses local_roundrobin.
SET explain_perf_mode=pretty; -- Open the explain pretty option to view a detailed plan.
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)

The stream_t2 table uses the local_roundrobin data distribution method.

The local_roundrobin hint takes effect only when the degree of parallelism for table scanning is 1. You are advised to use this hint with scandop hint.

Stream hints take effect only when parallel execution plans are generated.