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

Useless sort by

From: Gaetano Mendola <mendola(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Useless sort by
Date: 2010-09-13 09:47:24
Message-ID: i6ks04$2jet$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,
I have a view v_table defined as following:

select a,b,c,d,e,f
from  t_table
sort by  a,b,c;

the usage pattern of this view is the following:

select distinct(a) from v_table;
select distinct(b) from v_table where a = "XXX";
select distinct(c) from v_table where a = "XXX" and b = "YYYY";

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 ?


Regards
Gaetano Mendola




Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-09-13 14:26:28
Subject: Re: Problem with mergejoin performance
Previous:From: Gerhard WiesingerDate: 2010-09-13 06:39:44
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4

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