Re: slow IN() clause for many cases

On Fri, Dec 02, 2005 at 08:18:44AM +0000, Simon Riggs wrote:
> 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.

Are you sure? If you have one table joined to many others, that is the
single most obvious case for bitmap indexes. And joins are converted to
bitmap index scans all the time so I'm unsure why this case would be
any different. If the results are the same, the optimiser should
optimise both the same, no?

Anyway, maybe I'm just old fashioned in thinking that joins are by far
the easiest to optimise because they are closest to relational algebra.
IN() can also be converted to a join, except for the NULL effect.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.