Skip site navigation (1) Skip section navigation (2)

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

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6335: Weird planner decision with exists (a join b) condition
Date: 2011-12-15 22:01:35
Message-ID: CAK-MWwQi==EpR_T2pUfgzRBHwbVc-e3VA5h0DfT3wuecDC-ekg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Holec, JPH SoftwareDate: 2011-12-15 22:02:17
Subject: user names & non-ASCII
Previous:From: Holec, JPH SoftwareDate: 2011-12-15 20:40:47
Subject: user names & non-ASCII

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group