Re: Merge Joins and Views

Chris Mayfield <cmayfiel(at)cs(dot)purdue(dot)edu> 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