Re: Useless sort by

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>