To: Simon Riggs <simon@2ndQuadrant.com>
cc: List pgsql-patches <pgsql-patches@postgresql.org>
Subject: Re: WIP Join Removal 
In-reply-to: <1220375620.4371.479.camel@ebony.2ndQuadrant> 
References: <1220176372.4371.118.camel@ebony.2ndQuadrant>
	<15179.1220372890@sss.pgh.pa.us>
	<1220375620.4371.479.camel@ebony.2ndQuadrant>
Comments: In-reply-to Simon Riggs <simon@2ndQuadrant.com>
	message dated "Tue, 02 Sep 2008 18:13:40 +0100"
Date: Tue, 02 Sep 2008 13:18:37 -0400
Message-ID: <16037.1220375917@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Tue, 2008-09-02 at 12:28 -0400, Tom Lane wrote:
>> I haven't thought this through entirely, but wouldn't a partial index be
>> okay if it's marked predOK?  You might be right that the case is
>> unlikely, but if it's only one extra line to support it ...

> As of now, its it is predOK then that implies there was a qual on the
> checkrel so can't remove the join.

That conclusion seems utterly wrong to me.  Per the example of

a left join b on (a.x = b.y and b.z = 1)

b.z = 1 will bubble down to be a qual on b.  It doesn't prevent the join
optimization, and it does possibly allow the use of a partial index.
In particular a unique index on b.y with a predicate involving b.z would
be relevant to the optimization decision here.

In slightly more realistic cases, b might be a view on c that imposes
a WHERE condition on c.z, so that's another avenue for a qual to exist
below the join.

			regards, tom lane