Re: WIP: Upper planner pathification

On Tue, Mar 1, 2016 at 3:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Well, my point is that no such path would have been generated if the
> subquery hadn't had an internal reason to consider sorting on b.id.
> The "accidental" part of this is that the subquery's GROUP BY key
> matches what the outer query needs as a mergejoin key.

Hm. I can't seem to get it to generate such plans here. This is after
disabling hashjoin or else it doesn't want to do a sort at all:

postgres=# explain select * from (select * from v group by i) as v1
natural join (select * from v group by i) as v2;
QUERY PLAN
---------------------------------------------------------------------------
Merge Join (cost=107.04..111.04 rows=200 width=4)
Merge Cond: (v.i = v_1.i)
-> Sort (cost=53.52..54.02 rows=200 width=4)
Sort Key: v.i
-> HashAggregate (cost=41.88..43.88 rows=200 width=4)
Group Key: v.i
-> Seq Scan on v (cost=0.00..35.50 rows=2550 width=4)
-> Sort (cost=53.52..54.02 rows=200 width=4)
Sort Key: v_1.i
-> HashAggregate (cost=41.88..43.88 rows=200 width=4)
Group Key: v_1.i
-> Seq Scan on v v_1 (cost=0.00..35.50 rows=2550 width=4)
(12 rows)

I'm trying to construct a torture case where it generates lots more
paths than HEAD. I don't think a percent or two on planning time is
significant but if there are cases where the planning time increases
quickly that would be something to code against.

--
greg