Additional projection for primary key constraint

sknair

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

marcothesane

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
INSERT INTO FOO VALUES(1,'Arthur');COMMIT;
-- let Vertica show what it created:
SELECT EXPORT_OBJECTS('','foo',FALSE);
-- 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...

Sankarmn

@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?

sknair

Sankar,
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.

Sankarmn

@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!