Andrew Bartley wrote:
> It seems that the underlying stats tables are reset on a periodic
> basis, can i stop this process? Is it a .conf setting?
Up until PostgreSQL 8.2 there's a setting named
stats_reset_on_server_start that clears everything when the server
stops:
http://www.postgresql.org/docs/8.2/static/runtime-config-statistics.html
If you're on that version or earlier and it's turned on, there's your
problem. This went away in 8.3.
> Also i need to find similar information regarding functions and
> views.... Any suggestions?
Some suggestions already popped up here for functions. Views are
tougher because they essentially work like a macro substitution: the
content of the view gets substituted into the query where it appears,
and off the query planner goes. That's why there's no statistics about
them, they don't actually exist as objects that things are executed
against. I don't know of any way to track their use other than to log
all your queries and look for them popping up. A grep against the
application source code for them can be useful too.
The flip side to that is that eliminating views doesn't really improve
performance, so it's rarely a top priority to get rid of them--unlike
unused indexes for example.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us