X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8DCD85E46D5 for ; Sun, 15 Aug 2004 13:01:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20345-05 for ; Sun, 15 Aug 2004 16:01:36 +0000 (GMT) Received: from fep6.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id 7DC705E46C4 for ; Sun, 15 Aug 2004 13:01:27 -0300 (ADT) Received: from [127.0.0.1] (d226-86-215.home.cgocable.net [24.226.86.215]) by fep6.cogeco.net (Postfix) with ESMTP id 6D2745EA; Sun, 15 Aug 2004 12:01:32 -0400 (EDT) Message-ID: <411F88D6.3000300@ethereal-realms.org> Date: Sun, 15 Aug 2004 12:01:26 -0400 From: Martin Foster Organization: Ethereal Realms User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040803 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: PostgreSQL Performance Subject: Re: Faster with a sub-query then without References: <4oATc.14571$S55.11652@clgrps12> <15917.1092542103@sss.pgh.pa.us> In-Reply-To: <15917.1092542103@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/237 X-Sequence-Number: 7873 Tom Lane wrote: > Martin Foster writes: > >>The one not using sub-queries under EXPLAIN ANALYZE proves itself to be >>less efficient and have a far higher cost then those with the penalty of >>a sub-query. Since this seems to be counter to what I have been told >>in the past, I thought I would bring this forward and get some >>enlightenment. > > > The ones with the subqueries are not having to form the full join of W > and G; they just pick a few rows out of G and look up the matching W > rows. > > The "subquery penalty" is nonexistent in this case because the > subqueries are not dependent on any variables from the outer query, and > so they need be evaluated only once, rather than once per outer-query > row which is what I suppose you were expecting. This is reflected in > the EXPLAIN output: notice they are shown as InitPlans not SubPlans. > The outputs of the InitPlans are essentially treated as constants (shown > as $0 in the EXPLAIN output) and the outer plan is approximately what > it would be if you'd written WHERE g.field = 'constant' instead of > WHERE g.field = (select ...) > > regards, tom lane That would explain it overall. Still, it does seem unusual when one puts in additional code, which most literature warns you about and you actually gain a speed boost. Thanks! Martin Foster Creator/Designer Ethereal Realms martin@ethereal-realms.org