Super Projections vs Query Specific Projections

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
 ------------------------------