Re: BUG #6335: Weird planner decision with exists (a join b) condition

On Thu, Dec 15, 2011 at 7:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> maxim(dot)boguk(at)gmail(dot)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