Auto-Projection Column Sort Order

Posted July 8, 2019 by Jim Knicely, Vertica Principal Solution Architect

Red and blue square dots fly from scattered into two neat rows arranged by color.
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'; FROM configuration_parameters 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/latest/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm

Have fun!