We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


How to Avoid BROADCAST — Vertica Forum

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 :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file