To: Craig Ringer <ringerc@ringerc.id.au>
cc: Melaka Gunasekara <donmelaka@gmail.com>,
	Raghavendra <raghavendra.rao@enterprisedb.com>, pgsql-admin@postgresql.org
Subject: Re: How to change query planner configuration paramerters 
In-reply-to: <4E75CB6D.9000302@ringerc.id.au> 
References: 
 <CAM4Dy=120OKxqjsXV1rRK1eXH=uoDes_k5SvrUdR6xESUXWAww@mail.gmail.com>
	<CA+h6Ahhb6SyXt55YgsWmKOJkQ-opKpDHJv_rWJ_WejF=F+0GTw@mail.gmail.com>
	<CAM4Dy=27O0n9bvuETRdhCx3cR-24+ifmfNxm3CFNbA8_CKZsZA@mail.gmail.com>
	<4E75CB6D.9000302@ringerc.id.au>
Comments: In-reply-to Craig Ringer <ringerc@ringerc.id.au>
	message dated "Sun, 18 Sep 2011 18:43:57 +0800"
Date: Sun, 18 Sep 2011 12:33:40 -0400
Message-ID: <16153.1316363620@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Craig Ringer <ringerc@ringerc.id.au> writes:
> On 18/09/2011 5:51 PM, Melaka Gunasekara wrote:
>> Merge Full Join (cost=10000000074.40..10000000093.69 rows=1159 width=286)

>> Can you suggest why the merge join is being suggested when I have 
>> turned it off ?

> AFAIK SETting a join type to "off" really just increases the cost 
> estimate so high that the planner will avoid using it where it has any 
> alternative. In this case, it doesn't seem to think it has any other way 
> to execute the query, or it thinks that any other way will be so 
> incredibly, insanely slow that the merge join is still better.

It's the first of those --- FULL joins are only implemented in the
mergejoin logic, not in hash or nestloop joins, so there is no other way
to do this query.  (But as of 9.1, hash joins can do them too.)

			regards, tom lane