Projection questions sort order

[verticadba]

How to create a projection in vertica where I can have different sort order column for a table

Say I have a table employees with 4 column(FN,LN,SS,SAL) and I have three nodes

Can I store the different sort order on different nodes: Node1 should have (FN,LN), Node2 should have (SS) and Node3 should have (SAL)

Thanks.

[JimKnicely]

Can I store the different sort order on different nodes

Yup!

Example:

Code: Select all

dbadmin=> create table t (fn varchar(100), ln varchAR(100), ss VARCHAR(100), sal money);
CREATE TABLE
dbadmin=> create projection pr1 as select * from t order by fn, ln unsegmented node v_verticadb_node0001;
CREATE PROJECTION
dbadmin=> create projection pr2 as select * from t order by ss unsegmented node v_verticadb_node0002;
CREATE PROJECTION
dbadmin=> create projection pr3 as select * from t order by sal unsegmented node v_verticadb_node0003;
CREATE PROJECTION

Code: Select all

dbadmin=> select pr.node_name,
dbadmin->        pc.projection_name,
dbadmin->        pc.projection_column_name,
dbadmin->        pc.column_position,
dbadmin->        pc.sort_position
dbadmin->   from projections pr
dbadmin->   join projection_columns pc
dbadmin->     on pc.projection_name = pr.projection_name
dbadmin->  where pr.projection_name in ('pr1', 'pr2', 'pr3')
dbadmin->  order by 1, 2, 4;
      node_name      | projection_name | projection_column_name | column_position | sort_position
---------------------+-----------------+------------------------+-----------------+---------------
 v_snowfall_node0001 | pr1             | fn                     |               0 |             0
 v_snowfall_node0001 | pr1             | ln                     |               1 |             1
 v_snowfall_node0001 | pr1             | ss                     |               2 |
 v_snowfall_node0001 | pr1             | sal                    |               3 |
 v_snowfall_node0002 | pr2             | fn                     |               0 |
 v_snowfall_node0002 | pr2             | ln                     |               1 |
 v_snowfall_node0002 | pr2             | ss                     |               2 |             0
 v_snowfall_node0002 | pr2             | sal                    |               3 |
 v_snowfall_node0003 | pr3             | fn                     |               0 |
 v_snowfall_node0003 | pr3             | ln                     |               1 |
 v_snowfall_node0003 | pr3             | ss                     |               2 |
 v_snowfall_node0003 | pr3             | sal                    |               3 |             0
(12 rows)

[verticadba]

This is awesome, thank you

[pborne]

Just because you can do it doesn't mean you should!

Every query can use only one projection per table for the entire query. This means that once the optimizer has determined which one it will use, the other ones become useless! This means that your query will mostly run on one node only if you don't distribute your data across the nodes or if you don't replicate the projection on all nodes.

Performance will not be what it should and you will see one node working like crazy and the other two doing next to nothing. This defeats the whole idea of using a cluster in the first place.