Super Projections vs Query Specific Projections

AcharHere

Hi guys,

I have one doubt.. if we create a query specific projection which has all columns in the base table and has same syntax of its super projection. By default which projection, vertica optimizer chooses for execution.
I mean there is no difference in Projection SQL between Super and Specific projection.
I know we can avoid super projection using SET_OPTIMIZER_DIRECTIVES('AvoidUsingProjections=').

please let me know the working of the optimizer in such case.

Thanks,
Achar

Jim_Knicely

Hi,

If the SELECT part of a projections has all of the column in a table, it is considered a super projection. A query specific projection will typically have an ORDER BY clause that includes the source query's WHERE clause predicates and JOIN columns.

If the projections are exactly the same, Vertica will chose the one create first.

Example:

dbadmin=> select export_objects('','which_one');
                                                                                                                                                                                                                                  export_objects                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.which_one
(
    c int
);

CREATE PROJECTION public.which_one_super /*+basename(which_one),createtype(P)*/
(
 c
)
AS
 SELECT which_one.c
 FROM public.which_one
 ORDER BY which_one.c
SEGMENTED BY hash(which_one.c) ALL NODES OFFSET 0;

CREATE PROJECTION public.which_one_query_specific
(
 c
)
AS
 SELECT which_one.c
 FROM public.which_one
 ORDER BY which_one.c
SEGMENTED BY hash(which_one.c) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(0);
(1 row)

dbadmin=> select projection_name, is_super_projection from projections where anchor_table_name = 'which_one';
     projection_name      | is_super_projection
--------------------------+---------------------
 which_one_super          | t
 which_one_query_specific | t
(2 rows)

dbadmin=> explain select * from which_one;
                                                                                            QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain select * from which_one;

 Access Path:
 +-STORAGE ACCESS for which_one [Cost: 1, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.which_one_super
 |  Materialize: which_one.c
 ------------------------------

dbadmin=> drop projection public.which_one_super;
DROP PROJECTION

dbadmin=> CREATE PROJECTION public.which_one_super /*+basename(which_one),createtype(P)*/
dbadmin-> (
dbadmin(>  c
dbadmin(> )
dbadmin-> AS
dbadmin->  SELECT which_one.c
dbadmin->  FROM public.which_one
dbadmin->  ORDER BY which_one.c
dbadmin-> SEGMENTED BY hash(which_one.c) ALL NODES OFFSET 0;
WARNING 4468:  Projection <public.which_one_super> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

dbadmin=> explain select * from which_one;
                                                                                            QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain select * from which_one;

 Access Path:
 +-STORAGE ACCESS for which_one [Cost: 17, Rows: 1] (PATH ID: 1)
 |  Projection: public.which_one_query_specific
 |  Materialize: which_one.c
 ------------------------------