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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mark(at)mark(dot)mielke(dot)cc
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Date: 2006-08-18 01:21:33
Message-ID: 1484.1155864093@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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.

> neudb=> select uid, name from sm_change where system_dbid = (select system_dbid from sm_system where uid = '2ff5942c.dd2911d5.ad56.08:00:09:fd:1b:da') and lower(name) = lower('markm-Q00855572');

> neudb=> select sm_change.uid, sm_change.name from sm_change join sm_system using (system_dbid) where sm_system.uid = '2ff5942c.dd2911d5.ad56.08:00:09:fd:1b:da' and lower(sm_change.name) = lower('markm-Q00855572');

The subselect form constrains the sub-select to return at most one row
--- you'd have gotten an error if there were more than one sm_system row
with that uid.  The join form does not make this constraint.

Another related form is

neudb=> select uid, name from sm_change where system_dbid IN (select system_dbid from sm_system where uid = '2ff5942c.dd2911d5.ad56.08:00:09:fd:1b:da') and lower(name) = lower('markm-Q00855572');

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.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: markDate: 2006-08-18 02:21:31
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Previous:From: markDate: 2006-08-18 00:33:27
Subject: Q: Performance of join vs embedded query for simple queries?

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