[SQL] Strange strategy with COALESCE?

[Philip Warner]

I get unexpected query strategy when using coalesce.

Good:
-----

explain update zzz set 
        b = (select x.newVal from zzz_xref x where x.oldVal = zzz.b);
NOTICE:  QUERY PLAN:

Seq Scan on zzz  (cost=0.00..20.00 rows=1000 width=10)
  SubPlan
    ->  Index Scan using zzz_xref_ix2 on zzz_xref x  (cost=0.00..726.82
rows=983 width=4)

Bad (just by adding a COALESCE call):
-------------------------------------

explain update zzz set 
        b = coalesce((select x.newVal from zzz_xref x where x.oldVal =
zzz.b),b);
NOTICE:  QUERY PLAN:

Seq Scan on zzz  (cost=0.00..20.00 rows=1000 width=10)
  SubPlan
    ->  Index Scan using zzz_xref_ix2 on zzz_xref x  (cost=0.00..726.82
rows=983 width=4)
    ->  Seq Scan on zzz_xref x  (cost=0.00..1757.80 rows=98304 width=4)


Does this make sense?

P.S. There are indexes on both oldVal and newVal in zzz_xref, and table zzz
has one column (b).



----------------------------------------------------------------
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   |/