Re: slow IN() clause for many cases

On Tue, Nov 29, 2005 at 10:53:38PM +0000, Simon Riggs wrote:
> On Tue, 2005-11-29 at 17:21 -0500, Tom Lane wrote:
> > regression=# explain select * from tenk1 where unique1 = any (array(select f1 from int4_tbl));

<snip>

> So we could teach the planner to transform:
>
> IN (subselect)
>
> into
>
> = ANY(array(subselect))
>
> if we had the planner think the subselect had say < 1000 rows?

Do these constructs have the same semantics w.r.t. NULL? Currently
arrays can't have nulls but that is changing. Also, won't they behave
differently in the case where the subselect returns duplicate values?

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.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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.