From: Gaetano Mendola <mendola@gmail.com>
Subject: Re: Useless sort by
Date: Mon, 13 Sep 2010 17:55:46 +0200
Organization: Hub.Org Networking Services
Lines: 32
Message-ID: <4C8E4982.70004@gmail.com>
References: <i6ks04$2jet$1@news.hub.org> <12103.1284389088@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.10pre) Gecko/20100911 Shredder/3.1.5pre
In-Reply-To: <12103.1284389088@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

On 09/13/2010 04:44 PM, Tom Lane wrote:
> Gaetano Mendola <mendola@gmail.com> writes:
>> because of that sort in the view definition the first query above
>> takes not less than 3 seconds. I have solved this performance issue
>> removing the sort from the view definition and putting it in the
>> select reducing the time from > 3secons to < 150ms.
> 
>> Can not the optimizer take rid of that useless sort on those
>> kind of queries ?
> 
> It is not the optimizer's job to second-guess the user on whether a sort
> is really needed there.  If we did make it throw away non-top-level
> sorts, we'd have hundreds of users screaming loudly.

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;

then the user will not know about it. The result is the same but 30 times
faster (in my case).

Regards
Gaetano Mendola