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( [@queryblock] table_list)

Parameters

• For details about @queryblock, see Hint 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 and redistribute
  • 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.

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