Resegmentation During GROUP BY

Moderator: NorbertKrupa

Post Reply
karthiksharmasg
Newbie
Newbie
Posts: 1
Joined: Mon Oct 16, 2017 3:48 pm

Resegmentation During GROUP BY

Post by karthiksharmasg » Mon Oct 16, 2017 3:52 pm

Hi,
i am worried about the resegmentation during Group BY
say

-- to create super projection use ENCODING AUTO

Code: Select all

create table foo (a int ENCODING AUTO, b int, c int)

Code: Select all

CREATE PROJECTION foo_pj
(a,b,c) AS
select a,b,c from foo 
SEGMENTED BY HASH(a,b,c) 
ALL NODES OFFSET 0;

Code: Select all

CREATE PROJECTION foo_pj1
(b,c) AS
select b,c from foo 
SEGMENTED BY HASH(b,c) 
ALL NODES OFFSET 0;
---Check projection is fine

Code: Select all

select projection_basename,segment_expression,is_super_projection from projections where anchor_table_name='foo'
foo hash(foo.a, foo.b, foo.c) true
foo_pj hash(foo.a, foo.b, foo.c) true
foo_pj1 hash(foo.b, foo.c, foo.a) true

Now, when i execute following

Code: Select all

explain SELECT F.b,F.a FROM foo AS F, foo WHERE foo.b = F.a GROUP BY 1,2;
this is pipelined as

+-GROUPBY PIPELINED [Cost: 4, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Group By: F.a, F.b
| +---> JOIN HASH [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 2)

but for

Code: Select all

explain SELECT F.b,F.c FROM foo AS F, foo WHERE F.b = F.c GROUP BY 1,2;\
i an getting HASH,

+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Group By: F.b, F.c
| +---> JOIN (CROSS JOIN) [Cost: 4, Rows: 1 (NO STATISTICS)] (PATH ID: 2)
| | +-- Outer -> STORAGE ACCESS for foo [Cost: 1, Rows: 1 (NO STATISTICS)] (PATH ID: 3)

This means the second projection (foo_pj1) created has no effect on the SEGMENTATION?
Or my understanding of this is wrong please clarify.

Karthik S G

P.S posted same also in forum.vertica.com

Post Reply

Return to “General”