I have a parent table TABLE_A with a, b and c columns and table TABLE_B with a, b and d columns; I have written a function in Postgresql to join these tables, which function query will be faster. NOTE: both a and b columns of the TABLE_A and TABLE_B are indexed, and a and b will be constant values that won't change throughout the function.
QUERY 1:
SELECT * FROM TABLE_A as ta
LEFT JOIN TABLE_B as tb ON tb.a = ta.a and tb.b = tb.ta.b
WHERE
ta.a = $1 and
ta.b = $2
QUERY 2:
SELECT * FROM TABLE_A as ta
LEFT JOIN TABLE_B as tb ON tb.a = $1 and tb.b = $2
WHERE
ta.a = $1 and
ta.b = $2
NOTE: $1 and $2 are the function's first and second parameters, respectively.
You typically want to compare the execution plans to see the difference in the two statements. You will use EXPLAIN to this purpose:
PREPARE stmt1(int, int) AS
SELECT * FROM TABLE_A as ta
LEFT JOIN TABLE_B as tb ON tb.a = ta.a and tb.b = ta.b
WHERE
ta.a = $1 and
ta.b = $2;
EXPLAIN EXECUTE stmt1(42, 42);
which results in
QUERY PLAN |
------------------------------------------------------------------------------+
Nested Loop Left Join (cost=0.58..16.64 rows=1 width=16) |
-> Index Scan using tab_a_b on table_a ta (cost=0.29..8.31 rows=1 width=8)|
Index Cond: (b = 42) |
Filter: (a = 42) |
-> Index Scan using tab_b_b on table_b tb (cost=0.29..8.31 rows=1 width=8)|
Index Cond: (b = 42) |
Filter: (a = 42) |
If you repeat this with the second statements (omitted here for brevity) you'll see the identical result, which provides the answer that the two statements will behave identical.
It is no surprise, such trivial transformation of the predicates are performed by the RDBMs automatically:
ta.a = $1 and ta.a = tb.a => tb.a = $1