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
Projections With Expressions
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Projections With Expressions
Could you possibly provide an example of what you mean by expression, and the SQL / EXPLAIN plan?
Checkout vertica.tips for more Vertica resources.
Re: Projections With Expressions
http://my.vertica.com/docs/7.1.x/HTML/i ... ssions.htm
http://my.vertica.com/docs/7.1.x/HTML/i ... ctions.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;
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Projections With Expressions
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
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.