I presume there is more usage of this view than just those 3 queries (otherwise, for a start there would be no need for d, e, f in the view definition)
Why not just rewrite these 3 queries to go directly off the main table? Or, create a different view without the sort_by in its definition?
Or, if these are used very frequently and performance is critical, consider (i) caching these results in the application layer, with logic to understand when they need to be updated, or (b) maintaining extra tables that just contain (a) (a,b) and (a,b,c)
Objectively, it's always better to optimize the SQL and application level for the specific needs of the situation before concluding that the underlying database engine should do these optimizations automatically, and it seems like there are a number of options you could explore here.
Cheers
Dave
On Mon, Sep 13, 2010 at 4:47 AM, Gaetano Mendola
<mendola@gmail.com> wrote:
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance