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(at)mascari(dot)com