I noticed that the COALESCE function is implemented as a case statement,
with the result that:
update t1 set f = Coalesce( (select fn from t2 x where x.f1 = t1.f1),
t1.f1)
has the following plan:
Seq Scan on t1 (cost=0.00..20.00 rows=1000 width=10) SubPlan -> Seq Scan on t2 x (cost=0.00..22.50 rows=10
width=4) -> Seq Scan on t2 x (cost=0.00..22.50 rows=1000 width=4)
ie. it *seems* to scan t2 twice, because the resulting CASE statement for
the subselect is:
case when not (select fn from t2 x where x.f1 = t1.f1) is NULL then (select fn from t2 x where x.f1 =
t1.f1)else t1.f1 end
which does seem to imply two executions of the same select statement.
I realize that the standard says:
2) COALESCE (V(1), V(2)) is equivalent to the following <case
specification>:
CASE WHEN V(1) IS NOT NULL THEN V(1) ELSE V(2)
END
3) "COALESCE (V(1), V(2), . . . , V(n))", for n >= 3, is
equivalent to the following <case specification>:
CASE
WHEN V(1) IS NOT NULL THEN V(1) ELSE COALESCE (V(2), . . . , V(n)) END
I was wondering if there was a reason that we interpret this literally,
rather than implement a function? Or set a flag on the CaseExpr node to
indicate that the 'result == whenClause', or some such.
I am still hunting through the planner/optimizer to try to understand if
this is feasible, and would appreciate any suggestions...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/