Re: slow IN() clause for many cases

On Fri, 2005-10-14 at 19:09 -0400, Tom Lane wrote:
> I wrote:
> > I'm thinking that IN should be
> > converted to a ScalarArrayOpExpr, ie
>
> > x = ANY (ARRAY[val1,val2,val3,val4,...])
>
> Actually, there is one little thing in the way of doing this: it'll
> fail if any of the IN-list elements are NULL, because we have not got
> support for arrays with null elements. So we'd have to fix that first.

You'd also need to consider how this effects partial indexes and
constraint exclusion. Not much of an issue, but an extra case to handle
in the predicate proving code.

= = =

Just had a case where using an IN list was quicker than using a join
because it allowed an index lookup to occur. There is also some clear
mileage in transforming this type of query to a more plannable form:

select * from bigtable where word IN (
select word from customer_word where customer = 6)

i.e. where the values for the IN clause are evaluated at run time,
rather than at plan time.

Best Regards, Simon Riggs