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

Re: Useless sort by

From: Gaetano Mendola <mendola(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Useless sort by
Date: 2010-09-13 15:55:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On 09/13/2010 04:44 PM, Tom Lane wrote:
> Gaetano Mendola <mendola(at)gmail(dot)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).

Gaetano Mendola

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2010-09-13 16:48:07
Subject: Re: Useless sort by
Previous:From: Tom LaneDate: 2010-09-13 15:16:40
Subject: Re: Problem with mergejoin performance

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