The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Select by date range and projections for best performance-

Hi,
I'm doing typical join of two long tables by composite primary keys and filtering a range by non-key date field.

CREATE TABLE operations (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 operations AS op
JOIN additional AS ad
ON op.pk1 = ad.pk1
AND op.pk2 = ad.pk2
WHERE op.date BETWEEN '...' AND '...'

There are two points to optimize:

  • join by primary key and
  • filtering by date.

What projections I should create to achieve the best performance in this case?

Answers

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.