Projections With Expressions

Moderator: NorbertKrupa

Post Reply
darinbob
Newbie
Newbie
Posts: 10
Joined: Thu Mar 27, 2014 2:43 pm

Projections With Expressions

Post by darinbob » Fri Jul 24, 2015 4:34 pm

Is it possible to have projections with expressions and those projections have full statistics? From what I can tell these only have ROWCOUNT statistics.

I have a handful of these projections where I'm converting numeric source data into DATE and TIMESTAMP data types on load. However, I'm not getting the performance lift that I would expect by querying these projections instead of the anchor table. My explain plan shows NO STATISTICS for each join and I would think that's playing a role in the lackluster query performance.

Any advice is welcomed, thanks.

Darin

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Projections With Expressions

Post by NorbertKrupa » Fri Jul 24, 2015 8:22 pm

Could you possibly provide an example of what you mean by expression, and the SQL / EXPLAIN plan?
Checkout vertica.tips for more Vertica resources.

darinbob
Newbie
Newbie
Posts: 10
Joined: Thu Mar 27, 2014 2:43 pm

Re: Projections With Expressions

Post by darinbob » Fri Jul 24, 2015 8:45 pm

http://my.vertica.com/docs/7.1.x/HTML/i ... ssions.htm
http://my.vertica.com/docs/7.1.x/HTML/i ... ctions.htm

Code: Select all

EXPLAIN select p.*, c.CNAME from MYSCHEMA.MYTABLE_EXPRESSIONS_b0 AS p INNER JOIN MYSCHEMA.MYTABLE2 AS c on p.pcomp = c.ccomp and p."pclnt#" = c.cclnt

Code: Select all

------------------------------ 
QUERY PLAN DESCRIPTION: 
------------------------------

EXPLAIN select p.*, c.CNAME from MYSCHEMA.MYTABLE_EXPRESSIONS_b0 AS p INNER JOIN MYSCHEMA.MYTABLE2 AS c on p.pcomp = c.ccomp and p."pclnt#" = c.cclnt

Access Path:
+-JOIN HASH [Cost: 64K, Rows: 19M (NO STATISTICS)] (PATH ID: 1)
|  Join Cond: (p.PCOMP = c.CCOMP) AND (p."PCLNT#" = c.CCLNT)
|  Materialize at Output: p."PGUAR#", p.PSEQ, p.PDATE, p.TransactionDate, p.TransactionDateTime, p.PTYPE, p.POTYPE, p.PAYAMT, p.PCOMM, p.PBALAF, p.PCOLEC, p.PSPLIT, p.PSTATS, p.PCLREF, p.PSTMDT, p.StatementDate, p.DeleteOperation
|  Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for p [Cost: 11K, Rows: 19M (NO STATISTICS)] (PATH ID: 2)
| |      Projection: MYSCHEMA.MYTABLE_EXPRESSIONS_b0
| |      Materialize: p.PCOMP, p."PCLNT#"
| |      Execute on: All Nodes
| |      Runtime Filters: (SIP1(HashJoin): p.PCOMP), (SIP2(HashJoin): p."PCLNT#"), (SIP3(HashJoin): p.PCOMP, p."PCLNT#")
| +-- Inner -> STORAGE ACCESS for c [Cost: 460, Rows: 4K] (PATH ID: 3)
| |      Projection: MYSCHEMA.MYTABLE2_MANUAL1_node0004
| |      Materialize: c.CCOMP, c.CCLNT, c.CNAME
| |      Execute on: All Nodes

Code: Select all

CREATE PROJECTION MYSCHEMA.MYTABLE_EXPRESSIONS
(
 PCOMP ENCODING COMMONDELTA_COMP,
 "PGUAR#" ENCODING DELTARANGE_COMP,
 PSEQ ENCODING BLOCKDICT_COMP,
 "PCLNT#" ENCODING COMMONDELTA_COMP,
 PDATE ENCODING DELTARANGE_COMP,
 TransactionDate ENCODING DELTARANGE_COMP,
 TransactionDateTime ENCODING DELTARANGE_COMP,
 PTYPE ENCODING BLOCKDICT_COMP,
 POTYPE,
 PAYAMT ENCODING DELTARANGE_COMP,
 PCOMM ENCODING DELTARANGE_COMP,
 PBALAF ENCODING DELTARANGE_COMP,
 PCOLEC,
 PSPLIT,
 PSTATS,
 PCLREF,
 PSTMDT ENCODING DELTARANGE_COMP,
 StatementDate ENCODING DELTARANGE_COMP,
 DeleteOperation ENCODING COMMONDELTA_COMP
)
AS
 SELECT MYTABLE.PCOMP,
        MYTABLE."PGUAR#",
        MYTABLE.PSEQ,
        MYTABLE."PCLNT#",
        MYTABLE.PDATE,
        MYSCHEMA.NumericToDate(PDATE) as TransactionDate,
        MYSCHEMA.NumericToDate(PDATE)::TimeStamp as TransactionDateTime,
        MYTABLE.PTYPE,
        MYTABLE.POTYPE,
        MYTABLE.PAYAMT,
        MYTABLE.PCOMM,
        MYTABLE.PBALAF,
        MYTABLE.PCOLEC,
        MYTABLE.PSPLIT,
        MYTABLE.PSTATS,
        MYTABLE.PCLREF,
        MYTABLE.PSTMDT,
        (CASE
                WHEN pstmdt <> 0 THEN MYSCHEMA.NumericToDate(pstmdt)
                ELSE NULL END) as StatementDate,
        MYTABLE.DeleteOperation
 FROM MYSCHEMA.MYTABLE
 ORDER BY MYTABLE.PCOMP,
          MYTABLE."PGUAR#",
          TransactionDateTime,
          TransactionDate,
          MYTABLE."PCLNT#"
          
SEGMENTED BY hash(MYTABLE.PCOMP, MYTABLE."PGUAR#") ALL NODES KSAFE;

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Projections With Expressions

Post by JimKnicely » Sun Jul 26, 2015 3:55 am

Hi #darinbob,

I do not believe that it is possible to calculate FULL stats on a projection that has an expression. I'm trying to conceptualize how that would be possible? I think this might be tied to the fact that we "cannot run ANALYZE_STATISTICS on an anchor table for a live aggregate projection".

See the doc:

http://my.vertica.com/docs/7.1.x/HTML/C ... ISTICS.htm
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Performance Tuning”