How to avoid multiple table scan with "NOT IN"

Nick <nboutelier@gmail.com> writes:

Is there any way this query could be written that doesnt scan the
subquery table twice?

SELECT * FROM my_table
WHERE (one,two) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)
AND (two,one) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)

The first kluge that comes to mind is

SELECT * FROM my_table
WHERE
(least(one,two), greatest(one,two))
NOT IN (SELECT least(sub_one,sub_two), greatest(sub_one,sub_two)
FROM my_sub_table);

assuming that both the columns are of the same sortable datatype.
There are probably other ways.

regards, tom lane