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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
