Exporting Directed Queries from the Catalog

Before upgrading to a new version of Vertica, you can export directed queries for those queries whose optimal performance is critical to your system:

1. Use EXPORT_CATALOG with the argument DIRECTED_QUERIES to export from the database catalog all current directed queries and their current activation status:

   => SELECT EXPORT_CATALOG('../../export_directedqueries', 'DIRECTED_QUERIES');
   EXPORT_CATALOG
   -------------------------------------
   Catalog data exported successfully
   				

2. EXPORT_CATALOG creates a script to recreate the directed queries, as in the following example:

   SAVE QUERY SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)) ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name;
   CREATE DIRECTED QUERY CUSTOM findEmployeesCityJobTitle_OPT COMMENT 'Optimizer-generated directed query' OPTVER 'Vertica Analytic Database v11.0.1-20210815' PSDATE '2021-08-20 14:53:42.323963' SELECT /*+verbatim*/  employee_dimension.employee_first_name, employee_dimension.employee_last_name 
   FROM public.employee_dimension employee_dimension/*+projs('public.employee_dimension')*/ 
   WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)) 
   ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name;
   ACTIVATE DIRECTED QUERY findEmployeesCityJobTitle_OPT;

The script that EXPORT_CATALOG creates specifies to recreate all directed queries with CREATE DIRECTED QUERY CUSTOM, regardless of how they were created originally.

3. After the upgrade is complete, remove each directed query from the database catalog with DROP DIRECTED QUERY. Alternatively, edit the export script and insert a DROP DIRECTED QUERY statement before each CREATE DIRECTED QUERY statement. For example, you might modify the script generated earlier with the changes shown in bold:

   SAVE QUERY SELECT employee_dimension.employee_first_name, ... ;
   DROP DIRECTED QUERY findEmployeesCityJobTitle_OPT;
   CREATE DIRECTED QUERY CUSTOM findEmployeesCityJobTitle_OPT COMMENT 'Optimizer-generated ... ;
   ACTIVATE DIRECTED QUERY findEmployeesCityJobTitle_OPT;
   

4. When you run this script, Vertica recreates the directed queries and restores their activation status:

   => \i /home/dbadmin/export_directedqueries
   SAVE QUERY
   DROP DIRECTED QUERY
   CREATE DIRECTED QUERY
   ACTIVATE DIRECTED QUERY