Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id ADF5463289F for ; Mon, 13 Sep 2010 11:45:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 59132-04 for ; Mon, 13 Sep 2010 14:44:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 5172463252D for ; Mon, 13 Sep 2010 11:44:51 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o8DEinq3012104; Mon, 13 Sep 2010 10:44:49 -0400 (EDT) To: Gaetano Mendola cc: pgsql-performance@postgresql.org Subject: Re: Useless sort by In-reply-to: References: Comments: In-reply-to Gaetano Mendola message dated "Mon, 13 Sep 2010 11:47:24 +0200" Date: Mon, 13 Sep 2010 10:44:48 -0400 Message-ID: <12103.1284389088@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/55 X-Sequence-Number: 40258 Gaetano Mendola 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. regards, tom lane