Chris Mayfield <cmayfiel@xxxxxxxxxxxxx> writes: > See attached -- I've simplified my actual database quite a bit, but this > example shows the same results. OK, here's the problem: > CREATE VIEW v AS > SELECT id, COALESCE(opt, 0) AS opt FROM b; You're using this inside the nullable side of an outer join, and that means the COALESCE() creates a problem: its output won't go to null just because "opt" does. So the COALESCE has to be evaluated below the outer join, which means that the view can't be "flattened" into the upper query. You end up with a dumb seqscan that corresponds to planning the view in isolation, and then the best way of joining that with the other table is going to be the sort and merge join. 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
