optimizer not eliminating redundant sorts

Started by Bruce Momjianabout 23 years ago2 messagesgeneral
Jump to latest
#1Bruce Momjian
bruce@momjian.us

Another odd thing. This type of query seems to be causing redundant sorts.

From what I've read in the source it tries quite hard to keep track of the

sort order of subplans to avoid unnecessary sorts, so I think these are
supposed to get optimized out. I'm not sure if the group nodes can get
optimized out, but surely the sorts can.

slo=> explain select x from (select x,y from (select x,y,z from ttt group by x,y,z) as x group by x,y) as x group by x;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Group (cost=0.05..0.06 rows=1 width=16)
-> Sort (cost=0.05..0.06 rows=1 width=16)
Sort Key: x
-> Subquery Scan x (cost=0.03..0.04 rows=1 width=16)
-> Group (cost=0.03..0.04 rows=1 width=16)
-> Sort (cost=0.03..0.04 rows=1 width=16)
Sort Key: x, y
-> Subquery Scan x (cost=0.01..0.02 rows=1 width=16)
-> Group (cost=0.01..0.02 rows=1 width=16)
-> Sort (cost=0.01..0.02 rows=1 width=16)
Sort Key: x, y, z
-> Seq Scan on ttt (cost=0.00..0.00 rows=1 width=16)

--
greg

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruce Momjian (#1)
Re: optimizer not eliminating redundant sorts

Greg Stark <gsstark@mit.edu> writes:

Another odd thing. This type of query seems to be causing redundant sorts.
From what I've read in the source it tries quite hard to keep track of the
sort order of subplans to avoid unnecessary sorts, so I think these are
supposed to get optimized out.

It doesn't keep track of sort keys across subquery boundaries, which is
what you've got here. I've thought about that in the past, but it'd be
nontrivial --- the sort keys known for the lower path would have to be
translated into the Var namespace of the upper query. Hasn't got to the
top of the todo queue.

regards, tom lane