Select by date range and projections for best performance

Alex

Hi,
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));

SELECT
op.pk1
, op.pk2
, op.date
, op.somedata
, ad.somedata
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?

Isabelle_Vela

I don't know if you had an answer in a different thread. I would first try an incremental design with the query to optimize, and see what the design proposes as projection.

qinchaofeng

I suggest you design the projection by using pk1&pk2 as the segment column,and using date as order column，please have a try。