Currently we're able to derive X(C,B) from X(A,B) and A=C, however this works only for X = ComparativeBoolNode and does not work for IN <list> predicates.

Imagine a join like this:

select *
from A join B on A.ID = B.ID
where A.ID in (1, 2)
order by A.ID desc;

that can be loop-joined as either A->B or B->A. However, the IN predicate can currently use an index only for the A->B case, and if the optimizer chooses the opposite direction the performance drops down.

where A.ID = 1:
Fetches = 9, indexed reads from either A or B = 1

where A.ID = 1 OR A.ID = 2:
Fetches = 16, indexed reads from either A or B = 2

where A.ID IN (1, 2):
Fetches = 438587, indexed reads from either A or B = 72515

The plan in the last case reports "Index Full Scan" instead of the expected "Index List Scan".

After the fix:

where A.ID IN (1, 2):
Fetches = 18, indexed reads from either A or B = 2