Re: Planning for improved versions of IN/NOT IN

Joe Conway wrote:
> Tom Lane wrote:
> 
>> I've been thinking about how to improve the performance of queries using
>> "WHERE x IN (subselect)" and "WHERE x NOT IN (subselect)".
>
> How about starting with a rule-based method to make the choice?
> 
> 1. If uncorrelated: use hash-based approach - ISTM this might address a 
> large
>    percentage of the problem cases -- it could even handle the
>    "IN (list-of-scalars)" case. Could it fall back to a
>    tuplesort/binary-search for the too many to hash in memory case?
> 2. If correlated: use an inner indexscan
> 3. If you come up with a pattern where none of the approaches produce a
>    correct answer, use the existing implementation
> 
> You could always get fancier later if needed, but something along these 
> lines would be a great start.

I curious if any of the rewriting of EXISTS and NOT EXISTS would 
address the problem described by Date:

http://www.firstsql.com/iexist.htm

Mike Mascari
mascarm@mascari.com