vertica lap

gistfile1.txt

CREATE TABLE test  (
    k1 int,
    d date not null,
    some_col int,
    val int  )
PARTITION BY (d); 


CREATE PROJECTION test_p1 (  k1 ENCODING RLE,  d, some_col, val )
AS SELECT * FROM test  ORDER BY k1
SEGMENTED BY hash(k1,val) all nodes OFFSET 0 ksafe 0;

CREATE PROJECTION test_lap  
AS SELECT d, some_col, sum(val) sval, count(val) cval,  min(val) mval
 FROM test 
GROUP BY d, some_col;



insert into test
(
 WITH seq AS (
    SELECT ROW_NUMBER() OVER() AS num FROM (
        SELECT 1 FROM (
            SELECT date(0) + INTERVAL '1 second' AS se UNION ALL
            SELECT date(0) + INTERVAL '100000 seconds' AS se ) a
        TIMESERIES tm AS '1 second' OVER(ORDER BY se)
    ) b
)
 SELECT num %5, 'today',num%3, 1  FROM seq
)


explain select sum(val), min(val) from test group by some_col

Access Path:
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 2, Rows: 3 (NO STATISTICS)] (PATH ID: 1)
|  Aggregates: sum(test_lap.sval), min(test_lap.mval)
|  Group By: test_lap.some_col
| +---> STORAGE ACCESS for dw.test_lap (Rewritten LAP) [Cost: 1, Rows: 3 (NO STATISTICS)] (PATH ID: 2)
| |      Projection: dw.test_lap
| |      Materialize: test_lap.some_col, test_lap.sval, test_lap.mval

drop table test cascade