Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Select by date range and projections for best performance

edited December 2018 in General Discussion

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?

Answers

  • 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.

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.