Select by date range and projections for best performance
I have a typical query which joins two tables and selects rows in a date range
CREATE TABLE operation(pk1 int, pk2 int, date date, somedata int, PRIMARY KEY(pk1, pk2));
CREATE TABLE additional (pk1 int, pk2 int, somedata int, PRIMARY KEY (pk1,pk2));
FROM operation AS op
JOIN additional AS ad
ON op.pk1 = ad.pk1
AND op.pk2 =ad.pk2
WHERE op.date BETWEEN 'xxxx-xx-xx' AND 'yyyy-yy-yy'
So there is a need to have optimized JOIN by primary key (pk1, pk2) and selection by date field.
What projections should I create to achive the best performance in such scenario?
Can Vertica use more than one projection on table in that query? What is a common practice?