On Tue, Mar 23, 2010 at 2:09 PM, Gurjeet Singh <singh.gurj...@gmail.com> wrote: > On Sun, Mar 21, 2010 at 4:29 PM, Robert Haas <robertmh...@gmail.com> wrote: >> >> On Fri, Mar 19, 2010 at 2:09 PM, Gurjeet Singh <singh.gurj...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers