"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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
Ask me about EnterpriseDB's PostGIS support!
In response to
pgsql-general by date
|Next:||From: Chris Mayfield||Date: 2008-03-29 12:36:56|
|Subject: Re: Merge Joins and Views|
|Previous:||From: Richard Huxton||Date: 2008-03-29 08:05:01|
|Subject: Re: creating a trigger to access another postgres database?|