>> JOIN a AS table_b
>> ON table_a.id = table_b.id
>> AND table_a.key = table_b.key
> Anyways, to use an index for that join, you'd need a composite index on id *AND* key, not two separate indexes.
Its not as much as for using the index, but to be able to push the where clause inside both JOINED tables.
The = operator already does this. It gives the planner the option to join the table in using either id = id or key = key. It can deduce that if I have a WHERE condition with table_a.key = 'Something', then table_b.key must also be 'Something'. It can then decide to filter table_b on key. When using IS NOT DISTINCT FROM, the planner is not considering the same options. Now its like it doesn't know table_a.key is same the same as table_b.key.
I would somehow expect the IS NOT DISTINCT FROM operator to do the same. As it establish the same rules.. If a = 'test' and a IS NOT DISTINCT FROM b then b = 'test' also
-
Kim Carlsen