Re: Redundant database objects.

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