Hello,
Here is my exp. regarding VIEW (create VIEW view_name AS SELECT ....)
optimization.
I have found strange (or at least not very intelligent) behavior of query
planner (v.7.3.4).
Given task is to take data from 14 tables, join, group, sort, etc. I
currently use about ten views referencing each other.
I have done a lot of job to adjust 'postgresql.conf' configuration.
Except common increase of shared_mem&buffer stuff I felt I was unable to do
anything else.
So, initialy, time to generate the Monthly report was about 10 sec.
I started to play with GEQO....
Screaming success -- I downplayed query execution time about 3x JUST by
setting geqo_threshold=3.
Now my Monthly report takes about 3 sec.
(Users are running that sequence of select queries (I call it invocation of
the final view ;-) quite often for any given period of time --
usually from one week to half a year (rarely) so it is essential part of my
dbvs.
Maybe my exp. will encourage somebody to play with scary genetic stuff. ;-)
Good luck & Best Regards,
Alvis