Re: Query plan for NOT IN

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query plan for NOT IN
Date: 2009-10-05 14:30:36
Message-ID: 7197.1254753036@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> Yes, that does work, but only because id is NOT NULL. I thought Postgres
> 8.4 had had a load of these join types unified to make it less important
> how the query is written?

NOT IN is not easily optimizable because of its odd behavior in the
presence of nulls. Use NOT EXISTS instead, or that left join hack.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Stefan Kaltenbrunner 2009-10-05 14:55:15 Re: Best suiting OS
Previous Message Grzegorz Jaśkiewicz 2009-10-05 14:06:41 Re: Query plan for NOT IN