pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTunning PostgreSQL performance for views on Windows
5+ messages / 5 participants
[nested] [flat]
* Tunning PostgreSQL performance for views on Windows
@ 2007-07-26 14:26 Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-07-26 14:49 ` Re: Tunning PostgreSQL performance for views on Windows Bill Moran <wmoran@potentialtech.com>
2007-07-27 13:38 ` Re: Tunning PostgreSQL performance for views on Windows Merlin Moncure <mmoncure@gmail.com>
2007-07-30 12:22 ` Re: [SQL] Tunning PostgreSQL performance for views on Windows Lewis Cunningham <lewisc@rocketmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Ranieri Mazili @ 2007-07-26 14:26 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tunning PostgreSQL performance for views on Windows
2007-07-26 14:26 Tunning PostgreSQL performance for views on Windows Ranieri Mazili <ranieri.oliveira@terra.com.br>
@ 2007-07-26 14:49 ` Bill Moran <wmoran@potentialtech.com>
2007-07-26 15:17 ` Re: Tunning PostgreSQL performance for views on Windows Harald Armin Massa <haraldarminmassa@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Bill Moran @ 2007-07-26 14:49 UTC (permalink / raw)
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; +Cc: pgsql-general@postgresql.org
In response to Ranieri Mazili <ranieri.oliveira@terra.com.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tunning PostgreSQL performance for views on Windows
2007-07-26 14:26 Tunning PostgreSQL performance for views on Windows Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-07-26 14:49 ` Re: Tunning PostgreSQL performance for views on Windows Bill Moran <wmoran@potentialtech.com>
@ 2007-07-26 15:17 ` Harald Armin Massa <haraldarminmassa@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Harald Armin Massa @ 2007-07-26 15:17 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Ranieri Mazili <ranieri.oliveira@terra.com.br>; pgsql-general@postgresql.org
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tunning PostgreSQL performance for views on Windows
2007-07-26 14:26 Tunning PostgreSQL performance for views on Windows Ranieri Mazili <ranieri.oliveira@terra.com.br>
@ 2007-07-27 13:38 ` Merlin Moncure <mmoncure@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Merlin Moncure @ 2007-07-27 13:38 UTC (permalink / raw)
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Tunning PostgreSQL performance for views on Windows
2007-07-26 14:26 Tunning PostgreSQL performance for views on Windows Ranieri Mazili <ranieri.oliveira@terra.com.br>
@ 2007-07-30 12:22 ` Lewis Cunningham <lewisc@rocketmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Lewis Cunningham @ 2007-07-30 12:22 UTC (permalink / raw)
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; pgsql-general@postgresql.org; pgsql-sql
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.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.
>
> 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
----------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-30 12:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-26 14:26 Tunning PostgreSQL performance for views on Windows Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-07-26 14:49 ` Bill Moran <wmoran@potentialtech.com>
2007-07-26 15:17 ` Harald Armin Massa <haraldarminmassa@gmail.com>
2007-07-27 13:38 ` Merlin Moncure <mmoncure@gmail.com>
2007-07-30 12:22 ` Lewis Cunningham <lewisc@rocketmail.com>
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