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: 87d4pdygw1.fsf@oxford.xeocode.com (view raw or flat)
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
joins.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

In response to

Responses

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-2014 The PostgreSQL Global Development Group