which projection is the primary one

Hi Vincent,

Usually as per the naming conventions in Vertica.
A primary projection in your case is called a super projection and another buddy projection is nothing but the buddy projection for this super projection ( in other words this also refers to a super projections but on a offset node). So b0 is super projection and b1 is buddy projection for its super projections

Lets understand this with your example:

I created a table NAVIN.TEST_PROJ

nnani=> create table navin.test_proj as  select * from all_tables;
WARNING 4486:  Projections are always created and persisted in the default Vertica locale. The current locale is LEN_S1
CREATE TABLE

Table created, now lets see what projections it made and its details

nnani=> select node_name,projection_schema, anchor_Table_name, projection_name, is_super_projection, is_segmented from projections where projection_schema='navin' and anchor_Table_name like '%proj%'; 
node_name | projection_schema | anchor_Table_name | projection_name | is_super_projection | is_segmented
-----------+-------------------+-------------------+-----------------+---------------------+--------------
           | navin             | test_proj         | test_proj_b0    | t                   | t
           | navin             | test_proj         | test_proj_b1    | t                   | t
(2 rows)
Time: First fetch (2 rows): 506.502 ms. All rows formatted: 506.572 ms

Above it can be seen that both are super projections and they are segmented across nodes. thats why we con't see any specific node_name.

Now coming to your question, which is primary (super projection) and which is buddy projection.

A simple way to understand this is seeing their ddl:

DDL for test_proj_b0    

nnani=> select export_objects('','navin.test_proj_b0');                                                                                                                                                                                                                                                                                                                        export_objects                                                                                                                                                                                                                                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE PROJECTION navin.test_proj_b0 /*+basename(test_proj),createtype(A)*/
(
 schema_name,
 table_id,
 table_name,
 table_type,
 remarks
)
AS
 SELECT test_proj.schema_name,
        test_proj.table_id,
        test_proj.table_name,
        test_proj.table_type,
        test_proj.remarks
 FROM navin.test_proj
 ORDER BY test_proj.schema_name,
          test_proj.table_id,
          test_proj.table_name,
          test_proj.table_type,
          test_proj.remarks
SEGMENTED BY hash(test_proj.table_id, test_proj.table_type, test_proj.schema_name, test_proj.table_name, test_proj.remarks) ALL NODES ;

DDL for test_proj_b1

nnani=> select export_objects('','navin.test_proj_b1');                                                                                                                                                                                                                                                                                                                            export_objects                                                                                                                                                                                                                                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE PROJECTION navin.test_proj_b1 /*+basename(test_proj),createtype(A)*/
(
 schema_name,
 table_id,
 table_name,
 table_type,
 remarks
)
AS
 SELECT test_proj.schema_name,
        test_proj.table_id,
        test_proj.table_name,
        test_proj.table_type,
        test_proj.remarks
 FROM navin.test_proj
 ORDER BY test_proj.schema_name,
          test_proj.table_id,
          test_proj.table_name,
          test_proj.table_type,
          test_proj.remarks
SEGMENTED BY hash(test_proj.table_id, test_proj.table_type, test_proj.schema_name, test_proj.table_name, test_proj.remarks) ALL NODES OFFSET 1;

As we see clearly above, the ddl is different at the end for both projections
b0 has segmentation on all nodes
b1 has segmentation on all nodes offset 1
This means b1 is buddy projection for b0 projection.

For more information on Vertica naming conventions with Projections , you can refer here
How does Vertica name projections


Hope this helps
NC

Thanks Navin for the detailed reply!

If I may add some complicating information:  There is a projection that Vertica will tend to pick more often.  (They're identical and we have to pick one; it just happens that we do so predictably.)  But there really is no absolute notion of a "primary" projection in Vertica.  The two are buddies of each other; they are interchangeable.

For example, there are cases (often to do with multiple-nodes-down scenarios) where we will actually use pieces of both projections simultaneously, potentially in a somewhat-complicated way, to answer queries.  Recent Vertica versions have gained some additional cleverness (and complexity) here.

Also, if you manually create projections, it is possible to have two projections that are buddies but that have different sort orders.  We don't typically recommend this because it slows recovery dramatically -- we can't just copy ROS containers from the recovering node's buddy; we have to re-sort the data.  But you can do it.  In this case, Vertica's optimizer will choose one buddy or the other based on which is optimal for the current query.

The output of EXPLAIN for a particular query should give you more information about which projections that query is using.

Thanks for adding this Adam,

Adding to Adam's notes for EXPLAIN plan.

You can even use this table to check which query uses what projection.
PROJECTION_USAGE

Hope this helps
NC

Thank you so much Navin and Adam.