Re: WIP: Upper planner pathification

On Tue, Mar 1, 2016 at 3:02 PM, Tom Lane <tgl@sss.pgh.pa.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.50rows=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.50rows=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