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: (view raw or whole 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-2015 The PostgreSQL Global Development Group