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.77rows=100000 width=488) Merge Cond: (tenk1.twothousand = log.thousand) -> Sort
(cost=2287.89..2312.89rows=10000 width=244) Sort Key: tenk1.twothousand -> Seq Scan on tenk1
(cost=0.00..458.00rows=10000 width=244) -> Materialize (cost=2287.89..2412.89 rows=10000 width=244) -> Sort
(cost=2287.89..2312.89rows=10000 width=244) Sort Key: log.thousand -> Seq Scan on tenk1 log
(cost=0.00..458.00rows=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