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

Re: Useless sort by

From: Gaetano Mendola <mendola(at)gmail(dot)com>
To: Dave Crooke <dcrooke(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Useless sort by
Date: 2010-09-14 17:06:32
Message-ID: AANLkTimv8tRJJGqc65C5NhrSjTAoM843E13k-FNmHzT7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Sep 14, 2010 at 6:15 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
> 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.

Question here is not how to do it right, but how to make the optimizer smarter
than it is now, taking rid of work not needed.

Regards
Gaetano Mendola

-- 
cpp-today.blogspot.com

In response to

pgsql-performance by date

Next:From: Gerhard WiesingerDate: 2010-09-14 19:59:09
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Previous:From: Merlin MoncureDate: 2010-09-14 17:01:42
Subject: Re: Held idle connections vs use of a Pooler

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