From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> |
Cc: | PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Repeating Append operation |
Date: | 2010-03-23 18:55:42 |
Message-ID: | 603c8f071003231155i18503068nd6297a0ddb49c49b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Mar 23, 2010 at 2:09 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> On Sun, Mar 21, 2010 at 4:29 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> On Fri, Mar 19, 2010 at 2:09 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
>> wrote:
>> > Is there a way to avoid this double evaluation?
>>
>> Maybe with a CTE?
>>
>> WITH x AS (...) SELECT ...
>>
>> It does look like surprising behavior.
>
> It was discussed on the IRC that same day, and RhodiumToad (Andrew) pointed
> out that this behaviour is because of subquery un-nesting. Putting an OFFSET
> 0 clause (hint) in the inline view prevents it from being merged with the
> outer query:
>
> explain
> select v from (
> select array(
> select 1
> union all
> select 2) as v
> from (select 1) offset 0) as s
> where v is not null;
> QUERY PLAN
> ----------------------------------------------------------------------------------
> Subquery Scan s (cost=0.04..0.07 rows=1 width=32)
> Filter: (v IS NOT NULL)
> -> Limit (cost=0.04..0.06 rows=1 width=0)
> InitPlan
> -> Append (cost=0.00..0.04 rows=2 width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
> -> Subquery Scan __unnamed_subquery_0 (cost=0.00..0.02 rows=1
> width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
> (9 rows)
>
> This raises the point that we do subquery un-nesting purely on
> heuristics, and not on cost basis. I guess we should be be doing a cost
> comparison too. I think that this un-nesting happens quite before we start
> generating alternative plans for cost comparisons, and that we might not
> have costs to compare at this stage, but IMHO we should somehow incorporate
> cost comparisons too.
I don't think this is right. Flattening the subquery doesn't prevent
the join from being implemented a nested loop, which is essentially
what happens when it's treated as an initplan. It just allows other
options also.
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Gokulakannan Somasundaram | 2010-03-23 18:56:06 | Re: Deadlock possibility in _bt_check_unique? |
Previous Message | Alvaro Herrera | 2010-03-23 18:43:58 | Re: Proposal: access control jails (and introduction as aspiring GSoC student) |