From: Martin Foster <martin@ethereal-realms.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Faster with a sub-query then without
Date: Sun, 15 Aug 2004 12:01:26 -0400
Message-ID: <411F88D6.3000300@ethereal-realms.org> (raw)
In-Reply-To: <15917.1092542103@sss.pgh.pa.us>
References: <4oATc.14571$S55.11652@clgrps12>
<15917.1092542103@sss.pgh.pa.us>
Tom Lane wrote:
> Martin Foster <martin@ethereal-realms.org> 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
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: martin@ethereal-realms.org, tgl@sss.pgh.pa.us
Subject: Re: Faster with a sub-query then without
In-Reply-To: <411F88D6.3000300@ethereal-realms.org>
* 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