all my SQL-writin' life i have been assuming that expressions like =,
IN, BETWEEN in the WHERE clause are, in the most general sense,
alternative ways of doing the same things. i am hitting some very very
bizarre results in PGSQL:
i have a (very involved) view, say v_foo, largely optimized to be
queried by a user_id column, so:
select * from v_foo where user_id = 70728; Time: 580.620 ms
however an essentially synonymous IN construct takes minutes to complete
(the subquery inside the IN clause will return the 70728 ID and by
itself takes 40ms to complete):
select * from v_foo where user_id in (select user_id from bar group by
1 having count(*) = 10 limit 1); Time: 244616.464 ms
a synonymous-looking BETWEEN also takes forever:
select * from v_foo where user_id between 70728 and 70728; Time: 329332.722 ms
there is, admittedly, substantial complexity inside v_foo, with GROUP
BYs on user_id and various subqueries, but my basic thought is that
should not really matter...
i am on 8.1.3. i'd like to hope that the 8.2 optimizer improvements
might help with this but i haven't tested.
george