Additional projection for primary key constraint

sknairsknair Vertica Customer

If a table has a super projection sorted on primary key, will an additional projection for primary key constraint help with query performance?

Best Answer

  • Options
    marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    I suppose you created the table like I did below here, or in a similar way - that is, specifying the primary key at CREATE time:
    The default projection that comes out of that is sorted by and segmented by the hash of the primary key.

    CREATE TABLE foo (
      foo_id    INT NOT NULL
    ,  foo_name VARCHAR(32)
    , CONSTRAINT pk_foo PRIMARY KEY(foo_id)
    -- Insert a row to force the creation of a projection
    -- let Vertica show what it created:
    -- out 
    -- out CREATE TABLE dbadmin.foo
    -- out (
    -- out     foo_id int NOT NULL,
    -- out     foo_name varchar(32),
    -- out     CONSTRAINT pk_foo PRIMARY KEY (foo_id) DISABLED
    -- out );
    -- out 
    -- out 
    -- out CREATE PROJECTION dbadmin.foo_super /*+basename(foo),createtype(L)*/ 
    -- out (
    -- out  foo_id,
    -- out  foo_name
    -- out )
    -- out AS
    -- out  SELECT foo.foo_id,
    -- out         foo.foo_name
    -- out  FROM dbadmin.foo
    -- out  ORDER BY foo.foo_id
    -- out SEGMENTED BY hash(foo.foo_id) ALL NODES OFFSET 0;

    So the projection you seem to intend to build would be a complete waste of time and resources...


  • Options
    SankarmnSankarmn Community Edition User ✭✭

    @sknair, additional projections may help and depends on time to load data and the node count. are you following the best practices for projection optimization?

  • Options
    sknairsknair Vertica Customer

    Can you please point me to the documentation on best practices?
    We load data only on weekends. So load time is not the primary concern.
    But we are having performance issues with queries on some high volume tables.

  • Options
    SankarmnSankarmn Community Edition User ✭✭

    @sknair, check... https://www.vertica.com/kb/Projection-Optimization/Content/BestPractices/Projection-Optimization.htm
    When you say high volume tables, I think these can be best achieved by data distribution with right joins following the best practices.
    Hope this Helps!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file