To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Incomprehensible dogged sort in Merge Join 
In-reply-to: <46D82318.3070700@gmail.com> 
References: <46D82318.3070700@gmail.com>
Comments: In-reply-to Aleksandr Vinokurov <aleksandr.vin@gmail.com>
	message dated "Fri, 31 Aug 2007 18:18:00 +0400"
Date: Fri, 31 Aug 2007 10:43:39 -0400
Message-ID: <24061.1188571419@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 ;-)

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