"Tom Lane" <tgl@xxxxxxxxxxxxx> 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
