On Thu, Dec 15, 2011 at 7:11 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> maxim.bo...@gmail.com writes: > > EXPLAIN analyze select * > > from applicant_adv_subscription aas > > where > > aas.user_id in (5112699,7995496) > > and exists ( > > SELECT * from resume > > join resume_view_history using (resume_id) > > where > > resume.user_id = aas.user_id > > ); > > I'm hoping to fix this type of case with the "generalized inner > indexscan" work that I've been nattering about for a year or two now. > What you need to make this fast, given that resume and > resume_view_history are both large, is to push the current value of > aas.user_id down into the table scan of resume --- and because the join > and semijoin can't be reordered, that's not possible with the planner's > current simpleminded idea of what an inner indexscan can be. > > The other example you show manages to luck out and get a good plan due > to transitive propagation of equality conditions, but that's a narrow > special case. Any other form of constraint whatsoever on aas is going > to end up with the crummy plan where the whole lower join gets computed. > > regards, tom lane > Thank you very much for information. Rewriting the query did the trick and resolved performance issues. Do you plan create "generalized inner indexscan" mechanics for 9.2 version? -- Maxim Boguk
