How to Avoid BROADCAST
Hi,
i am trying to test couple of things and once do a join it is getting BROADCAST.Please Help me understanding why the BROADCAST is comming after the Proper segmentation.
Table1
-----------
select * from t5;
a | b | c
---+---+---
2 | 2 | 3
5 | 2 | 3
1 | 2 | 3
3 | 2 | 3
4 | 2 | 3
(5 rows)
Table 2:
select * from t4;
a | b | c
---+---+---
2 | 2 | 3
5 | 2 | 3
1 | 2 | 3
3 | 2 | 3
4 | 2 | 3
(5 rows)
Custom Projections
-----------------
CREATE PROJECTION t5_1
(
a
)
AS
SELECT t5.a
FROM t5
ORDER BY t5.a
SEGMENTED BY hash(t5.a) ALL NODES KSAFE 1;
CREATE PROJECTION t4_1
(
a
)
AS
SELECT t4.a
FROM t4
ORDER BY t4.a
SEGMENTED BY hash(t4.a) ALL NODES KSAFE 1;
EXPLAIN PLAN(RESEGMENT)
---------------------------------------
explain select a.a,b.a from "263723_egbw_rvcpoc".t5 a,"263723_egbw_rvcpoc".t4 b where a.a=b.a;
Access Path:
+-JOIN MERGEJOIN(inputs presorted) [Cost: 8, Rows: 1 (NO STATISTICS)] (PATH ID: 1) Outer (RESEGMENT) Inner (RESEGMENT)
| Join Cond: (a.a = b.a)
| Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for a [Cost: 3, Rows: 5 (NO STATISTICS)] (PATH ID: 2)
| | Projection: 263723_egbw_rvcpoc.t5_b0
| | Materialize: a.a
| | Execute on: All Nodes
| +-- Inner -> STORAGE ACCESS for b [Cost: 3, Rows: 5 (NO STATISTICS)] (PATH ID: 3)
| | Projection: 263723_egbw_rvcpoc.t4_b0
| | Materialize: b.a
| | Execute on: All Nodes
EXPLAIN PLAN 2(BROADCAST)
---------------------------------------------
explain select * from "263723_egbw_rvcpoc".t5 a,"263723_egbw_rvcpoc".t4 b where a.a=b.a;
Access Path:
+-JOIN MERGEJOIN(inputs presorted) [Cost: 14, Rows: 1 (NO STATISTICS)] (PATH ID: 1) Inner (BROADCAST)
| Join Cond: (a.a = b.a)
| Materialize at Output: a.b, a.c
| Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for a [Cost: 3, Rows: 5 (NO STATISTICS)] (PATH ID: 2)
| | Projection: 263723_egbw_rvcpoc.t5_b0
| | Materialize: a.a
| | Execute on: All Nodes
| | Runtime Filter: (SIP1(MergeJoin): a.a)
| +-- Inner -> STORAGE ACCESS for b [Cost: 9, Rows: 5 (NO STATISTICS)] (PATH ID: 3)
| | Projection: 263723_egbw_rvcpoc.t4_b0
| | Materialize: b.a, b.b, b.c
| | Execute on: All Nodes
Why the RESEGMENT is happening even if column is Segmented properly on Column A and its going Inner join.Even i see the hash also return the same result.
select *,hash(b.a),hash(a.a) from t5 a, t4 b where a.a=b.a;
a | b | c | a | b | c | hash | hash
---+---+---+---+---+---+---------------------+---------------------
2 | 2 | 3 | 2 | 2 | 3 | 1618211815126016456 | 1618211815126016456
5 | 2 | 3 | 5 | 2 | 3 | 8299427032879314813 | 8299427032879314813
1 | 2 | 3 | 1 | 2 | 3 | 5783548743464686114 | 5783548743464686114
3 | 2 | 3 | 3 | 2 | 3 | 3883506187811235133 | 3883506187811235133
4 | 2 | 3 | 4 | 2 | 3 | 5130139942120141781 | 5130139942120141781
Comments
Firstly, you are running a select * in your query so it cant use the custom projections you created.
You can run select export_objects('', 'table_name') to see all your projections.
Secondly, run select analyze_statistics on both your tables.
I think either, after stats colelction optimizer sholud take the proper projection
Anyway, if you've got merge join, just foget about other things and be happy