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.
I will attach the code I used to this thread once complete.
Again Thanks
Andrew Bartley
On 14 July 2010 00:43, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote:
Andrew Bartley wrote: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
It seems that the underlying stats tables are reset on a periodic basis, can i stop this process? Is it a .conf setting?
If you're on that version or earlier and it's turned on, there's your problem. This went away in 8.3.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.
Also i need to find similar information regarding functions and views.... Any suggestions?
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@xxxxxxxxxxxxxxx www.2ndQuadrant.us
