> On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote:
>
> > One thing you might consider is materialized views. Your aggregate
> > functions are killing you...try to avoid using them (except min/max
on
> > an index). Just watch out for mutable functions like now().
> >
> > http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html
> >
> > An application specific approach is to use triggers to keep the data
you
> > need in as close to query form as possible...you can reap enormous
> > savings particularly if your queries involve 3 or more tables or
have
> > large aggregate scans.
>
> I thought materialized views support in pgsql was experimental as yet.
> Are the pg mat-view code upto production servers? Also, do you have to
> delete mat-views before you dump the db or does dump automatically not
> dump the mat-views data?
I think you are thinking about 100% 'true' materialized views. In that
case the answer is no, the server does not have them. The GeneralBits
article describes how to emulate them through pl/sql triggers. I just
bumped into the article yesterday and was very impressed by it...I have
to admin though Note: I have never tried the method, but it should work.
I cc'd the author who perhaps might chime in and tell you more about
them.
Materialized views can give performance savings so good that the tpc
people had to ban them from benchmarks because they skewed results...:)
In postgres, they can help a lot with aggregates...there are many
gotchas tho, for example keeping a count() up to date can get kind of
tricky. If you can get them to work, the filesystem cache efficiency
will rocket upwards...YMMV.
Getting back on topic, I missed the original post where the author
stated his problems were i/o related, not cpu (contrary to my
speculation). I wonder what his insert/update load is?
Merlin
