Re: Incomprehensible dogged sort in Merge Join

Tom Lane wrote:
> Aleksandr Vinokurov <aleksandr(dot)vin(at)gmail(dot)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.