vertica plans, unresolved attributes

Moderator: NorbertKrupa

Post Reply
cilibar
Newbie
Newbie
Posts: 3
Joined: Fri Jun 07, 2013 10:12 pm

vertica plans, unresolved attributes

Post by cilibar » 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)?

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: vertica plans, unresolved attributes

Post by id10t » 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.

cilibar
Newbie
Newbie
Posts: 3
Joined: Fri Jun 07, 2013 10:12 pm

Re: vertica plans, unresolved attributes

Post by cilibar » 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?

Post Reply

Return to “Vertica Administration Tools”