If a table has a super projection sorted on primary key, will an additional projection for primary key constraint help with query performance?
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:
-- 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 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...
@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?
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.
@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!