"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> In the case where you introduce the intermediate sub-select, the
> view *can* be flattened into that, producing
> SELECT id, COALESCE(opt, 0) AS opt FROM b ORDER BY id
> Again, that can't be flattened into the top query, but looking at
> it in isolation the planner chooses an indexscan as the best plan
> (by no means a sure thing, but it will do it if the index correlation
> is high). And then the mergejoin without sort falls out from that.
>
> So the long and the short of it is that the COALESCE acts as an
> optimization fence in the presence of outer joins. We've seen this
> before and there are some rough ideas about fixing it. (In fact,
> I thought it was on the TODO list, but I can't find an entry now.)
> Don't hold your breath though --- it'll take major planner surgery.
In this case isn't all the planner needs the pathkey list to give it a hint
that that ordering might be useful? It can't re-order the join but it can
still try to produce those rows in any order which can be useful for the upper
joins.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
