From: Merlin Moncure <mmoncure@gmail.com>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Tunning PostgreSQL performance for views on Windows
Date: Fri, 27 Jul 2007 09:38:45 -0400
Message-ID: <b42b73150707270638y5f09b767i510ea2d86cbf950a@mail.gmail.com> (raw)
In-Reply-To: <46A8AF1B.4090607@terra.com.br>
References: <46A8AF1B.4090607@terra.com.br>
On 7/26/07, Ranieri Mazili <ranieri.oliveira@terra.com.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mmoncure@gmail.com, ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: Tunning PostgreSQL performance for views on Windows
In-Reply-To: <b42b73150707270638y5f09b767i510ea2d86cbf950a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox