Re: Index Usage using IN

On Thu, Feb 02, 2006 at 09:12:59 +1300,
Ralph Mason <ralph(dot)mason(at)telogis(dot)com> wrote:
> Hi,
>
> I have 2 tables both have an index on ID (both ID columns are an oid).
>
> I want to find only only rows in one and not the other.
>
> Select ID from TableA where ID not IN ( Select ID from Table B)
>
> This always generates sequential scans.
>
> Table A has about 250,000 rows. Table B has about 250,000 Rows.
>
> We should get a Scan on Table B and a Index Lookup on Table A.

I don't think that is going to work if there are NULLs in table B.
I don't know whether or not Postgres has code to special case NULL testing
(either for constraints ruling them out, or doing probes for them in addition
to the key it is trying to match) for doing NOT IN. Just doing a simple
index probe into table A isn't going to tell you all you need to know if
you don't find a match.