How to ignore or avoid a projection to be used in Vertica

Recently i came across a nice feature called "AvoidUsingProjections" that can be used in Vertica, it is actually an option that can be used with the set_optimizer_directives Vertica function. What is the point of using this ? Well maybe Vertica doesn't choose the best projection for you and you decide to tell Vertica what projection to use(you force the use of a specific projection) Basic Syntax: 

  SELECT set_optimizer_directives('AvoidUsingProjections=proj1,proj2,...');
 Here is an example of you can use it:  For this example i will use the table dc_proj_used that has 2 projection as seen below: 
(dbadmin@:5433) [dbadmin]  select projection_name from projections where anchor_table_name='dc_proj_used';
projection_name
-----------------
dc_proj_used_b0
dc_proj_used_b1

Now let's do a select on the table dc_proj_used and see what projection is using  
dbadmin@:5433) [dbadmin]  explain select * from dc_proj_used;

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

explain select * from dc_proj_used;

Access Path:
+-STORAGE ACCESS for dc_proj_used [Cost: 547, Rows: 2K] (PATH ID: 1)
| Projection: test.dc_proj_used_b0
  • we can see that it's using the  dc_proj_used_b0 to resolv our query.
Now let's go and tell vertica to stop using this projection:  
SELECT set_optimizer_directives('AvoidUsingProjections=test.dc_proj_used_b0');
Run the select with explain to see the projection that is using now 
QUERY PLAN DESCRIPTION:
------------------------------

Optimizer Directives
----------------------
AvoidUsingProjections=test.dc_proj_used_b0
explain select * from dc_proj_used;

Access Path:
+-STORAGE ACCESS for dc_proj_used [Cost: 547, Rows: 2K] (PATH ID: 1)
| Projection: test.dc_proj_used_b1
  • see that is listening to the directives we just set.
What if we tell it to avoid using both of the projections ?  
  • first we need to clear the directive
SELECT set_optimizer_directives('AvoidUsingProjections=')
Tell the optimizer to avoid both projections: 
SELECT set_optimizer_directives('AvoidUsingProjections=test.dc_proj_used_b0,test.dc_proj_used_b1');
Run the query now  
(dbadmin@:5433) [dbadmin]  explain select * from dc_proj_used;
ERROR 3586: Insufficient projections to answer query
DETAIL: No projections eligible to answer query
HINT: Projection dc_proj_used_b1 not used in the plan because the projection is not allowed because of current optimizer directives.
Projection dc_proj_used_b0 not used in the plan because the projection is not allowed because of current optimizer directives.
Use this option with care and on your own risk.

Copyright 2020. All Rights Reserved