Select by date range and projections for best performance-
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))
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?