The Vertica Database Forums

Posted: **Mon Jun 10, 2013 7:58 pm**

I was looking at some vertica plans produced with EXPLAIN command and it seems that for some expressions instead of real attribute names the plan prints <SVAR> . This happens even if I have different attributes (for all of them it prints <SVAR>).
Here is the example for two aggregates over different attributes:
| Aggregates: max(<SVAR>), max(<SVAR>)

Is there some reason for this? How can I resolve them deterministically? Is there a way to prevent this (adding some hint to the query or something similar)?

Posted: **Mon Jun 10, 2013 8:38 pm**

Hi!

>> Is there a way to prevent this?
I don't think, it's decision of query optimizer and I don't see any reason to prevent it.

SVAR - means substituted variable.
Example:

Code: Select all

 explain select p1, avg(p2) as average from test group by p1 order by average;
 
 Access Path:
 +-SORT [Cost: 2K, Rows: 100] (PATH ID: 1)
 |  Order: (<SVAR> / float8(<SVAR>)) ASC
 | +---> GROUPBY PIPELINED [Cost: 2K, Rows: 100] (PATH ID: 2)
 | |      Aggregates: sum_float(test.p2), count(test.p2)
 | |      Group By: test.p1
 | | +---> STORAGE ACCESS for test [Cost: 2K, Rows: 40M (10K RLE)] (PATH ID: 3)
 | | |      Projection: public.test_DBD_4_rep_dbd_node0001
 | | |      Materialize: test.p1, test.p2

As you can see Vertica substitutes calculation of AVG by SUM / COUNT.

Posted: **Mon Jun 10, 2013 10:04 pm**

I understand that this is the variable substitution but I just wanted to see why and when the optimizer does it. It seems nondeterministic.
Can I find somewhere the complete specification of plans that vertica produces?

The Vertica Database Forums

vertica plans, unresolved attributes

vertica plans, unresolved attributes

Re: vertica plans, unresolved attributes

Re: vertica plans, unresolved attributes