Auto-Projection Column Sort Order

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert admin
edited July 5 in Vertica Tips

Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. Vertica uses the current value of the MaxAutoSortColumns configuration parameter to determine which table columns are included in the auto-projection sort order.

Example:

dbadmin=> SELECT default_value, current_value, description
dbadmin->   FROM configuration_parameters
dbadmin->  WHERE parameter_name = 'MaxAutoSortColumns';
default_value | current_value |                                       description
---------------+---------------+-----------------------------------------------------------------------------------------
8             | 8             | Max number of columns used in auto projection sort expression (0 is to use all columns)
(1 row)

dbadmin=> CREATE TABLE test_sort_order (
dbadmin(> c1 INT,
dbadmin(> c2 INT,
dbadmin(> c3 INT,
dbadmin(> c4 INT,
dbadmin(> c5 INT,
dbadmin(> c6 INT,
dbadmin(> c7 INT,
dbadmin(> c8 INT,
dbadmin(> c9 INT,
dbadmin(> c10 INT);
CREATE TABLE

dbadmin=> INSERT INTO test_sort_order (c1) SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT DISTINCT projection_column_name, sort_position
dbadmin->   FROM projection_columns
dbadmin->  WHERE table_name = 'test_sort_order'
dbadmin->    AND sort_position IS NOT NULL
dbadmin->  ORDER BY sort_position;
projection_column_name | sort_position
------------------------+---------------
c1                     |             0
c2                     |             1
c3                     |             2
c4                     |             3
c5                     |             4
c6                     |             5
c7                     |             6
c8                     |             7
(8 rows)

dbadmin=> ALTER DATABASE test_db SET MaxAutoSortColumns = 4;
ALTER DATABASE

dbadmin=> DROP TABLE test_sort_order;
DROP TABLE

dbadmin=> CREATE TABLE test_sort_order (
dbadmin(> c1 INT,
dbadmin(> c2 INT,
dbadmin(> c3 INT,
dbadmin(> c4 INT,
dbadmin(> c5 INT,
dbadmin(> c6 INT,
dbadmin(> c7 INT,
dbadmin(> c8 INT,
dbadmin(> c9 INT,
dbadmin(> c10 INT);
CREATE TABLE

dbadmin=> INSERT INTO test_sort_order (c1) SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT DISTINCT projection_column_name, sort_position
dbadmin->   FROM projection_columns
dbadmin->  WHERE table_name = 'test_sort_order'
dbadmin->    AND sort_position IS NOT NULL
dbadmin->  ORDER BY sort_position;
projection_column_name | sort_position
------------------------+---------------
c1                     |             0
c2                     |             1
c3                     |             2
c4                     |             3
(4 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm

Have fun!

Sign In or Register to comment.