Re: IN surpasses NOT EXISTS in 7.4RC2 ?? - Mailing list pgsql-performance

From Tom Lane
Subject Re: IN surpasses NOT EXISTS in 7.4RC2 ??
Date
Msg-id 26331.1068736791@sss.pgh.pa.us
Whole thread Raw
In response to IN surpasses NOT EXISTS in 7.4RC2 ??  (Rajesh Kumar Mallah <mallah@trade-india.com>)
Responses Re: IN surpasses NOT EXISTS in 7.4RC2 ??  (Rajesh Kumar Mallah <mallah@trade-india.com>)
List pgsql-performance
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> NOT EXISTS is taking almost double time than NOT IN .
> I know IN has been optimised in 7.4 but is anything
> wrong with the NOT EXISTS?

That's the expected behavior in 7.4.  EXISTS in the style you are using
it effectively forces a nestloop-with-inner-indexscan implementation.
As of 7.4, IN can do that, but it can do several other things too,
including the hash-type plan you have here.  So assuming that the
planner chooses the right plan choice (not always a given ;-))
IN should be as fast or faster than EXISTS in all cases.

            regards, tom lane

pgsql-performance by date:

Previous
From: Rod Taylor
Date:
Subject: Re: performance optimzations
Next
From: Robert Treat
Date:
Subject: Re: Value of Quad vs. Dual Processor machine