optimizer not eliminating redundant sorts

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: optimizer not eliminating redundant sorts
Date: 2003-02-06 21:17:15
Message-ID: 87bs1p2jok.fsf@stark.dyndns.tv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-02-06 21:18:47 Re: Index not used with IS NULL
Previous Message Tom Lane 2003-02-06 21:15:57 Re: backend process crash - PL/pgSQL functions - parsing problem?