Thanks to all that replied, I used Joe Conway's suggestion, using grep and an extracted list of tables, functions and views form the DB. It worked very well.
I will attach the code I used to this thread once complete. Again Thanks Andrew Bartley On 14 July 2010 00:43, Greg Smith <g...@2ndquadrant.com> wrote: > 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 > g...@2ndquadrant.com www.2ndQuadrant.us > >
