I have a relatively large Redshift cluster which I am attempting to optimize via the selection of sort and dist keys. I have a recurring situation where multiple tables are joined together via an intermediary join. The join is relatively stable among all queries. I know that you're supposed to distribute on the field that you join to and sort on the conditions in the where clause. However, in this situation there is no where clause and it is joined twice. For example I have three tables:
users
-> accounts
-> subscriptions
. These are in a 1:1:1 relationship. The joins are as follows:
users.id
= accounts.user_id
accounts.id
= subscriptions.accounts_id
.
What is the optimal way to set the sort and dist keys on the accounts
table in this situation?