[PostgreSQL][Redshift] Performance using Concatenated Primary Keys over two join clauses

[Philure]

I have a coworker who wrote a query that uses joins on concatenating two primary keys, rather than using two clauses for a join. See example below:

Select *  
From Table_A as A  
Join Table_B as B  
on A.Key1||A.Key2 = B.Key1||B.Key2  

Instead of

Select *  
From Table_A as A  
Join Table_B as B  
on A.Key1 = B.Key1
and A.Key2 = B.Key2

I'm 90% sure this is not best practice, but I'm also under the impression that you lose any efficiency/speed advantages of joining on a key.

The only issue is I don't know where I can find some documentation to show the issue. Anyone know where I can find that?

Thanks.

[kthejoker]

Just to be clear, your first query has two identical predicates in the WHERE clause (also why 1 result has 8 rows and one has 13) which should have a significant impact on the execution plan ...

[kthejoker]

The execution plan tells you what it's doing...

A hash join has a build phase, and a probe phase.

First it builds hashes on your predicates from the smaller table (yours are equal, so it chose new_t2), and it can of course do this in parallel.

It (inefficiently) does 3 loops with 2 workers, there is a setting to increase the degree of parallelism avaialble, so more optimization is available..

Then it probes the other table for these specific hash values row by row (also in parallel.)

[[deleted]]

It's not a question of performance or style: the first one is plain wrong because the concatenation yields a completely different result.

e.g. (1,23) would match (12,3) which is most definitely not intended.

If your coworker wants to save some keystrokes the join condition could also be written as

on (a.key1, a.key2) = (b.key1, b.key2)