Re: How to avoid multiple table scan with "NOT IN"

[Tom Lane <tgl@xxxxxxxxxxxxx>]

Nick <nboutelier@xxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general