Re: Useless sort by

On Mon, Sep 13, 2010 at 11:09 AM, Gaetano Mendola <mendola(at)gmail(dot)com> wrote:
> On Mon, Sep 13, 2010 at 6:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Gaetano Mendola <mendola(at)gmail(dot)com> writes:
>>> Of course I'm not suggesting to take away the "sort by" and give the user
>>> an unsorted result, I'm asking why the the optimizer in cases like:
>>
>>>    select unique(a) from v_table_with_order_by;
>>
>>> doesn't takes away the "order by" inside the view and puts it back "rewriting the
>>> query like this:
>>
>>>    select unique(a) from v_table_without_order_by
>>>    order by a;
>>
>> That changes the order in which the rows are fed to unique(a).  The
>> principal real-world use for a non-top-level ORDER BY is exactly to
>> determine the order in which rows are fed to a function, so we will
>> have a revolt on our hands if we break that.
>
> I see your point, but some functions like:  unique, count are not affected
> by the order of values fed, and I don't think either that unique has to
> give out the unique values in the same fed order.

First off, having a top level order by in a view is considered poor
practice. It adds an overhead you may or may not need each time the
view is accessed, and there's no simple way to avoid it once it's in
there.

On top of that you'd be adding complexity to the planner that would
make it slower and more likely to make mistakes, all to fix a problem
that I and most others don't have.

--
To understand recursion, one must first understand recursion.