Identifying Active Directed Queries

Multiple directed queries can map to the same input query. The is_active column returned by GET DIRECTED QUERY clarifies which directed queries are active. If multiple directed queries are active for the same input query, the optimizer uses the first one to be created. In that case, you can use EXPLAIN to identify which directed query is active.

It is good practice to activate only one directed query at a time for a given input query.

In the following example, GET DIRECTED QUERY returns with two directed queries that map to the same input query: findBostonCashiers_OPT, and findBostonCashiers_CUSTOM. Only findBostonCashiers_CUSTOM is flagged as active:

=> GET DIRECTED QUERY SELECT Employee_first_name, Employee_last_name FROM Emp_Dimension 
   WHERE Employee_city='Boston' and Employee_position='Cashier';
-[ RECORD 1 ]---+
query_name      | findBostonCashiers_OPT
is_active       | f
vertica_version | Vertica Analytic Database v7.2.0
comment         | Optimizer-generated directed query
creation_date   | 2015-09-02 09:36:29.395702
annotated_query | SELECT /*+ verbatim */ Emp_Dimension.Employee_first_name AS Employee_first_name, Emp_Dimension.Employee_last_name AS Employee_last_name
FROM public.Emp_Dimension AS Emp_Dimension/*+projs('public.Emp_Dimension')*/
WHERE (Emp_Dimension.Employee_city = 'Boston'::varchar(6)) AND (Emp_Dimension.Employee_position = 'Cashier'::varchar(7))
-[ RECORD 2 ]---+
query_name      | findBostonCashiers_CUSTOM
is_active       | t
vertica_version | Vertica Analytic Database v7.2.0-20150902
comment         | Custom directed query
creation_date   | 2015-09-02 13:27:38.225568
annotated_query | SELECT Emp_Dimension.Employee_first_name, Emp_Dimension.Employee_last_name FROM public.Emp_Dimension/*+Projs('public.Emp_Dimension_Unseg')*/ WHERE ((Emp_Dimension.Employee_city = 'Boston'::varchar(6)) AND (Emp_Dimension.Employee_position = 'Cashier'::varchar(7)))

If you run EXPLAIN on the same input query, it returns with a query plan that confirms use of findBostonCashiers_CUSTOM as the active directed query:

=> EXPLAIN SELECT Employee_first_name, Employee_last_name FROM Emp_Dimension WHERE Employee_city='Boston' AND Employee_position='Cashier';
QUERY PLAN 
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT Employee_first_name, Employee_last_name FROM Emp_Dimension WHERE Employee_city='Boston' AND Employee_position='Cashier';

The following active directed query(query name: findBostonCashiers_CUSTOM) is being executed:
SELECT Emp_Dimension.Employee_first_name, Emp_Dimension.Employee_last_name FROM public.Emp_Dimension/*+Projs('public.Emp_Dimension_Unseg')*/ WHERE ((Emp_Dimension.Employee_city = 'Boston'::varchar(6)) AND (Emp_Dimension.Employee_position = 'Cashier'::varchar(7)))

Access Path:
+-STORAGE ACCESS for Emp_Dimension [Cost: 154, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Projection: public.Emp_Dimension_Unseg
|  Materialize: Emp_Dimension.Employee_first_name, Emp_Dimension.Employee_last_name
|  Filter: (Emp_Dimension.Employee_city = 'Boston')
|  Filter: (Emp_Dimension.Employee_position = 'Cashier')
|  Execute on: Query Initiator