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

On Thu, Aug 17, 2006 at 09:21:33PM -0400, Tom Lane wrote:
> 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'); 
> ...
> 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.

FYI: You are correct. The IN takes about as long as the join to plan,
and does generate the same plan as the join. This restores confidence
for me that PostgreSQL is able to understand the two as equivalent.

With regard to that unique constraint planning - I gave you the wrong
query from my log. I had already thought that through, and realized
that my original query missed the type. The timings and plans are the
functionally the same for all the three queries, with or without the
type qualifier. This is the table:

                                        Table "public.sm_system"
   Column    |          Type          |                            Modifiers
-------------+------------------------+-----------------------------------------------------------------
 system_dbid | integer                | not null default nextval('sm_system_system_dbid_seq'::regclass)
 type        | character varying(10)  | not null
 uid         | character varying(200) | not null
 name        | character varying(200) | not null
 owner       | character varying(80)  | not null
Indexes:
    "sm_system_pkey" PRIMARY KEY, btree (system_dbid) CLUSTER
    "sm_system_type_key" UNIQUE, btree ("type", uid)
Check constraints:
    "sm_system_type_check" CHECK ("type"::text = 'NEU'::text OR "type"::text = 'PLS'::text)

And this is what the query should have been:

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

-------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop IN Join  (cost=0.00..7.86 rows=1 width=80) (actual time=19.438..19.453 rows=1 loops=1)
   ->  Index Scan using sm_change_name_key on sm_change  (cost=0.00..4.83 rows=1 width=84) (actual time=0.064..0.073
rows=1loops=1) 
         Index Cond: (lower((name)::text) = 'markm-q00855572'::text)
   ->  Index Scan using sm_system_pkey on sm_system  (cost=0.00..3.02 rows=1 width=4) (actual time=19.358..19.358
rows=1loops=1) 
         Index Cond: ("outer".system_dbid = sm_system.system_dbid)
         Filter: ((("type")::text = 'NEU'::text) AND ((uid)::text = '2ff5942c.dd2911d5.ad56.08:00:09:fd:1b:da'::text))
 Total runtime: 19.568 ms
(7 rows)

Time: 21.449 ms


I guess the case isn't as simple as I thought. It would need to recognize
that the specification of both the 'type' and the 'uid' are static, and
unique, therefore the argument to the IN, or the table that it is joining
with will be either 0 rows or 1 row. Too complicated to be worth it, eh? :-)

Cheers,
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/