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