Re: Tunning PostgreSQL performance for views on Windows

On 7/26/07, Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br> wrote:
> Hello,
>
> I'm developing a BI and as database it's using postgresql 8.2, how data
> are very detailed, I'm creating a view to consolidate the most important
> data, but the performance of view is very poor, 1 minute to perform more
> or less without where clause.
> I need to know how I can increase the performance, if exist some option
> to do cache, because the view will change only one time per day.
> My configuration is default, without modifications after install.
> I'm using windows 2003 server with a dell server with 4GB of memory.

PostgreSQL views are expanded on the fly by the planner...so
optimizing for views is no different than standard query optimization.
Views allow you to layer queries in a logical way but during
execution are treated a single query (think: c macros).

Set returning functions are different...they are a black box to the
planner in most cases and the planner can't optimize through them.

> To create the view, I created some functions, and then perform they on
> one select like:
> select A.field1, B.field2, ... from function_A() A, function_B() B...
> Is this the best way to do it?

So, the first thing I would look at would be to (if possible) rewrite
function_a, b, etc as views and expose fields you filter on to the
outer query in the join. While you can expose fields similarly as
parameters to the function, there are various tricks that the planner
can do that are not possible if some of the sql is hidden away into
functions.

Beyond that, you will have to give more detailed information about
your problem to get more specific advise.

merlin