"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> Given the amount of damage a person with write access to a table can get
> into it seems pointless to not allow them to analyze the table after their
> updates - since best practices would say that normal work with a table
> should not be performed by an owner.
> I should the check for whether a given user can or cannot analyze a table
> should be whether the user has INSERT, UPDATE, or DELETE privileges.
By that argument, we should allow anyone with any write access to do
TRUNCATE. Or perhaps even DROP TABLE. I'm not impressed.
TRUNCATE indeed also seems overly restrictive. But in any case we have a GRANT for TRUNCATE. If you are saying that you'd be OK with adding a GRANT for ANALYZE that would probably suffice.
I'd place DROP TABLE into a different category simply because it alters the design of the database - some only owners in the database should be allowed to do. We also don't tell people to "run DROP TABLE" so that the planner can choose better query plans. We do tell people that after inserting or deleting lots of data they should run ANALYZE to ensure subsequent queries have good info to work with. Telling them to pray that (and/or wait an indefinite period of time for) the auto-vacuum process ran - or to call their DBA - it not a good solution to that problem.
As much as I respect your opinion I was hoping for something less trite.
ANALYZE, even on a large table (though statistics target would influence this), is seemingly fast due to the random sampling. It also operates fully concurrently with other activity.
David J.
