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 tableWHERE 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
