Tips & Tricks

Ignore Projections During Optimization

2

I just learned about an undocumented function which allows you to tell the optimizer to ignore certain projections. Ignoring projections can be useful for testing or when optimizing queries. This post will show how to use the set_optimizer_directives() function to accomplish this.

We’ll begin by setting up a test table and sample data:

Let’s verify that super projections were created for the table:

We create an alternate projection for the table:

We can see that new projections have been created for the table:

Let’s see what the initial EXPLAIN looks like:

The optimizer chose to use the projection public.tar_b0.

Using the SET_OPTIMIZER_DIRECTIVES function, we ignore the projections using AvoidUsingProjections followed by a comma separated list of projections. The function does not accept projection ID’s or the base name of the projection; you must use the full projection name:

We receive a confirmation that the directive has been set:

Running the EXPLAIN again, we can see that the optimizer is using the projection public.tar_p_node0002 and has ignored the projections we set:

To roll this change back, just clear the projections in the directive:

As Sharon Cutter points out, you should use caution when using this function in a production environment. Further, the function is more suited towards testing individual projections and not projection sets.

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user’s employer.

2 Comments

  1. sra1 February 8, 2016 at 7:30 AM -  Reply

    This is not working for me even after skipping the super projection.

    • Norbert Krupa February 8, 2016 at 11:14 AM -  Reply

      Can you please explain what “not working” means? Give specific examples. What version are you running?

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: