@Amir - Both indexes and partitions help with joining tables. If you don't have a distributed system like a single MySQL or PostgreSQL instance, but still have a lot of data, indexes might get too big - and maintaining them can become a pain. So, you can think about partitioning the tables, i.e., when a heavy query comes in, it will first prune the partitions and then use the index to reach the data that you actually need.

In distributed processing systems like Spark, partitions have different connotations. The internals work differently. Having said that, the end goal of partitioning there too is pruning the ones that you don't need. What you mentioned about loading tables in parallel applies here.