Re: Tunning PostgreSQL performance for views on Windows

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.

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?

I appreciate any help.

Thanks

In response to Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br>:
>
> 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.

Standard tuning advice would apply, as well as the advice not to cross-
post. Also boilerplate advice that we can't really help much without
more detail.

Good places to start with tuning:
http://www.powerpostgresql.com/PerfList
http://www.varlena.com/GeneralBits/Tidbits/perf.html

However, if the data only changes once a day, you may be better of
materializing the data, instead of using a view. Run a cron job
once a day that does your big query and stores the data in another
table vi SELECT INTO might be better for you than a view.

> 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?
>
> I appreciate any help.
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bill Moran
http://www.potentialtech.com

in addition to the good advise of "materialzing the view" as in "create
table <whatever> as select * from <viewwhatever>" once a day,

and to provide more information,

PLEASE take notice that

Good places to start with tuning:
> http://www.powerpostgresql.com/PerfList
> http://www.varlena.com/GeneralBits/Tidbits/perf.html
>

those performance-recommendations are primarly based on Unix-Systems (Linux,
BSD, Solaris).

Especially shared buffers have totally different effects on win32.

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

How big are the underlying tables?

If they are large, are you partitioning?

Since the values only change daily, if the end result is a reasonable
size, have you considered using a CTAS rather than views?

LewisC

--- 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.
>
> 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?
>
> I appreciate any help.
>
> Thanks
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

-----------------------------------------------------------
Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

EnterpriseDB: The Definitive Reference
http://tinyurl.com/39246e
----------------------------------------------------------