Re: slow IN() clause for many cases

On Wed, 2005-11-30 at 07:18 +0100, Martijn van Oosterhout wrote:

> And finally, why can't:
>
> > > > Select * From Sales where month IN (
> > > > select month from time_dimension where FinYear = 2005 and Quarter = 3)
>
> Be written as:
>
> Select sales.* From Sales, time_dimension
> where month = time_dimension.inYear = 2005 and time_dimension.Quarter = 3;
>
> As long as there are no NULLs it returns the same as the IN() version
> and PostgreSQL can optimise it just fine.

It can, of course, but there must be value in that optimization.

If you consider how IN () would be transformed into
=ANY(ARRAY(subselect)) you'll see that the subselect values would be
treated as constants that could result in a bitmap index lookup.

Transforming IN () into straight joins would not take the same approach
when more than one join (i.e. 3 or more tables) was requested.

Best Regards, Simon Riggs