From: Gaetano Mendola <mendola@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Useless sort by
Date: Mon, 13 Sep 2010 17:55:46 +0200
Message-ID: <4C8E4982.70004@gmail.com> (raw)
In-Reply-To: <12103.1284389088@sss.pgh.pa.us>
References: <i6ks04$2jet$1@news.hub.org>
<12103.1284389088@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mendola@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Useless sort by
In-Reply-To: <4C8E4982.70004@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox