Using optimizer-generated and custom directed queries together

You can use the annotated SQL that the optimizer creates as the basis for creating your own custom directed queries. This approach can be especially useful in evaluating the plan that the optimizer creates to handle a given query, and testing plan modifications.

For example, you might want to modify how the optimizer implements the following query:

=> SELECT COUNT(customer_name) Total, customer_region Region
    FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
    JOIN product_dimension p ON s.product_key = p.product_key
    WHERE p.category_description ilike '%Medical%'
      AND p.product_description ilike '%antibiotics%'
      AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
    GROUP BY customer_region;

When you run EXPLAIN on this query, you discover that the optimizer uses projection customers_proj_age for the customer_dimension table. This projection is sorted on column customer_age. Consequently, the optimizer hash-joins the tables store_sales and customer_dimension on customer_key.

After analyzing customer_dimension table data, you observe that most customers are under 30, so it makes more sense to use projection customer_proj_id for the customer_dimension table, which is sorted on customer_key:

You can create a directed query that encapsulates this change as follows:

1. Obtain optimizer-generated annotations on the query with EXPLAIN ANNOTATED:

   => \o annotatedQuery
   => EXPLAIN ANNOTATED SELECT COUNT(customer_name) Total, customer_region Region
        FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
        JOIN product_dimension p ON s.product_key = p.product_key
        WHERE p.category_description ilike '%Medical%'
          AND p.product_description ilike '%antibiotics%'
          AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
        GROUP BY customer_region;
   => \o
   => \! cat annotatedQuery
   ...
   SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
    FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
       JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customers_proj_age')*/
         ON (c.customer_key = s.customer_key))
       JOIN /*+Distrib(L,B),JType(M)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
         ON (s.product_key = p.product_key))
    WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
        AND (c.customer_age <= 30)
        AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
        AND (p.product_description ~~* '%antibiotics%'::varchar(13))
    GROUP BY  /*+GByType(Hash)*/ 2
   (4 rows)
   

2. Modify the annotated query:

   
   SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
    FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
       JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customer_proj_id')*/
         ON (c.customer_key = s.customer_key))
       JOIN /*+Distrib(L,B),JType(H)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
         ON (s.product_key = p.product_key))
    WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
        AND (c.customer_age <= 30)
        AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
        AND (p.product_description ~~* '%antibiotics%'::varchar(13))
    GROUP BY  /*+GByType(Hash)*/ 2
   

3. Use the modified annotated query to create the desired directed query:

   • Save the desired input query with SAVE QUERY:

     
     => SAVE QUERY SELECT COUNT(customer_name) Total, customer_region Region
         FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
         JOIN product_dimension p ON s.product_key = p.product_key
         WHERE p.category_description ilike '%Medical%'
           AND p.product_description ilike '%antibiotics%'
           AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
         GROUP BY customer_region;
     

   • Create a custom directed query that associates the saved input query with the modified annotated query:

     
     => CREATE DIRECTED QUERY CUSTOM 'getCustomersUnder31'
        SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
      FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
         JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customer_proj_id')*/
           ON (c.customer_key = s.customer_key))
         JOIN /*+Distrib(L,B),JType(H)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
           ON (s.product_key = p.product_key))
      WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
          AND (c.customer_age <= 30)
          AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
          AND (p.product_description ~~* '%antibiotics%'::varchar(13))
      GROUP BY  /*+GByType(Hash)*/ 2;
     CREATE DIRECTED QUERY
     

4. Activate this directed query:

   => ACTIVATE DIRECTED QUERY getCustomersUnder31;
   ACTIVATE DIRECTED QUERY
   

When the optimizer processes a query that matches this directed query's input query, it uses the directed query's annotated query to generate a query plan:

=> EXPLAIN SELECT COUNT(customer_name) Total, customer_region Region
     FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
     JOIN product_dimension p ON s.product_key = p.product_key
     WHERE p.category_description ilike '%Medical%'
       AND p.product_description ilike '%antibiotics%'
       AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
     GROUP BY customer_region;

 The following active directed query(query name: getCustomersUnder31) is being executed:
...