Select by date range and projections for best performance

AlexAlex Registered User
edited December 2018 in Vertica Forum

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?

Leave a Comment

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