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