Re: slow IN() clause for many cases

Tom Lane <tgl@sss.pgh.pa.us> writes:

> It strikes me that now that we have the bitmap indexscan mechanism,
> it wouldn't be hard to do better.  I'm thinking that IN should be
> converted to a ScalarArrayOpExpr, ie
> 
>     x = ANY (ARRAY[val1,val2,val3,val4,...])
> 
> and then we could treat both OpExpr and ScalarArrayOpExpr as matching
> an index when the LHS is the index key and the operator is in the
> index's opclass.  This wouldn't fit comfortably into a plain indexscan,
> but a bitmap indexscan has already got the mechanism for ORing together
> the results of several primitive indexscans (one per array element).
> You just do the scans and insert all the results into your output
> bitmap.

Would this mean it would be impossible to get a fast-start plan for an IN
expression though?

I would fear queries like
SELECT * from tab WHERE x IN (1,2,3) LIMIT 1

Which ought to be instantaneous would become potentially slow.

(Actually I'm more interested in cases where instead of LIMIT 1 it's the
client that will stop as soon as it finds the record it's really looking for.)


-- 
greg