From: mark@mark.mielke.cc
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Date: Thu, 17 Aug 2006 22:21:31 -0400
Message-ID: <20060818022131.GA15599@mark.mielke.cc> (raw)
In-Reply-To: <1484.1155864093@sss.pgh.pa.us>
References: <20060818003326.GA11682@mark.mielke.cc>
<1484.1155864093@sss.pgh.pa.us>
On Thu, Aug 17, 2006 at 09:21:33PM -0400, Tom Lane wrote:
> mark@mark.mielke.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@mielke.cc / markm@ncf.ca / markm@nortel.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@mark.mielke.cc, tgl@sss.pgh.pa.us
Subject: Re: Q: Performance of join vs embedded query for simple queries?
In-Reply-To: <20060818022131.GA15599@mark.mielke.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox