Joining large tables (dataframe and sql), but only want few columns: select before or after join?

[#1]

Concerning memory usage and efficiency, when joining two large tables with many columns but only want a few columns from each of them, is it better to select() before or after the join()? My instinct tells me to select() before the join but some other voices would be very helpful

[#2 · Accepted solution]

Yes. Reducing size of dataset before JOIN would surely help rather than other way round.

[#3]

Does this also hold for other methods besides JOIN? E.g., I want to do a groupBy. Should I select() before the groupBy()?

[#4]

Yes. You can think of select() as the "filter" of columns where filter() filters rows. You want to reduce the impact of the shuffle as much as possible. Perform both of these as soon as possible. The groupBy() is going to cause a shuffle by key (most likely). Be careful with the groupBy(). If you can accomplish what you need to do with a reduceBy(), you should use that instead.

If you mean dataframe instead of dataset, SparkSQL will handle much of this optimization for you. But if using normal RDDs, you are going to have to deal with these types of optimizations on your own.

[#5]

Yes. A projection before any sort of transformation/action would help in computation time and storage optimization.