Re: IN list processing performance (yet again)

Mario Weilguni wrote:

>>My application relies heavily on IN lists.  The lists are primarily
>>constant integers, so queries look like:
>>
>>SELECT val FROM table WHERE id IN (43, 49, 1001, 100002, ...)
>>
>>Performance is critical, and the size of these lists depends a lot on
>>how the larger 3-tier applicaiton is used,
>>but it wouldn't be out of the question to retrieve 3000-10000 items.
>>
>>PostgreSQL 7.3.2 seems to have a lot of trouble with large lists.
>>    
>>
>
>you should rewrite your query if the query is created from an applition:
>
>SELECT val
>   FROM table
> WHERE id between 43 and 100002
>       AND id IN (43, 49, 1001, 100002, ...)
>
>where 43 is the min and 100002 the max of all values.
>
>I had this case with postgresql 7.2 and the planner made much smarter
>choices in my case.
>
>Regards,
>    Mario Weilguni
>  
>
Very interesting!  I tried it out, but it didn't appreciably change the 
thresholds in my results for going by for IN list
sizes 100 - 1000.  It's also likely to be of use only if the range for 
the between is fairly restricted,
which isn't necessarily characteristic of my data.

Dave