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

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