Skip site navigation (1) Skip section navigation (2)

Re: Merge Joins and Views

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Chris Mayfield" <cmayfiel(at)cs(dot)purdue(dot)edu>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Merge Joins and Views
Date: 2008-03-29 10:16:30
Message-ID: (view raw or whole thread)
Lists: pgsql-general
"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

  Gregory Stark
  Ask me about EnterpriseDB's PostGIS support!

In response to


pgsql-general by date

Next:From: Chris MayfieldDate: 2008-03-29 12:36:56
Subject: Re: Merge Joins and Views
Previous:From: Richard HuxtonDate: 2008-03-29 08:05:01
Subject: Re: creating a trigger to access another postgres database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group