Skip site navigation (1) Skip section navigation (2)

Re: Unexpected sort order.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Unexpected sort order.
Date: 2006-11-27 22:39:30
Message-ID: 25132.1164667170@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> Tom Lane wrote:
>> It looks to me like the planner thinks that order by a and order by b
>> are equivalent because the expressions are equal(); hence it discards
>> what it thinks is a redundant second sort step.

> Would it be a smaller waste of cycles and still avoid the problem
> if the planner blindly kept only the second sort step rather than
> the first one when it sees these redundant steps?  Or would that
> get other cases wrong?

I was fuzzing the explanation a bit --- there really isn't any place
that we could simply reverse the logic and get the other behavior.
The real issue is that the planner's "PathKey" representation of sort
ordering is actually incapable of distinguishing whether the sub-query
is sorted by a or by b: in either case the PathKeyItem will contain the
expression "(random()*10)::int".  So when the upper query tries to
decide whether the lower query is already sorted the way it wants,
it'll come out with a match.  We surely don't want to discard the
optimization of avoiding redundant sorts of subquery outputs, so the
only way to "fix" this would be a fundamental redesign of the PathKey
mechanism to special-case volatile expressions somehow.  I'm resistant
to doing that without a fairly solid use-case for sorting by volatile
expressions ...

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Lucian CapdefierDate: 2006-11-28 00:21:27
Subject: BUG #2789: problem with delete statement
Previous:From: Tom LaneDate: 2006-11-27 22:22:21
Subject: Re: Unexpected sort order.

pgsql-general by date

Next:From: rnshahDate: 2006-11-27 22:40:30
Subject: CertFirst Legit?
Previous:From: Joshua D. DrakeDate: 2006-11-27 22:36:36
Subject: Re: IS it a good practice to use SERIAL as Primary Key?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group