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

Re: Q: Performance of join vs embedded query for simple queries?

From: mark(at)mark(dot)mielke(dot)cc
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Date: 2006-08-18 02:21:31
Message-ID: 20060818022131.GA15599@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Aug 17, 2006 at 09:21:33PM -0400, Tom Lane wrote:
> mark(at)mark(dot)mielke(dot)cc writes:
> > I have two simple queries that do what I believe to be the exact same
> > thing.
> These are actually not equivalent per spec.
> ...
> This still isn't equivalent to the join: it'll return at most one copy
> of any sm_change row, whereas you can get multiple copies of the same
> sm_change row from the join, if there were multiple matching sm_system
> rows.  (Hm, given the unique index on (system_dbid, uid), I guess that
> couldn't actually happen --- but you have to reason about it knowing
> that that index is there, it's not obvious from the form of the query.)

> Anyway: given the way that the planner works, the IN form and the join
> form will probably take comparable amounts of time to plan.  The "=
> subselect" form is much more constrained in terms of the number of
> alternative implementations we have, so it doesn't surprise me that it
> takes less time to plan.

That makes sense. Would it be reasonable for the planner to eliminate
plan considerations based on the existence of unique indexes, or is
this a fundamentally difficult thing to get right in the general case?

I did the elimination in my head, which is why I considered the plans to
be the same. Can the planner do it?

Sub-millisecond planning/execution for simple queries on moderate
hardware seems sexy... :-)

Thanks,
mark

-- 
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


In response to

Responses

pgsql-performance by date

Next:From: markDate: 2006-08-18 02:30:32
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Previous:From: Tom LaneDate: 2006-08-18 01:21:33
Subject: Re: Q: Performance of join vs embedded query for simple queries?

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