DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=KEt/cAVovvYKjO/8MxwHfLXi6shY2OO+5jRfXLJ/A9sgFeUrwuKi2H2wE+Q9kxxzMIq4e8uP9y0BaTYYOAYYx00lAfo8WT5z2GxwHmy1JFzry50YK8M1dbldMyU0SDPnSuBBu42nfhT9BaSYTc8iyuwH3u6MkE4F+mPLCiGJl4U=
Message-ID: <46D82B86.6060600@gmail.com>
Date: Fri, 31 Aug 2007 18:53:58 +0400
From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-sql@postgresql.org
Subject: Re: Incomprehensible dogged sort in Merge Join
References: <46D82318.3070700@gmail.com> <24061.1188571419@sss.pgh.pa.us>
In-Reply-To: <24061.1188571419@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes:
>> Trying to get an extra time savings in my query, I stopped at an unusual 
>> doggedness of the planner.
> 
>>   Merge Left Join
>>     Merge Cond: ("outer".name = "inner".name)
>>     ->  Sort
>>           Sort Key: log.name
>>           ->  Seq Scan on log_example_3 log
>>     ->  Sort
>>           Sort Key: uh.name
>>           ->  Subquery Scan uh
>>                 ->  Sort
>>                       Sort Key: name
>>                       ->  Seq Scan on user_history
> 
>> The strange thing is that planner can combine two sorts by uh.name key 
>> in one, but it seems it can't see this.
> 
>> May be this can be recorded as a needed feature for future releases?
> 
> When criticizing planner deficiencies, it's considered polite to use
> something that's less than two major releases back ;-)

Sorry, it was blown out from my head at the end of composing: my version 
is 8.0.1, not so old, IMHO.

> 
> CVS HEAD gets this right, although I need to go look at why it's
> sticking a Materialize in there:
> 
> regression=# explain select * from (select * from tenk1 order by twothousand) uh right join tenk1 log on log.thousand = uh.twothousand;
>                                     QUERY PLAN                                     
> -----------------------------------------------------------------------------------
>  Merge Right Join  (cost=4575.77..6225.77 rows=100000 width=488)
>    Merge Cond: (tenk1.twothousand = log.thousand)
>    ->  Sort  (cost=2287.89..2312.89 rows=10000 width=244)
>          Sort Key: tenk1.twothousand
>          ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
>    ->  Materialize  (cost=2287.89..2412.89 rows=10000 width=244)
>          ->  Sort  (cost=2287.89..2312.89 rows=10000 width=244)
>                Sort Key: log.thousand
>                ->  Seq Scan on tenk1 log  (cost=0.00..458.00 rows=10000 width=244)
> (9 rows)
> 
> [ pokes at it a bit more... ]  8.1 and 8.2 get it right for a plain
> join, but not for an outer join.  Strange, I need to check that too.
> 
> 			regards, tom lane
> 

Best wishes,
   Aleksandr.