How to avoid multiple table scan with "NOT IN"

From: Nick <nboutelier(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to avoid multiple table scan with "NOT IN"
Date: 2009-03-21 22:48:01
Message-ID: ba6aa9cf-a72f-42a8-932e-f511c9ab6872@u9g2000pre.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-03-22 01:55:33 Re: How to avoid multiple table scan with "NOT IN"
Previous Message Jerry LeVan 2009-03-21 17:17:07 Re: Messed up dblink...